TR_BILLINGITEMPRICE_INSERT_UPDATE (Trigger)

                
      create trigger TR_BILLINGITEMPRICE_INSERT_UPDATE on dbo.BILLINGITEMPRICE after insert,update not for replication
      as begin
          begin try
            if (exists (select BILLINGITEMPRICE.ID from BILLINGITEMPRICE inner join INSERTED I on BILLINGITEMPRICE.BILLINGITEMID=I.BILLINGITEMID
            where (BILLINGITEMPRICE.ID <> I.ID) AND (I.DATERANGETYPECODE=0 OR BILLINGITEMPRICE.DATERANGETYPECODE=0)))
                begin
                    raiserror('ERR_BILLINGITEMPRICE_OVERLAP', 13, 1);
                end
            
            if (exists (select BILLINGITEMPRICE.ID from BILLINGITEMPRICE inner join INSERTED I on BILLINGITEMPRICE.BILLINGITEMID=I.BILLINGITEMID
            where (BILLINGITEMPRICE.ID <> I.ID) AND (
            (BILLINGITEMPRICE.STARTDATE between I.STARTDATE and I.ENDDATE) OR 
            (BILLINGITEMPRICE.ENDDATE between I.STARTDATE and I.ENDDATE) OR 
            (BILLINGITEMPRICE.STARTDATE <= I.STARTDATE and BILLINGITEMPRICE.ENDDATE >= I.ENDDATE) OR
            (BILLINGITEMPRICE.ENDDATE IS NULL AND BILLINGITEMPRICE.STARTDATE<=I.ENDDATE) OR
            (I.ENDDATE IS NULL AND I.STARTDATE<=BILLINGITEMPRICE.ENDDATE) OR
            (I.ENDDATE IS NULL AND BILLINGITEMPRICE.ENDDATE IS NULL))))
                begin
                    raiserror('ERR_BILLINGITEMPRICE_OVERLAP', 13, 1);
                end
            
          end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
            end catch
      end