Back to OIM Explorer

dbo.QBM_TUDialogSchedule

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on DialogSchedule. Trigger parent table: DialogSchedule

Source: sandbox-db sys.sql_modules

Source size: 2.506 characters

Interpretation

Database trigger. Treat parent table and enqueue/object-layer calls as the main relation points.

Relations

Trigger parent table: DialogSchedule

Typed Edges

trigger on table DialogSchedule Trigger parent table: DialogSchedule
references source dbo.QBM_FGIScheduleNextRun source text reference
references source dbo.QBM_FGISessionContext source text reference
references source dbo.QBM_FGITimeIsValid source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE trigger QBM_TUDialogSchedule2  ON DialogSchedule FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @jetzt datetime = getutcdate()7  DECLARE @XUser nvarchar(64) = object_name(@@procid)8  DECLARE @Xdate datetime = getutcdate()9  BEGIN TRY10    IF EXISTS(11      SELECT TOP 1 112      FROM inserted)13    GOTO start14    RETURN start:15    DECLARE @GenProcID varchar(38)16    SELECT @GenProcID = dbo.QBM_FGISessionContext('')17    IF18    UPDATE(StartTime)19    BEGIN20      IF EXISTS(21        SELECT TOP 1 122        FROM inserted i23        WHERE24          dbo.QBM_FGITimeIsValid(i.starttime) < 0)25      BEGIN26        RAISERROR('#LDS#The time entered is not valid, must be between 00:00 and 23:59 or empty.|',27        18,28        3)29          WITH nowait30      END31    END32    IF33    UPDATE(Enabled) OR34    UPDATE(StartDate) OR35    UPDATE(EndDate) OR36    UPDATE(Frequency) OR37    UPDATE(FrequencyType) OR38    UPDATE(FrequencySubType) OR39    UPDATE(StartTime) OR40    UPDATE(UID_DialogTimeZone)41    BEGIN42      UPDATE DialogSchedule43      SET EndDate = '2200-01-01',44      XDateUpdated = @Xdate,45      XUserUpdated = @XUser46      FROM DialogSchedule s47      JOIN deleted i48        ON s.UID_DialogSchedule = i.UID_DialogSchedule49      WHERE50        s.EndDate IS NULL51      UPDATE DialogSchedule52      SET NextRun = dbo.QBM_FGIScheduleNextRun('1899-12-31',53      '1899-12-31',54      CASE55        WHEN isnull(s.StartDate, '1900-01-01') > @jetzt AND s.FrequencyType NOT IN('Week', 'Month', 'Year', 'Day') THEN56      s.StartDate57        WHEN isnull(s.StartDate, '1900-01-01') > @jetzt AND s.FrequencyType IN('Week', 'Month', 'Year') THEN58      dateadd(dd, -1, s.StartDate)59        WHEN isnull(s.StartDate, '1900-01-01') > @jetzt AND s.FrequencyType IN('Day') THEN60      dateadd(hh, -1, s.StartDate)61      ELSE @jetzt62      END,63      s.StartTime,64      s.FrequencyType,65      CASE66        WHEN isnull(s.StartDate, '1900-01-01') > @jetzt AND s.FrequencyType NOT IN('Week', 'Month', 'Year', 'Day') AND s.FrequencyType NOT LIKE 'WD[_]%'67      THEN68      069        WHEN s.FrequencyType LIKE 'WD[_]%' THEN70      s.Frequency71      ELSE 172      END,73      s.FrequencySubType,74      isnull(tz.CurrentUTCOffset, 0)),75      XDateUpdated = @Xdate,76      XUserUpdated = @XUser77      FROM DialogSchedule s78      JOIN deleted i79        ON s.UID_DialogSchedule = i.UID_DialogSchedule80      LEFT81      OUTER82      JOIN DialogTimeZone tz83        ON s.UID_DialogTimeZone = tz.UID_DialogTimeZone84      WHERE85        isnull(s.EndDate,86      '2200-01-01') > @jetzt AND s.Enabled = 187      UPDATE DialogSchedule88      SET nextrun = '2200-01-01',89      LastRun = '1900-01-02',90      XDateUpdated = @Xdate,91      XUserUpdated = @XUser92      FROM DialogSchedule s93      JOIN deleted d94        ON s.UID_DialogSchedule = d.UID_DialogSchedule95      WHERE96        s.EndDate < @jetzt OR s.Enabled = 097    END98  END TRY99  BEGIN CATCH100    EXEC QBM_PSessionErrorAdd DEFAULT101    RAISERROR('',102    18,103    1)104      WITH NOWAIT105  END CATCH106END

Open raw exported source

1  create   trigger QBM_TUDialogSchedule on DialogSchedule  for update not for Replication as begin declare @jetzt datetime = getutcdate() declare2 @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() BEGIN TRY if exists (select top 1 1 from inserted) goto start return3 start: declare @GenProcID varchar(38) select @GenProcID = dbo.QBM_FGISessionContext('') if update(StartTime) begin if exists (select top 1 1 from inserted4 i where dbo.QBM_FGITimeIsValid(i.starttime) < 0 ) begin  raiserror( '#LDS#The time entered is not valid, must be between 00:00 and 23:59 or empty.|', 518, 3) with nowait end end if update(Enabled) or update(StartDate) or update(EndDate) or update(Frequency) or update(FrequencyType) or update(FrequencySubType6) or update(StartTime) or update(UID_DialogTimeZone) begin        update DialogSchedule set EndDate = '2200-01-01' , XDateUpdated = @Xdate , XUserUpdated7 = @XUser from DialogSchedule s join deleted i on s.UID_DialogSchedule = i.UID_DialogSchedule where s.EndDate is null update DialogSchedule set NextRun8 = dbo.QBM_FGIScheduleNextRun ('1899-12-31'  , '1899-12-31'  , case  when isnull(s.StartDate, '1900-01-01') > @jetzt and s.FrequencyType not in ('Week'9, 'Month', 'Year', 'Day') then s.StartDate when isnull(s.StartDate, '1900-01-01') > @jetzt and s.FrequencyType in ('Week', 'Month', 'Year') then dateadd10 (dd, -1, s.StartDate) when isnull(s.StartDate, '1900-01-01') > @jetzt and s.FrequencyType in ('Day') then dateadd (hh, -1, s.StartDate) else @jetzt end11 , s.StartTime  , s.FrequencyType  ,  case when isnull(s.StartDate, '1900-01-01') > @jetzt  and s.FrequencyType not in ('Week', 'Month', 'Year', 'Day')12 and s.FrequencyType not like 'WD[_]%' then 0 when s.FrequencyType like 'WD[_]%' then s.Frequency else 1 end , s.FrequencySubType  , isnull(tz.CurrentUTCOffset13, 0)  ) , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogSchedule s join deleted i on s.UID_DialogSchedule = i.UID_DialogSchedule left outer 14join DialogTimeZone tz on s.UID_DialogTimeZone = tz.UID_DialogTimeZone where isnull(s.EndDate, '2200-01-01') > @jetzt  and s.Enabled = 1 update DialogSchedule15 set nextrun = '2200-01-01' , LastRun = '1900-01-02' , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogSchedule s join deleted d on s.UID_DialogSchedule16 = d.UID_DialogSchedule where s.EndDate < @jetzt or s.Enabled = 0 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH 17NOWAIT END CATCH end 18