Back to OIM Explorer

dbo.QER_TUQERAssign

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on QERAssign. Bulk DBQueue insert -> QER-K-PersonHasQERAssign / QER_ZPersonHasQERAssign at line 12; Bulk DBQueue insert -> QER-K-OrgHasQERAssign / QER_ZOrgHasQERAssign at line 12; Bulk DBQueue insert -> QER-K-PersonHasQERAssign / QER_ZPersonHasQERAssign at line 12; Bulk DBQueue insert -> QER-K-OrgHasQERAssign / QER_ZOrgHasQERAssign at line 16

Source: sandbox-db sys.sql_modules

Source size: 5.493 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.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> QER-K-PersonHasQERAssign / QER_ZPersonHasQERAssign at line 12
  • Bulk DBQueue insert -> QER-K-OrgHasQERAssign / QER_ZOrgHasQERAssign at line 12
  • Bulk DBQueue insert -> QER-K-PersonHasQERAssign / QER_ZPersonHasQERAssign at line 12
  • Bulk DBQueue insert -> QER-K-OrgHasQERAssign / QER_ZOrgHasQERAssign at line 16
  • Bulk DBQueue insert -> QER-K-PersonHasQERAssign / QER_ZPersonHasQERAssign at line 16
  • Bulk DBQueue insert -> QER-K-PersonHasQERAssign / QER_ZPersonHasQERAssign at line 22
  • References QBM_PJobCreate*
  • References QBM_PDBQueueInsert_Bulk
  • Trigger parent table: QERAssign

Typed Edges

  • queues DBQueue task QER_ZPersonHasQERAssign at line 12 Bulk DBQueue insert -> QER-K-PersonHasQERAssign / QER_ZPersonHasQERAssign at line 12
  • queues DBQueue task QER_ZOrgHasQERAssign at line 12 Bulk DBQueue insert -> QER-K-OrgHasQERAssign / QER_ZOrgHasQERAssign at line 12
  • queues DBQueue task QER_ZOrgHasQERAssign at line 16 Bulk DBQueue insert -> QER-K-OrgHasQERAssign / QER_ZOrgHasQERAssign at line 16
  • queues DBQueue task QER_ZPersonHasQERAssign at line 16 Bulk DBQueue insert -> QER-K-PersonHasQERAssign / QER_ZPersonHasQERAssign at line 16
  • queues DBQueue task QER_ZPersonHasQERAssign at line 22 Bulk DBQueue insert -> QER-K-PersonHasQERAssign / QER_ZPersonHasQERAssign at line 22
  • trigger on table QERAssign Trigger parent table: QERAssign
  • references source dbo.QBM_FCVBitToString source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOUpdate source text reference
  • references source dbo.QBM_PJobCreate_HOUpdate_B source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_PIsForITShopFlagCheck source text reference
  • references source dbo.QER_PITShopHelperFill source text reference
  • references source dbo.QER_PITShopHelperFill_Recalc source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL231 lines
