Back to OIM Explorer

dbo.TSB_TUUNSGroupB

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on UNSGroupB. Bulk DBQueue insert -> TSB-K-UNSAccountBInUNSGroupB / TSB_ZUNSAccountBInUNSGroupB at line 40; References QBM_PJobCreate*; References QBM_PDBQueueInsert_Bulk; Trigger parent table: UNSGroupB

Source: sandbox-db sys.sql_modules

Source size: 5.689 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 -> TSB-K-UNSAccountBInUNSGroupB / TSB_ZUNSAccountBInUNSGroupB at line 40
  • References QBM_PJobCreate*
  • References QBM_PDBQueueInsert_Bulk
  • Trigger parent table: UNSGroupB

Typed Edges

  • queues DBQueue task TSB_ZUNSAccountBInUNSGroupB at line 40 Bulk DBQueue insert -> TSB-K-UNSAccountBInUNSGroupB / TSB_ZUNSAccountBInUNSGroupB at line 40
  • trigger on table UNSGroupB Trigger parent table: UNSGroupB
  • references source dbo.QBM_FCVBigIntToString source text reference
  • references source dbo.QBM_FGIBitPatternXMarkedForDel 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

Complete Source

SQL235 lines
1CREATE trigger TSB_TUUNSGroupB2  ON UNSGroupB FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @parameter nvarchar(256)7  DECLARE @ObjectkeyOrdered varchar(138)8  DECLARE @uid_accproduct varchar(38)9  DECLARE @whereclauseOrg nvarchar(max)10  DECLARE @whereclauseMuster nvarchar(max) = ' UID_ITShopOrg in ( select UID_OrgPR 11							from QER_VPWOProductNodesSlim12							where ObjectkeyOrdered = ''@ObjectkeyOrdered'' 13								and isnull(UID_AccProduct, '''') <> ''@UID_AccProduct''14					)15'16  DECLARE @ElementBuffer QBM_YCursorBuffer17  DECLARE @ElementCount int18  DECLARE @ElementIndex int19  DECLARE @QBM_BitPatternXMarkedForDel_Out int = dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',20  0)21  BEGIN TRY22    IF EXISTS(23      SELECT TOP 1 124      FROM inserted)25    GOTO start26    IF EXISTS(27      SELECT TOP 1 128      FROM deleted)29    GOTO start30    RETURN start:31    DECLARE @GenProcID varchar(38)32    SELECT @GenProcID = dbo.QBM_FGISessionContext('')33    IF34    UPDATE(isForITShop) OR35    UPDATE(isITShopOnly)36    BEGIN37      DECLARE @FlagTest QBM_YParameterlist38      INSERT INTO @FlagTest(Parameter1,39      Parameter2,40      HasContentFull,41      e.Parameter3)42      SELECT43        i.UID_UNSGroupB,44        i.XObjectKey,45        i.IsForITShop,46        dbo.QBM_FCVBigIntToString(i.IsITShopOnly)47      FROM inserted i48      EXEC QER_PIsForITShopFlagCheck 'UNSGroupB',49        @FlagTest,50      'UNSAccountBInUNSGroupB'51      EXEC QER_PIsForITShopFlagCheck 'UNSGroupB',52        @FlagTest,53      'UNSAccountBHasUNSGroupB'54    END55    IF56    UPDATE(uid_accproduct)57    BEGIN58      INSERT INTO @ElementBuffer(ObjectKey1,59      UID1)60      SELECT61        X.XObjectKey,62        isnull(x.uid_accproduct,63        '')64      FROM UNSGroupB x65      JOIN deleted d66        ON x.uid_UNSGroupB = d.uid_UNSGroupB AND isnull(x.uid_accproduct,67      '') <> isnull(d.uid_accproduct,68      '') AND d.uid_accproduct > ' '69      JOIN dialogColumn c70        ON c.UID_DialogTable = 'QER-T-ITShopOrg' AND c.columnname = 'uid_ACCProduct' AND c.IsDeactivatedByPreProcessor = 071      SELECT @ElementCount = @@rowcount72      SELECT @ElementIndex = 173      WHILE @ElementIndex <= @ElementCount74      BEGIN75        SELECT76          TOP 1 @ObjectkeyOrdered = bu.ObjectKey1,77          @UID_AccProduct = bu.UID178        FROM @ElementBuffer bu79        WHERE80          bu.ElementIndex = @ElementIndex81        SELECT @whereclauseOrg = @whereclauseMuster82        SELECT83          @whereclauseOrg = replace(@whereclauseOrg,84          N '@ObjectkeyOrdered',85          @ObjectkeyOrdered)86        SELECT87          @whereclauseOrg = replace(@whereclauseOrg,88          N '@uid_accproduct',89          rtrim(@uid_accproduct))90        EXEC QBM_PJobCreate_HOUpdate_B 'ITShopOrg',91          @whereclauseOrg,92          @GenProcID,93          @p1 = 'uid_ACCProduct',94          @v1 = @uid_accproduct,95          @AdditionalObjectKeysAffected = DEFAULT96        SELECT @ElementIndex += 197      END98    END99    IF100    UPDATE(UID_AccProduct)101    BEGIN102      DECLARE @RecalcParameter_OA QBM_YCursorBuffer103      INSERT INTO @RecalcParameter_OA(UID1,104      ObjectKey1,105      Ident1)106      SELECT107        'QER-PWODecisionRule-OA',108        a.XObjectKey,109        'I'110      FROM inserted i111      JOIN UNSGroupB g112        ON i.UID_UNSGroupB = g.UID_UNSGroupB113      JOIN QERAccProductUsage u114        ON g.XObjectKey = u.XObjectKey115      JOIN AccProduct a116        ON u.UID_AccProduct = a.UID_AccProduct117      UNION118      SELECT119        'QER-PWODecisionRule-OA',120        a.XObjectKey,121        'I'122      FROM deleted d123      JOIN UNSGroupB g124        ON d.UID_UNSGroupB = g.UID_UNSGroupB125      JOIN QERAccProductUsage u126        ON g.XObjectKey = u.XObjectKey127      JOIN AccProduct a128        ON u.UID_AccProduct = a.UID_AccProduct129      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_OA,130        @GenprocID131      DECLARE @RecalcParameter_OA2 QBM_YCursorBuffer132      INSERT INTO @RecalcParameter_OA2(UID1,133      ObjectKey1,134      Ident1)135      SELECT136        'QER-PWODecisionRule-OA',137        pwo.XObjectKey,138        'I'139      FROM inserted i140      JOIN UNSGroupB g141        ON i.UID_UNSGroupB = g.UID_UNSGroupB142      JOIN AccProduct a143        ON i.UID_AccProduct = a.UID_AccProduct144      JOIN PersonWantsOrg pwo145        ON g.XObjectKey = pwo.ObjectKeyElementUsedInAssign146      UNION147      SELECT148        'QER-PWODecisionRule-OA',149        pwo.XObjectKey,150        'I'151      FROM deleted d152      JOIN UNSGroupB g153        ON d.UID_UNSGroupB = g.UID_UNSGroupB154      JOIN AccProduct a155        ON d.UID_AccProduct = a.UID_AccProduct156      JOIN PersonWantsOrg pwo157        ON g.XObjectKey = pwo.ObjectKeyElementUsedInAssign158      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_OA2,159        @GenprocID160    END161    IF162    UPDATE(MatchPatternForMembership) OR163    UPDATE(XMarkedForDeletion)164    BEGIN165      DECLARE @GroupsChanged QBM_YParameterList166      INSERT INTO @GroupsChanged(Parameter1,167      Parameter2)168      SELECT169        g.UID_UNSGroupB AS uid,170        g.XObjectKey171      FROM UNSGroupB g172      JOIN deleted d173        ON g.UID_UNSGroupB = d.UID_UNSGroupB174      WHERE175        (g.MatchPatternForMembership <> d.MatchPatternForMembership) OR(g.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out = 0 AND d.XMarkedForDeletion176      & @QBM_BitPatternXMarkedForDel_Out > 0)177      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw178      INSERT INTO @DBQueueElements_01(object,179      subobject,180      genprocid)181      SELECT182        x.uid,183        NULL,184        @GenProcID185      FROM(186      SELECT aig.UID_UNSAccountB AS uid187      FROM @GroupsChanged g188      JOIN UNSAccountBInUNSGroupB aig189        ON g.Parameter1 = aig.UID_UNSGroupB AND aig.XOrigin > 0190      UNION191      SELECT aig.UID_UNSAccountB AS uid192      FROM @GroupsChanged g193      JOIN UNSAccountBHasUNSGroupB aig194        ON g.Parameter1 = aig.UID_UNSGroupB AND aig.XOrigin > 0195      UNION196      SELECT u.UID_UNSAccountB AS uid197      FROM @GroupsChanged g198      JOIN BaseTreeHasObject bho199        ON g.Parameter2 = bho.ObjectKey200      JOIN PersonInBaseTree pho201        ON bho.UID_Org = pho.UID_Org202      JOIN UNSAccountB u203        ON u.UID_Person = pho.UID_Person204      UNION205      SELECT u.UID_UNSAccountB AS uid206      FROM @GroupsChanged g207      JOIN BaseTreeHasObject bho208        ON g.Parameter2 = bho.ObjectKey209      JOIN HelperPersonOrg pho210        ON bho.UID_Org = pho.UID_Org211      JOIN UNSAccountB u212        ON u.UID_Person = pho.UID_Person213      UNION214      SELECT u.UID_UNSAccountB AS uid215      FROM @GroupsChanged g216      JOIN ESetHasEntitlement ehe217        ON g.Parameter2 = ehe.Entitlement218      JOIN PersonHasESet phe219        ON phe.UID_ESet = ehe.UID_ESet220      JOIN UNSAccountB u221        ON u.UID_Person = phe.UID_Person) AS x222      EXEC QBM_PDBQueueInsert_Bulk 'TSB-K-UNSAccountBInUNSGroupB',223        @DBQueueElements_01224    END225  END TRY226  BEGIN CATCH227    EXEC QBM_PSessionErrorAdd DEFAULT228    RAISERROR('',229    18,230    1)231      WITH NOWAIT232  END CATCH233  ende:234  RETURN235END
Open raw exported source
SQL ยท Raw42 lines
1create   trigger TSB_TUUNSGroupB on UNSGroupB  for Update not for Replication as begin declare @parameter nvarchar(256) declare @ObjectkeyOrdered2 varchar(138) declare @uid_accproduct 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 declare @QBM_BitPatternXMarkedForDel_Out int = dbo.QBM_FGIBitPatternXMarkedForDel9('|OutStanding|', 0)  BEGIN TRY if exists (select top 1 1 from inserted) goto start if exists (select top 1 1 from deleted) goto start return start: declare10 @GenProcID varchar(38) select @GenProcID = dbo.QBM_FGISessionContext('') if update(isForITShop) or update(isITShopOnly) begin declare @FlagTest QBM_YParameterlist11 insert into @FlagTest(Parameter1, Parameter2, HasContentFull, e.Parameter3) select i.UID_UNSGroupB, i.XObjectKey, i.IsForITShop, dbo.QBM_FCVBigIntToString12(i.IsITShopOnly) from inserted i exec QER_PIsForITShopFlagCheck 'UNSGroupB', @FlagTest, 'UNSAccountBInUNSGroupB'  exec QER_PIsForITShopFlagCheck 13'UNSGroupB', @FlagTest, 'UNSAccountBHasUNSGroupB'  end   if update(uid_accproduct) begin insert into @ElementBuffer(ObjectKey1, UID1) select X.XObjectKey14, isnull(x.uid_accproduct,'') from UNSGroupB x join deleted d on x.uid_UNSGroupB = d.uid_UNSGroupB and isnull(x.uid_accproduct,'') <> isnull(d.uid_accproduct15,'')  and d.uid_accproduct > ' '  join dialogColumn c on c.UID_DialogTable = 'QER-T-ITShopOrg' and c.columnname = 'uid_ACCProduct' and c.IsDeactivatedByPreProcessor16 = 0  select @ElementCount = @@rowcount select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @ObjectkeyOrdered = bu.ObjectKey117 , @UID_AccProduct = bu.UID1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @whereclauseOrg = @whereclauseMuster select @whereclauseOrg18 = replace(@whereclauseOrg, N'@ObjectkeyOrdered' , @ObjectkeyOrdered) select @whereclauseOrg = replace(@whereclauseOrg, N'@uid_accproduct' , rtrim(@uid_accproduct19))    exec QBM_PJobCreate_HOUpdate_B 'ITShopOrg', @whereclauseOrg, @GenProcID , @p1 = 'uid_ACCProduct', @v1 = @uid_accproduct , @AdditionalObjectKeysAffected20 = DEFAULT select @ElementIndex += 1 end   end  if update (UID_AccProduct) begin declare @RecalcParameter_OA QBM_YCursorBuffer insert into @RecalcParameter_OA21 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-OA', a.XObjectKey, 'I' from inserted i join UNSGroupB g on i.UID_UNSGroupB = g.UID_UNSGroupB22 join QERAccProductUsage u on g.XObjectKey = u.XObjectKey join AccProduct a on u.UID_AccProduct = a.UID_AccProduct union select 'QER-PWODecisionRule-OA'23, a.XObjectKey, 'I' from deleted d join UNSGroupB g on d.UID_UNSGroupB = g.UID_UNSGroupB join QERAccProductUsage u on g.XObjectKey = u.XObjectKey24 join AccProduct a on u.UID_AccProduct = a.UID_AccProduct exec QER_PITShopHelperFill_Recalc @RecalcParameter_OA, @GenprocID declare @RecalcParameter_OA225 QBM_YCursorBuffer insert into @RecalcParameter_OA2 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-OA', pwo.XObjectKey, 'I' from inserted i join26 UNSGroupB g on i.UID_UNSGroupB = g.UID_UNSGroupB join AccProduct a on i.UID_AccProduct = a.UID_AccProduct join PersonWantsOrg pwo on g.XObjectKey27 = pwo.ObjectKeyElementUsedInAssign union select 'QER-PWODecisionRule-OA', pwo.XObjectKey, 'I' from deleted d join UNSGroupB g on d.UID_UNSGroupB28 = g.UID_UNSGroupB join AccProduct a on d.UID_AccProduct = a.UID_AccProduct join PersonWantsOrg pwo on g.XObjectKey = pwo.ObjectKeyElementUsedInAssign29 exec QER_PITShopHelperFill_Recalc @RecalcParameter_OA2, @GenprocID end  if update(MatchPatternForMembership)  or update(XMarkedForDeletion) begin  declare30 @GroupsChanged QBM_YParameterList insert into @GroupsChanged (Parameter1, Parameter2) select g.UID_UNSGroupB as uid, g.XObjectKey from UNSGroupB31 g join deleted d on g.UID_UNSGroupB = d.UID_UNSGroupB where ( g.MatchPatternForMembership <> d.MatchPatternForMembership ) or (g.XMarkedForDeletion32 & @QBM_BitPatternXMarkedForDel_Out = 0 and d.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out > 0 ) declare @DBQueueElements_01 QBM_YDBQueueRaw insert33 into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from (  select aig.UID_UNSAccountB as uid from @GroupsChanged g34 join UNSAccountBInUNSGroupB aig on g.Parameter1 = aig.UID_UNSGroupB and aig.XOrigin > 0  union select aig.UID_UNSAccountB as uid from @GroupsChanged35 g join UNSAccountBHasUNSGroupB aig on g.Parameter1 = aig.UID_UNSGroupB and aig.XOrigin > 0   union select u.UID_UNSAccountB as uid from @GroupsChanged36 g join BaseTreeHasObject bho on g.Parameter2  = bho.ObjectKey join PersonInBaseTree pho on bho.UID_Org = pho.UID_Org join UNSAccountB u on u.UID_Person37 = pho.UID_Person union select u.UID_UNSAccountB as uid from @GroupsChanged g join BaseTreeHasObject bho on g.Parameter2  = bho.ObjectKey join HelperPersonOrg38 pho on bho.UID_Org = pho.UID_Org join UNSAccountB u on u.UID_Person = pho.UID_Person  union select u.UID_UNSAccountB as uid from @GroupsChanged g join39 ESetHasEntitlement ehe on g.Parameter2  = ehe.Entitlement join PersonHasESet phe on phe.UID_ESet = ehe.UID_ESet join UNSAccountB u on u.UID_Person = phe.UID_Person40 ) as x exec QBM_PDBQueueInsert_Bulk 'TSB-K-UNSAccountBInUNSGroupB', @DBQueueElements_01 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR41 ('', 18, 1) WITH NOWAIT END CATCH ende: return end 42