Back to OIM Explorer

dbo.QBM_TIJobqueue

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on JobQueue. HOFireEvent -> JobQueue.OVERLIMITDETECT at line 22; HOFireEvent -> JobQueue.OVERLIMITWARNING at line 22; HOFireEvent -> JobQueue.OVERLIMITWARNING at line 33; References QBM_PJobCreate*

Source: sandbox-db sys.sql_modules

Source size: 5.388 characters

Interpretation

  • Database trigger. Treat parent table and enqueue/object-layer calls as the main relation points.
  • Object-layer bridge detected through QBM_PJobCreate helper usage.

Relations

  • HOFireEvent -> JobQueue.OVERLIMITDETECT at line 22
  • HOFireEvent -> JobQueue.OVERLIMITWARNING at line 22
  • HOFireEvent -> JobQueue.OVERLIMITWARNING at line 33
  • References QBM_PJobCreate*
  • References QBM_PJobCreate_HOFireEvent*
  • Trigger parent table: JobQueue

Typed Edges

  • fires event JobQueue.OVERLIMITDETECT HOFireEvent -> JobQueue.OVERLIMITDETECT at line 22
  • fires event JobQueue.OVERLIMITWARNING HOFireEvent -> JobQueue.OVERLIMITWARNING at line 22
  • trigger on table JobQueue Trigger parent table: JobQueue
  • references source dbo.QBM_FCVStringToGUID source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOFireEvent source text reference
  • references source dbo.QBM_PJobQueueOverviewSetInv source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_TIJob source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL243 lines