1CREATE trigger QER_TUQERAssign2  ON QERAssign FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @ObjectkeyOrdered varchar(138)7  DECLARE @UID_AccProduct varchar(38)8  DECLARE @whereclauseOrg nvarchar(max)9  DECLARE @whereclauseMuster nvarchar(max) = ' UID_ITShopOrg in ( select UID_OrgPR 10							from QER_VPWOProductNodesSlim11							where ObjectkeyOrdered = ''@ObjectkeyOrdered'' 12								and isnull(UID_AccProduct, '''') <> ''@UID_AccProduct''13					)14'15  DECLARE @ElementBuffer QBM_YCursorBuffer16  DECLARE @ElementCount int17  DECLARE @ElementIndex int18  BEGIN TRY19    IF EXISTS(20      SELECT TOP 1 121      FROM inserted)22    GOTO start23    IF EXISTS(24      SELECT TOP 1 125      FROM deleted)26    GOTO start27    RETURN start:28    DECLARE @GenProcID varchar(38)29    SELECT @GenProcID = dbo.QBM_FGISessionContext('')30    IF31    UPDATE(UID_QERAssignPredecessor)32    BEGIN33      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw34      INSERT INTO @DBQueueElements_01(object,35      subobject,36      genprocid)37      SELECT38        x.uid,39        NULL,40        @GenProcID41      FROM(42      SELECT43        DISTINCT phr.UID_Person AS uid44      FROM QERAssign v45      JOIN deleted d46        ON d.uid_QERAssign = v.UID_QERAssign AND isnull(v.UID_QERAssignPredecessor, '') <> ISNULL(d.UID_QERAssignPredecessor,47      '')48      JOIN PersonHasQERAssign phr49        ON phr.UID_QERAssign = v.UID_QERAssign AND phr.XOrigin > 0) AS x50      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-PersonHasQERAssign',51        @DBQueueElements_0152      DECLARE @DBQueueElements_02 QBM_YDBQueueRaw53      INSERT INTO @DBQueueElements_02(object,54      subobject,55      genprocid)56      SELECT57        x.uid,58        NULL,59        @GenProcID60      FROM(61      SELECT62        DISTINCT phr.UID_org AS uid63      FROM QERAssign v64      JOIN deleted d65        ON d.uid_QERAssign = v.UID_QERAssign AND isnull(v.UID_QERAssignPredecessor, '') <> ISNULL(d.UID_QERAssignPredecessor,66      '')67      JOIN BaseTreeHasQERAssign phr68        ON phr.UID_QERAssign = v.UID_QERAssign AND phr.XOrigin > 0) AS x69      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-OrgHasQERAssign',70        @DBQueueElements_0271    END72    IF73    UPDATE(isForITShop) OR74    UPDATE(isITShopOnly)75    BEGIN76      DECLARE @FlagTest QBM_YParameterlist77      INSERT INTO @FlagTest(Parameter1,78      Parameter2,79      HasContentFull,80      e.Parameter3)81      SELECT82        i.UID_QERAssign,83        i.XObjectKey,84        i.IsForITShop,85        dbo.QBM_FCVBitToString(i.IsITShopOnly)86      FROM inserted i87      EXEC QER_PIsForITShopFlagCheck 'QERAssign',88        @FlagTest89    END90    IF91    UPDATE(IsNoInheriteToSecurityIncident)92    BEGIN93      DECLARE @DBQueueElements_03 QBM_YDBQueueRaw94      INSERT INTO @DBQueueElements_03(object,95      subobject,96      genprocid)97      SELECT98        x.uid,99        NULL,100        @GenProcID101      FROM(102      SELECT103        DISTINCT phr.UID_Person AS uid104      FROM QERAssign v105      JOIN deleted d106        ON d.uid_QERAssign = v.UID_QERAssign107      JOIN PersonHasQERAssign phr108        ON phr.UID_QERAssign = v.UID_QERAssign AND phr.XOrigin > 0109      WHERE110        (d.IsNoInheriteToSecurityIncident = 1 AND v.IsNoInheriteToSecurityIncident = 0 AND phr.XIsInEffect = 0) OR(d.IsNoInheriteToSecurityIncident111      = 0 AND v.IsNoInheriteToSecurityIncident = 1 AND phr.XIsInEffect = 1)) AS x112      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-PersonHasQERAssign',113        @DBQueueElements_03114    END115    IF116    UPDATE(uid_accproduct)117    BEGIN118      INSERT INTO @ElementBuffer(ObjectKey1,119      UID1)120      SELECT121        x.XObjectKey,122        isnull(x.uid_accproduct,123        '')124      FROM QERAssign x125      JOIN deleted d126        ON x.uid_QERAssign = d.uid_QERAssign AND isnull(x.uid_accproduct,127      '') <> isnull(d.uid_accproduct,128      '') AND d.uid_accproduct > ' '129      JOIN dialogColumn c130        ON c.UID_DialogTable = 'QER-T-ITShopOrg' AND c.columnname = 'uid_ACCProduct' AND c.IsDeactivatedByPreProcessor = 0131      SELECT @ElementCount = @@ROWCOUNT132      SELECT @ElementIndex = 1133      WHILE @ElementIndex <= @ElementCount134      BEGIN135        SELECT136          TOP 1 @ObjectkeyOrdered = bu.ObjectKey1,137          @UID_AccProduct = bu.UID1138        FROM @ElementBuffer bu139        WHERE140          bu.ElementIndex = @ElementIndex141        SELECT @whereclauseOrg = @whereclauseMuster142        SELECT143          @whereclauseOrg = replace(@whereclauseOrg,144          N '@ObjectkeyOrdered',145          @ObjectkeyOrdered)146        SELECT147          @whereclauseOrg = replace(@whereclauseOrg,148          N '@uid_accproduct',149          rtrim(@uid_accproduct))150        EXEC QBM_PJobCreate_HOUpdate_B N 'ITShopOrg',151          @whereclauseOrg,152          @GenProcID,153          @p1 = 'uid_ACCProduct',154          @v1 = @uid_accproduct,155          @AdditionalObjectKeysAffected = DEFAULT156        SELECT @ElementIndex += 1157      END158    END159    IF160    UPDATE(UID_AccProduct)161    BEGIN162      DECLARE @RecalcParameter_OA QBM_YCursorBuffer163      INSERT INTO @RecalcParameter_OA(UID1,164      ObjectKey1,165      Ident1)166      SELECT167        'QER-PWODecisionRule-OA',168        a.XObjectKey,169        'I'170      FROM inserted i171      JOIN QERAssign g172        ON i.UID_QERAssign = g.UID_QERAssign173      JOIN QERAccProductUsage u174        ON g.XObjectKey = u.XObjectKey175      JOIN AccProduct a176        ON u.UID_AccProduct = a.UID_AccProduct177      UNION178      SELECT179        'QER-PWODecisionRule-OA',180        a.XObjectKey,181        'I'182      FROM deleted d183      JOIN QERAssign g184        ON d.UID_QERAssign = g.UID_QERAssign185      JOIN QERAccProductUsage u186        ON g.XObjectKey = u.XObjectKey187      JOIN AccProduct a188        ON u.UID_AccProduct = a.UID_AccProduct189      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_OA,190        @GenprocID191      DECLARE @RecalcParameter_OA2 QBM_YCursorBuffer192      INSERT INTO @RecalcParameter_OA2(UID1,193      ObjectKey1,194      Ident1)195      SELECT196        'QER-PWODecisionRule-OA',197        pwo.XObjectKey,198        'I'199      FROM inserted i200      JOIN QERAssign g201        ON i.UID_QERAssign = g.UID_QERAssign202      JOIN AccProduct a203        ON i.UID_AccProduct = a.UID_AccProduct204      JOIN PersonWantsOrg pwo205        ON g.XObjectKey = pwo.ObjectKeyElementUsedInAssign206      UNION207      SELECT208        'QER-PWODecisionRule-OA',209        pwo.XObjectKey,210        'I'211      FROM deleted d212      JOIN QERAssign g213        ON d.UID_QERAssign = g.UID_QERAssign214      JOIN AccProduct a215        ON d.UID_AccProduct = a.UID_AccProduct216      JOIN PersonWantsOrg pwo217        ON g.XObjectKey = pwo.ObjectKeyElementUsedInAssign218      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_OA2,219        @GenprocID220    END221  END TRY222  BEGIN CATCH223    EXEC QBM_PSessionErrorAdd DEFAULT224    RAISERROR('',225    18,226    1)227      WITH NOWAIT228  END CATCH229  ende:230  RETURN231END
Open raw exported source
SQL ยท Raw40 lines
1 create   trigger QER_TUQERAssign on QERAssign  for Update not for Replication as begin  declare @ObjectkeyOrdered varchar(138) declare @UID_AccProduct2 varchar(38) declare @whereclauseOrg nvarchar(max) declare @whereclauseMuster nvarchar(max) = ' UID_ITShopOrg in ( select UID_OrgPR 3							from QER_VPWOProductNodesSlim4							where ObjectkeyOrdered = ''@ObjectkeyOrdered'' 5								and isnull(UID_AccProduct, '''') <> ''@UID_AccProduct''6					)7'8 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int BEGIN TRY if exists (select top 1 1 from inserted) goto start9 if exists (select top 1 1 from deleted) goto start return start: declare @GenProcID varchar(38) select @GenProcID = dbo.QBM_FGISessionContext('') if update10(UID_QERAssignPredecessor) begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid11, null, @GenProcID from ( select distinct phr.UID_Person as uid from QERAssign v join deleted d on d.uid_QERAssign = v.UID_QERAssign and isnull(v.UID_QERAssignPredecessor12, '') <> ISNULL(d.UID_QERAssignPredecessor, '') join PersonHasQERAssign phr on phr.UID_QERAssign = v.UID_QERAssign and phr.XOrigin > 0  ) as x exec QBM_PDBQueueInsert_Bulk13 'QER-K-PersonHasQERAssign', @DBQueueElements_01 declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid14) select x.uid, null, @GenProcID from ( select distinct phr.UID_org as uid from QERAssign v join deleted d on d.uid_QERAssign = v.UID_QERAssign and isnull15(v.UID_QERAssignPredecessor, '') <> ISNULL(d.UID_QERAssignPredecessor, '') join BaseTreeHasQERAssign phr on phr.UID_QERAssign = v.UID_QERAssign and phr.XOrigin16 > 0  ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-OrgHasQERAssign', @DBQueueElements_02 end if update(isForITShop) or update(isITShopOnly) begin declare17 @FlagTest QBM_YParameterlist insert into @FlagTest(Parameter1, Parameter2, HasContentFull, e.Parameter3) select i.UID_QERAssign, i.XObjectKey, i.IsForITShop18, dbo.QBM_FCVBitToString(i.IsITShopOnly) from inserted i exec QER_PIsForITShopFlagCheck 'QERAssign', @FlagTest end  if update(IsNoInheriteToSecurityIncident19) begin declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object, subobject, genprocid) select x.uid, null, @GenProcID from 20( select distinct phr.UID_Person as uid from QERAssign v join deleted d on d.uid_QERAssign = v.UID_QERAssign join PersonHasQERAssign phr on phr.UID_QERAssign21 = v.UID_QERAssign and phr.XOrigin > 0  where (d.IsNoInheriteToSecurityIncident = 1 and v.IsNoInheriteToSecurityIncident = 0 and phr.XIsInEffect = 0 ) 22or (d.IsNoInheriteToSecurityIncident = 0 and v.IsNoInheriteToSecurityIncident = 1 and phr.XIsInEffect = 1 ) ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-PersonHasQERAssign'23, @DBQueueElements_03 end if update(uid_accproduct) begin insert into @ElementBuffer(ObjectKey1, UID1) select x.XObjectKey, isnull(x.uid_accproduct,'')24 from QERAssign x join deleted d on x.uid_QERAssign = d.uid_QERAssign and isnull(x.uid_accproduct,'') <> isnull(d.uid_accproduct,'')  and d.uid_accproduct25 > ' '  join dialogColumn c on c.UID_DialogTable = 'QER-T-ITShopOrg' and c.columnname = 'uid_ACCProduct' and c.IsDeactivatedByPreProcessor = 0  select 26@ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @ObjectkeyOrdered = bu.ObjectKey1 , @UID_AccProduct27 = bu.UID1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @whereclauseOrg = @whereclauseMuster select @whereclauseOrg = replace(@whereclauseOrg28, N'@ObjectkeyOrdered' , @ObjectkeyOrdered) select @whereclauseOrg = replace(@whereclauseOrg, N'@uid_accproduct' , rtrim(@uid_accproduct))    exec QBM_PJobCreate_HOUpdate_B29 N'ITShopOrg', @whereclauseOrg, @GenProcID , @p1 = 'uid_ACCProduct', @v1 = @uid_accproduct , @AdditionalObjectKeysAffected = DEFAULT select @ElementIndex30 += 1 end  end  if update (UID_AccProduct) begin declare @RecalcParameter_OA QBM_YCursorBuffer insert into @RecalcParameter_OA (UID1, ObjectKey1, Ident131) select 'QER-PWODecisionRule-OA', a.XObjectKey, 'I' from inserted i join QERAssign g on i.UID_QERAssign = g.UID_QERAssign join QERAccProductUsage u on32 g.XObjectKey = u.XObjectKey join AccProduct a on u.UID_AccProduct = a.UID_AccProduct union select 'QER-PWODecisionRule-OA', a.XObjectKey, 'I' from deleted33 d join QERAssign g on d.UID_QERAssign = g.UID_QERAssign join QERAccProductUsage u on g.XObjectKey = u.XObjectKey join AccProduct a on u.UID_AccProduct34 = a.UID_AccProduct exec QER_PITShopHelperFill_Recalc @RecalcParameter_OA, @GenprocID declare @RecalcParameter_OA2 QBM_YCursorBuffer insert into @RecalcParameter_OA235 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-OA', pwo.XObjectKey, 'I' from inserted i join QERAssign g on i.UID_QERAssign = g.UID_QERAssign 36join AccProduct a on i.UID_AccProduct = a.UID_AccProduct join PersonWantsOrg pwo on g.XObjectKey = pwo.ObjectKeyElementUsedInAssign union select 'QER-PWODecisionRule-OA'37, pwo.XObjectKey, 'I' from deleted d join QERAssign g on d.UID_QERAssign = g.UID_QERAssign join AccProduct a on d.UID_AccProduct = a.UID_AccProduct join38 PersonWantsOrg pwo on g.XObjectKey = pwo.ObjectKeyElementUsedInAssign exec QER_PITShopHelperFill_Recalc @RecalcParameter_OA2, @GenprocID end  END TRY 39BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end 40