TR_BILLINGITEMDATES_INSERT_UPDATE (Trigger)

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