1CREATE trigger QBM_TIJobqueue2  ON JobQueue FOR3INSERT NOT FOR Replication4AS5BEGIN6  DECLARE @JobchainName nvarchar(255)7  DECLARE @uid_job varchar(38)8  DECLARE @SQLWhereClause nvarchar(1000)9  DECLARE @GenProcID varchar(38) = dbo.QBM_FGISessionContext('')10  DECLARE @JustNow datetime = getutcdate()11  DECLARE @LimitedChains QBM_YParameterList12  DECLARE @ElementLast int13  DECLARE @ElementCount int14  DECLARE @ElementIndex int15  DECLARE @XUser nvarchar(64) = object_name(@@procid)16  DECLARE @Xdate datetime = getutcdate()17  BEGIN TRY18    IF EXISTS(19      SELECT TOP 1 120      FROM inserted)21    GOTO start22    RETURN start:23    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw24    INSERT INTO @DBQueueElements_01(Object,25    SubObject,26    GenProcID)27    SELECT28      dbo.QBM_FCVStringToGUID('',29      x.QueueName),30      NULL,31      @GenProcID32    FROM(33    SELECT i.Queue AS QueueName34    FROM inserted i35    GROUP BY i.Queue) AS x36    DELETE @DBQueueElements_0137    FROM @DBQueueElements_01 q38    JOIN QBMJobqueueOverview o39      WITH(readpast)40      ON q.Object = o.UID_QBMJobqueueOverview41    WHERE42      o.IsInvalid = 143    IF EXISTS(44      SELECT TOP 1 145      FROM @DBQueueElements_01)46    BEGIN47      EXEC QBM_PJobQueueOverviewSetInv @DBQueueElements_0148    END49    IF '1' = dbo.QBM_FGIConfigparmValue('Common\JobQueueStats')50    BEGIN51      SELECT @JustNow = GetUTCDate()52      INSERT INTO JobQueueStats(UID_JobQueueStats,53      queue,54      jobYear,55      jobMonth,56      jobDay,57      jobHour,58      countInserted,59      countActivated,60      countDeleted)61      SELECT62        'XX' +convert(varchar(64),63        newid()),64        d.queue,65        year(@JustNow),66        month(@JustNow),67        day(@JustNow),68        datepart(hh,69        @JustNow),70        (-1) * count(*),71        0,72        073      FROM inserted d74      GROUP BY d.queue75    END76    IF EXISTS(77      SELECT TOP 1 178      FROM inserted i79      WHERE80        i.LimitationWarning + i.LimitationCount > 0)81    BEGIN82      IF EXISTS(83        SELECT TOP 1 184        FROM inserted i85        WHERE86          i.LimitationCount > 0)87      BEGIN88        INSERT INTO @limitedChains(ContentShort)89        SELECT name90        FROM jobchain91        WHERE92          limitationcount > 093        IF @@rowcount = 094        BEGIN95          GOTO NachLimitationCount96        END97        merge INTO jobqueue AS t using(98        SELECT99          z.Jobchainname,100          max(z.AltBestand) *(-1) AS AlteOverlimit101        FROM(102        SELECT103          q.limitationcount, l.ContentShort AS Jobchainname, CASE ready2exe104          WHEN N 'OVERLIMIT' THEN105          1106        ELSE 0107        END AS altbestand108        FROM Jobqueue q109          WITH(readpast, index(QBM_XI7JobQueue))110        JOIN @limitedChains l111          ON q.jobchainname = l.ContentShort112        WHERE113          q.Ready2EXE LIKE N '[^DH][^A]%' AND q.IsRootJob = 1 AND q.limitationcount > 0 AND q.JobChainName IN(114        SELECT115          DISTINCT i.JobChainName116        FROM inserted i)) AS z117        GROUP BY z.Jobchainname118        HAVING count(*) > max(z.LimitationCount)) AS s119          ON t.JobChainName = s.JobChainName AND t.Ready2EXE = N 'TRUE' AND t.IsRootJob = 1 AND t.LimitationCount > 0 WHEN matched THEN120        UPDATE121        SET t.ready2exe = N 'OVERLIMIT',122        t.xtouched = 'N',123        t.xdateupdated = dateadd(ss,124        s.alteOverlimit,125        @JustNow);126        IF @@rowcount > 0127        BEGIN128          DECLARE @ElementBufferMulti_01 QBM_YCursorBuffer129          INSERT INTO @ElementBufferMulti_01(UID1)130          SELECT min(q.UID_Job)131          FROM JobQueue q132            WITH(readpast)133          WHERE134            q.IsRootJob = 1 AND q.Ready2EXE = N 'OVERLIMIT' AND q.XDateUpdated = @JustNow135          GROUP BY q.Queue,136          q.JobChainName137          SELECT @ElementCount = @@ROWCOUNT138          SELECT @ElementIndex = @@IDENTITY - @ElementCount +1139          SELECT @ElementLast = @@IDENTITY140          WHILE @ElementIndex <= @ElementLast141          BEGIN142            SELECT TOP 1 @uid_job = bu.UID1143            FROM @ElementBufferMulti_01 bu144            WHERE145              bu.ElementIndex = @ElementIndex146            SELECT @SQLWhereClause = N ' uid_job = ''' + rtrim(@uid_job) + N ''''147            EXEC QBM_PJobCreate_HOFireEvent 'JobQueue',148              @SQLWhereClause,149            'OVERLIMITDETECT',150              @GenProcID = @GenProcID,151              @ObjectKeysAffected = DEFAULT152            SELECT @ElementIndex += 1153          END154        END155      END156      NachLimitationCount:157      IF EXISTS(158        SELECT TOP 1 1159        FROM inserted i160        WHERE161          i.LimitationWarning > 0)162      BEGIN163        DECLARE @ElementBufferMulti_02 QBM_YCursorBuffer164        INSERT INTO @ElementBufferMulti_02(ContentShort,165        UID1)166        SELECT167          x.JobChainName,168          min(x.UID_Job)169        FROM(170        SELECT171          i.JobChainName,172          i.UID_Job,173          i.LimitationWarning,174          1 AS IsNew,175          0 AS IsOverlimitRaised176        FROM inserted i177        WHERE178          i.IsRootJob = 1 AND i.Ready2EXE LIKE N '[^DH][^A]%' AND i.LimitationWarning > 0179        UNION all180        SELECT181          j.JobChainName,182          j.UID_Job,183          j.LimitationWarning,184          0 AS isNew,185        CASE j.Ready2EXE186          WHEN N 'OVERLIMIT' THEN187        1188        ELSE 0189        END AS IsOverlimitRaised190        FROM JobQueue j191          WITH(readpast)192        JOIN(193        SELECT194          DISTINCT ii.JobChainName195        FROM inserted ii) i196          ON j.JobChainName = i.JobChainName197        WHERE198          j.IsRootJob = 1 AND j.Ready2EXE LIKE N '[^DH][^A]%' AND j.LimitationWarning > 0 AND NOT EXISTS(199        SELECT TOP 1 1200        FROM inserted i2201        WHERE202          i2.UID_Job = j.UID_Job)) AS x203        GROUP BY x.JobChainName204        HAVING count(*) > max(x.LimitationWarning) AND MAX(x.IsNew) = 1 AND MAX(x.IsOverlimitRaised) = 0205        SELECT @ElementCount = @@ROWCOUNT206        SELECT @ElementIndex = @@IDENTITY - @ElementCount +1207        SELECT @ElementLast = @@IDENTITY208        WHILE @ElementIndex <= @ElementLast209        BEGIN210          SELECT211            TOP 1 @JobchainName = bu.ContentShort,212            @uid_job = bu.UID1213          FROM @ElementBufferMulti_02 bu214          WHERE215            bu.ElementIndex = @ElementIndex216          UPDATE JobQueue217          SET StartAt = dateadd(mi,218          1,219          StartAt),220          XDateUpdated = @Xdate,221          XUserUpdated = @XUser222          WHERE223            UID_Job = @uid_job224          SELECT @SQLWhereClause = N ' uid_job = ''' + rtrim(@uid_job) + N ''''225          EXEC QBM_PJobCreate_HOFireEvent 'JobQueue',226            @SQLWhereClause,227          'OVERLIMITWARNING',228            @GenProcID = @GenProcID,229            @ObjectKeysAffected = DEFAULT,230            @checkForExisting = 1231          SELECT @ElementIndex += 1232        END233      END234    END235  END TRY236  BEGIN CATCH237    EXEC QBM_PSessionErrorAdd DEFAULT238    RAISERROR('',239    18,240    1)241      WITH NOWAIT242  END CATCH243END
Open raw exported source
SQL ยท Raw36 lines
1    create   trigger QBM_TIJobqueue on JobQueue  for Insert not for Replication as begin declare @JobchainName nvarchar(255) declare @uid_job varchar2(38) declare @SQLWhereClause nvarchar(1000) declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') declare @JustNow datetime = getutcdate()   declare3 @LimitedChains QBM_YParameterList    declare @ElementLast int declare @ElementCount int declare @ElementIndex int declare @XUser nvarchar(64) = object_name4(@@procid) declare @Xdate datetime = getutcdate() BEGIN TRY if exists (select top 1 1 from inserted) goto start return start:  declare @DBQueueElements_015 QBM_YDBQueueRaw insert into @DBQueueElements_01(Object, SubObject, GenProcID) select dbo.QBM_FCVStringToGUID('', x.QueueName), null, @GenProcID from (6 select i.Queue as QueueName from inserted i group by i.Queue ) as x  delete  @DBQueueElements_01 from @DBQueueElements_01 q join QBMJobqueueOverview o7 with (readpast) on q.Object = o.UID_QBMJobqueueOverview where o.IsInvalid = 1 if exists (select top 1 1 from @DBQueueElements_01 ) begin   exec QBM_PJobQueueOverviewSetInv8 @DBQueueElements_01 end  if '1' = dbo.QBM_FGIConfigparmValue('Common\JobQueueStats') begin select @JustNow = GetUTCDate() insert into JobQueueStats (UID_JobQueueStats9, queue , jobYear, jobMonth , jobDay , jobHour , countInserted, countActivated, countDeleted ) select 'XX'+convert(varchar(64), newid()), d.queue , year10(@JustNow) ,month(@JustNow) ,day( @JustNow) , datepart(hh,@JustNow) , (-1) * count(*) , 0, 0 from inserted d group by d.queue end  if exists (select top11 1 1 from inserted i where i.LimitationWarning + i.LimitationCount > 0 ) begin    if exists (select top 1 1 from inserted i where i.LimitationCount > 012 ) begin  insert into @limitedChains (ContentShort ) select name from jobchain where limitationcount > 0 if @@rowcount = 0 begin goto NachLimitationCount13 end  merge into jobqueue as t using  ( select z.Jobchainname, max(z.AltBestand) * (-1) as AlteOverlimit from ( select q.limitationcount, l.ContentShort14 as Jobchainname , case ready2exe when N'OVERLIMIT' then 1 else 0 end as altbestand from Jobqueue q with (readpast, index (QBM_XI7JobQueue)) join @limitedChains15 l on q.jobchainname = l.ContentShort  where q.Ready2EXE like N'[^DH][^A]%'  and q.IsRootJob = 1 and q.limitationcount > 0  and q.JobChainName in (select16 distinct i.JobChainName from inserted i ) ) as z group by z.Jobchainname having count(*) > max(z.LimitationCount) ) as s on t.JobChainName = s.JobChainName17 and t.Ready2EXE = N'TRUE' and t.IsRootJob = 1 and t.LimitationCount > 0 when matched then update set t.ready2exe = N'OVERLIMIT' , t.xtouched = 'N' , t.xdateupdated18 = dateadd(ss, s.alteOverlimit , @JustNow) ; if @@rowcount > 0 begin  declare @ElementBufferMulti_01 QBM_YCursorBuffer insert into @ElementBufferMulti_0119 (UID1) select min(q.UID_Job)  from JobQueue q with (readpast) where q.IsRootJob = 1 and q.Ready2EXE = N'OVERLIMIT' and q.XDateUpdated = @JustNow group20 by q.Queue, q.JobChainName select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while21 @ElementIndex <= @ElementLast begin select top 1 @uid_job = bu.UID1 from @ElementBufferMulti_01 bu where bu.ElementIndex = @ElementIndex select @SQLWhereClause22 = N' uid_job = ''' + rtrim(@uid_job) + N''''  exec QBM_PJobCreate_HOFireEvent 'JobQueue', @SQLWhereClause, 'OVERLIMITDETECT', @GenProcID = @GenProcID 23, @ObjectKeysAffected = DEFAULT select @ElementIndex += 1 end  end end     NachLimitationCount:    if exists (select top 1 1 from inserted i where i.LimitationWarning24 > 0 ) begin declare @ElementBufferMulti_02 QBM_YCursorBuffer insert into @ElementBufferMulti_02 (ContentShort, UID1) select x.JobChainName, min(x.UID_Job25)  from (  select i.JobChainName, i.UID_Job, i.LimitationWarning, 1 As IsNew, 0 as IsOverlimitRaised from inserted i where i.IsRootJob = 1 and i.Ready2EXE26 like N'[^DH][^A]%'  and i.LimitationWarning > 0 union all select j.JobChainName, j.UID_Job, j.LimitationWarning, 0 as isNew ,case j.Ready2EXE when N'OVERLIMIT'27 then 1 else 0 end as IsOverlimitRaised from JobQueue j with (readpast) join ( select distinct ii.JobChainName from inserted ii ) i on j.JobChainName =28 i.JobChainName   where j.IsRootJob = 1 and j.Ready2EXE like N'[^DH][^A]%'  and j.LimitationWarning > 0  and Not exists (select top 1 1 from inserted i229 where i2.UID_Job = j.UID_Job ) ) as x group by x.JobChainName having count(*) > max(x.LimitationWarning)  and MAX(x.IsNew ) = 1  and MAX(x.IsOverlimitRaised30) = 0 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast31 begin select top 1 @JobchainName = bu.ContentShort , @uid_job = bu.UID1 from @ElementBufferMulti_02 bu where bu.ElementIndex = @ElementIndex update JobQueue32 set StartAt = dateadd(mi, 1, StartAt) , XDateUpdated = @Xdate , XUserUpdated = @XUser where UID_Job = @uid_job select @SQLWhereClause = N' uid_job = '''33 + rtrim(@uid_job) + N'''' exec QBM_PJobCreate_HOFireEvent 'JobQueue', @SQLWhereClause, 'OVERLIMITWARNING', @GenProcID = @GenProcID , @ObjectKeysAffected34 = DEFAULT , @checkForExisting = 1 select @ElementIndex += 1 end  end     end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 1835, 1) WITH NOWAIT END CATCH end 36