Back to OIM Explorer

dbo.TSB_TUTSBAccountDef

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on TSBAccountDef. Bulk DBQueue insert -> TSB-K-TSBAccountDefFlags / TSB_ZTSBAccountDefFlags at line 22; Bulk DBQueue insert -> TSB-K-PersonHasTSBAccountDef / TSB_ZPersonHasTSBAccountDef at line 22; Bulk DBQueue insert -> TSB-K-OrgHasTSBAccountDef / TSB_ZOrgHasTSBAccountDef at line 22; Bulk DBQueue insert -> TSB-K-PersonHasTSBAccountDef / TSB_ZPersonHasTSBAccountDef at line 25

Source: sandbox-db sys.sql_modules

Source size: 6.322 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-TSBAccountDefFlags / TSB_ZTSBAccountDefFlags at line 22
  • Bulk DBQueue insert -> TSB-K-PersonHasTSBAccountDef / TSB_ZPersonHasTSBAccountDef at line 22
  • Bulk DBQueue insert -> TSB-K-OrgHasTSBAccountDef / TSB_ZOrgHasTSBAccountDef at line 22
  • Bulk DBQueue insert -> TSB-K-PersonHasTSBAccountDef / TSB_ZPersonHasTSBAccountDef at line 25
  • Bulk DBQueue insert -> TSB-K-OrgHasTSBAccountDef / TSB_ZOrgHasTSBAccountDef at line 25
  • Bulk DBQueue insert -> TSB-K-OrgHasTSBAccountDef / TSB_ZOrgHasTSBAccountDef at line 27
  • References QBM_PJobCreate*
  • References QBM_PDBQueueInsert_Bulk
  • Trigger parent table: TSBAccountDef

Typed Edges

  • queues DBQueue task TSB_ZTSBAccountDefFlags at line 22 Bulk DBQueue insert -> TSB-K-TSBAccountDefFlags / TSB_ZTSBAccountDefFlags at line 22
  • queues DBQueue task TSB_ZPersonHasTSBAccountDef at line 22 Bulk DBQueue insert -> TSB-K-PersonHasTSBAccountDef / TSB_ZPersonHasTSBAccountDef at line 22
  • queues DBQueue task TSB_ZOrgHasTSBAccountDef at line 22 Bulk DBQueue insert -> TSB-K-OrgHasTSBAccountDef / TSB_ZOrgHasTSBAccountDef at line 22
  • queues DBQueue task TSB_ZPersonHasTSBAccountDef at line 25 Bulk DBQueue insert -> TSB-K-PersonHasTSBAccountDef / TSB_ZPersonHasTSBAccountDef at line 25
  • queues DBQueue task TSB_ZOrgHasTSBAccountDef at line 25 Bulk DBQueue insert -> TSB-K-OrgHasTSBAccountDef / TSB_ZOrgHasTSBAccountDef at line 25
  • queues DBQueue task TSB_ZOrgHasTSBAccountDef at line 27 Bulk DBQueue insert -> TSB-K-OrgHasTSBAccountDef / TSB_ZOrgHasTSBAccountDef at line 27
  • trigger on table TSBAccountDef Trigger parent table: TSBAccountDef
  • references source dbo.QBM_FCVBigIntToString 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

SQL254 lines
1CREATE trigger TSB_TUTSBAccountDef2  ON TSBAccountDef 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    DECLARE @TSBAccountDefsToCheck QBM_YSingleGUID31    IF32    UPDATE(isForITShop) OR33    UPDATE(isITShopOnly)34    BEGIN35      DECLARE @FlagTest QBM_YParameterlist36      INSERT INTO @FlagTest(Parameter1,37      Parameter2,38      HasContentFull,39      e.Parameter3)40      SELECT41        i.UID_TSBAccountDef,42        i.XObjectKey,43        i.IsForITShop,44        dbo.QBM_FCVBigIntToString(i.IsITShopOnly)45      FROM inserted i46      EXEC QER_PIsForITShopFlagCheck 'TSBAccountDef',47        @FlagTest48    END49    IF50    UPDATE(PTDInheritAccountDef) OR51    UPDATE(PFDInheritAccountDef) OR52    UPDATE(PMDInheritAccountDef) OR53    UPDATE(PSIInheritAccountDef) OR54    UPDATE(UID_TSBAccountDefPredecessor)55    BEGIN56      INSERT INTO @TSBAccountDefsToCheck(UID_SingleGuid)57      SELECT r.uid_TSBAccountDef58      FROM TSBAccountDef r59      JOIN deleted d60        ON r.uid_TSBAccountDef = d.uid_TSBAccountDef61      WHERE62        (isnull(r.PTDInheritAccountDef, 0) <> isnull(d.PTDInheritAccountDef, 0) OR isnull(r.PFDInheritAccountDef,63      0) <> isnull(d.PFDInheritAccountDef, 0) OR isnull(r.PMDInheritAccountDef, 0) <> isnull(d.PMDInheritAccountDef,64      0) OR isnull(r.PSIInheritAccountDef, 0) <> isnull(d.PSIInheritAccountDef, 0) OR isnull(r.UID_TSBAccountDefPredecessor,65      '') <> isnull(d.UID_TSBAccountDefPredecessor, ''))66      UNION67      SELECT r.UID_TSBAccountDefPredecessor68      FROM TSBAccountDef r69      JOIN deleted d70        ON r.uid_TSBAccountDef = d.uid_TSBAccountDef71      WHERE72        isnull(r.UID_TSBAccountDefPredecessor,73      '') <> isnull(d.UID_TSBAccountDefPredecessor,74      '') AND r.UID_TSBAccountDefPredecessor > ' '75      UNION76      SELECT d.UID_TSBAccountDefPredecessor77      FROM TSBAccountDef r78      JOIN deleted d79        ON r.uid_TSBAccountDef = d.uid_TSBAccountDef80      WHERE81        isnull(r.UID_TSBAccountDefPredecessor,82      '') <> isnull(d.UID_TSBAccountDefPredecessor,83      '') AND d.UID_TSBAccountDefPredecessor > ' '84      IF 0 <(85        SELECT count(*)86      FROM @TSBAccountDefsToCheck)87      BEGIN88        DECLARE @DBQueueElements_01 QBM_YDBQueueRaw89        INSERT INTO @DBQueueElements_01(object,90        subobject,91        genprocid)92        SELECT93          x.uid,94          NULL,95          @GenProcID96        FROM(97        SELECT98          DISTINCT a.UID_TSBAccountDef AS uid99        FROM TSBAccountDef a100        JOIN @TSBAccountDefsToCheck c101          ON a.UID_TSBAccountDefPredecessor = c.UID_SingleGuid) AS x102        EXEC QBM_PDBQueueInsert_Bulk 'TSB-K-TSBAccountDefFlags',103          @DBQueueElements_01104        DECLARE @DBQueueElements_02 QBM_YDBQueueRaw105        INSERT INTO @DBQueueElements_02(object,106        subobject,107        genprocid)108        SELECT109          x.uid,110          NULL,111          @GenProcID112        FROM(113        SELECT114          DISTINCT a.UID_Person AS uid115        FROM PersonHasTSBAccountDef a116        JOIN @TSBAccountDefsToCheck c117          ON a.UID_TSBAccountDef = c.UID_SingleGuid) AS x118        EXEC QBM_PDBQueueInsert_Bulk 'TSB-K-PersonHasTSBAccountDef',119          @DBQueueElements_02120        DECLARE @DBQueueElements_03 QBM_YDBQueueRaw121        INSERT INTO @DBQueueElements_03(object,122        subobject,123        genprocid)124        SELECT125          x.uid,126          NULL,127          @GenProcID128        FROM(129        SELECT130          DISTINCT a.UID_Org AS uid131        FROM BaseTreeHasTSBAccountDef a132        JOIN @TSBAccountDefsToCheck c133          ON a.UID_TSBAccountDef = c.UID_SingleGuid AND a.XOrigin > 0) AS x134        EXEC QBM_PDBQueueInsert_Bulk 'TSB-K-OrgHasTSBAccountDef',135          @DBQueueElements_03136      END137    END138    IF139    UPDATE(uid_accproduct)140    BEGIN141      INSERT INTO @ElementBuffer(ObjectKey1,142      UID1)143      SELECT144        x.XObjectKey,145        isnull(x.uid_accproduct,146        '')147      FROM TSBAccountDef x148      JOIN deleted d149        ON x.uid_TSBAccountDef = d.uid_TSBAccountDef AND isnull(x.uid_accproduct,150      '') <> isnull(d.uid_accproduct,151      '') AND d.uid_accproduct > ' '152      JOIN dialogColumn c153        ON c.UID_DialogTable = 'QER-T-ITShopOrg' AND c.columnname = 'uid_ACCProduct' AND c.IsDeactivatedByPreProcessor = 0154      SELECT @ElementCount = @@rowcount155      SELECT @ElementIndex = 1156      WHILE @ElementIndex <= @ElementCount157      BEGIN158        SELECT159          TOP 1 @ObjectkeyOrdered = bu.ObjectKey1,160          @UID_AccProduct = bu.UID1161        FROM @ElementBuffer bu162        WHERE163          bu.ElementIndex = @ElementIndex164        SELECT @whereclauseOrg = @whereclauseMuster165        SELECT166          @whereclauseOrg = replace(@whereclauseOrg,167          N '@ObjectkeyOrdered',168          @ObjectkeyOrdered)169        SELECT170          @whereclauseOrg = replace(@whereclauseOrg,171          N '@uid_accproduct',172          rtrim(@uid_accproduct))173        EXEC QBM_PJobCreate_HOUpdate_B 'ITShopOrg',174          @whereclauseOrg,175          @GenProcID,176          @p1 = 'uid_ACCProduct',177          @v1 = @uid_accproduct,178          @AdditionalObjectKeysAffected = DEFAULT179        SELECT @ElementIndex += 1180      END181    END182    IF183    UPDATE(UID_AccProduct)184    BEGIN185      DECLARE @RecalcParameter_OA QBM_YCursorBuffer186      INSERT INTO @RecalcParameter_OA(UID1,187      ObjectKey1,188      Ident1)189      SELECT190        'QER-PWODecisionRule-OA',191        a.XObjectKey,192        'I'193      FROM inserted i194      JOIN TSBAccountDef g195        ON i.UID_TSBAccountDef = g.UID_TSBAccountDef196      JOIN QERAccProductUsage u197        ON g.XObjectKey = u.XObjectKey198      JOIN AccProduct a199        ON u.UID_AccProduct = a.UID_AccProduct200      UNION201      SELECT202        'QER-PWODecisionRule-OA',203        a.XObjectKey,204        'I'205      FROM deleted d206      JOIN TSBAccountDef g207        ON d.UID_TSBAccountDef = g.UID_TSBAccountDef208      JOIN QERAccProductUsage u209        ON g.XObjectKey = u.XObjectKey210      JOIN AccProduct a211        ON u.UID_AccProduct = a.UID_AccProduct212      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_OA,213        @GenprocID214      DECLARE @RecalcParameter_OA2 QBM_YCursorBuffer215      INSERT INTO @RecalcParameter_OA2(UID1,216      ObjectKey1,217      Ident1)218      SELECT219        'QER-PWODecisionRule-OA',220        pwo.XObjectKey,221        'I'222      FROM inserted i223      JOIN TSBAccountDef g224        ON i.UID_TSBAccountDef = g.UID_TSBAccountDef225      JOIN AccProduct a226        ON i.UID_AccProduct = a.UID_AccProduct227      JOIN PersonWantsOrg pwo228        ON g.XObjectKey = pwo.ObjectKeyElementUsedInAssign229      UNION230      SELECT231        'QER-PWODecisionRule-OA',232        pwo.XObjectKey,233        'I'234      FROM deleted d235      JOIN TSBAccountDef g236        ON d.UID_TSBAccountDef = g.UID_TSBAccountDef237      JOIN AccProduct a238        ON d.UID_AccProduct = a.UID_AccProduct239      JOIN PersonWantsOrg pwo240        ON g.XObjectKey = pwo.ObjectKeyElementUsedInAssign241      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_OA2,242        @GenprocID243    END244  END TRY245  BEGIN CATCH246    EXEC QBM_PSessionErrorAdd DEFAULT247    RAISERROR('',248    18,249    1)250      WITH NOWAIT251  END CATCH252  ende:253  RETURN254END
Open raw exported source
SQL ยท Raw46 lines
1 create   trigger TSB_TUTSBAccountDef on TSBAccountDef  for Update not for Replication as begin  declare @ObjectkeyOrdered varchar(138) declare 2@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 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('') declare10 @TSBAccountDefsToCheck QBM_YSingleGUID    if update(isForITShop) or update(isITShopOnly) begin declare @FlagTest QBM_YParameterlist insert into @FlagTest11(Parameter1, Parameter2, HasContentFull, e.Parameter3) select i.UID_TSBAccountDef, i.XObjectKey, i.IsForITShop, dbo.QBM_FCVBigIntToString(i.IsITShopOnly12) from inserted i exec QER_PIsForITShopFlagCheck 'TSBAccountDef', @FlagTest end  if update(PTDInheritAccountDef) or update(PFDInheritAccountDef) or update13(PMDInheritAccountDef) or update(PSIInheritAccountDef) or update(UID_TSBAccountDefPredecessor) begin insert into @TSBAccountDefsToCheck(UID_SingleGuid 14) select r.uid_TSBAccountDef from TSBAccountDef r join deleted d on r.uid_TSBAccountDef = d.uid_TSBAccountDef where (isnull(r.PTDInheritAccountDef,0) <>15 isnull(d.PTDInheritAccountDef,0) or isnull(r.PFDInheritAccountDef,0) <> isnull(d.PFDInheritAccountDef,0) or isnull(r.PMDInheritAccountDef,0) <> isnull16(d.PMDInheritAccountDef,0) or isnull(r.PSIInheritAccountDef,0) <> isnull(d.PSIInheritAccountDef,0) or isnull(r.UID_TSBAccountDefPredecessor,'') <> isnull17(d.UID_TSBAccountDefPredecessor,'') ) union select r.UID_TSBAccountDefPredecessor from TSBAccountDef r join deleted d on r.uid_TSBAccountDef = d.uid_TSBAccountDef18 where isnull(r.UID_TSBAccountDefPredecessor,'') <> isnull(d.UID_TSBAccountDefPredecessor,'') and r.UID_TSBAccountDefPredecessor > ' ' union select d.UID_TSBAccountDefPredecessor19 from TSBAccountDef r join deleted d on r.uid_TSBAccountDef = d.uid_TSBAccountDef where isnull(r.UID_TSBAccountDefPredecessor,'') <> isnull(d.UID_TSBAccountDefPredecessor20,'') and d.UID_TSBAccountDefPredecessor > ' ' if 0 < (select count(*) from @TSBAccountDefsToCheck) begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert21 into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from (  select distinct a.UID_TSBAccountDef as uid from TSBAccountDef22 a join @TSBAccountDefsToCheck c on a.UID_TSBAccountDefPredecessor = c.UID_SingleGuid  ) as x exec QBM_PDBQueueInsert_Bulk 'TSB-K-TSBAccountDefFlags', 23@DBQueueElements_01  declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, @GenProcID24 from ( select distinct a.UID_Person as uid from PersonHasTSBAccountDef a join @TSBAccountDefsToCheck c on a.UID_TSBAccountDef = c.UID_SingleGuid  ) as25 x exec QBM_PDBQueueInsert_Bulk 'TSB-K-PersonHasTSBAccountDef', @DBQueueElements_02 declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_0326 (object, subobject, genprocid) select x.uid, null, @GenProcID from (  select distinct a.UID_Org as uid from BaseTreeHasTSBAccountDef a join @TSBAccountDefsToCheck27 c on a.UID_TSBAccountDef = c.UID_SingleGuid  and a.XOrigin > 0 ) as x exec QBM_PDBQueueInsert_Bulk 'TSB-K-OrgHasTSBAccountDef', @DBQueueElements_03 end28 end    if update(uid_accproduct) begin insert into @ElementBuffer(ObjectKey1, UID1) select x.XObjectKey, isnull(x.uid_accproduct,'') from TSBAccountDef29 x join deleted d on x.uid_TSBAccountDef = d.uid_TSBAccountDef and isnull(x.uid_accproduct,'') <> isnull(d.uid_accproduct,'')  and d.uid_accproduct > ' '30  join dialogColumn c on c.UID_DialogTable = 'QER-T-ITShopOrg' and c.columnname = 'uid_ACCProduct' and c.IsDeactivatedByPreProcessor = 0  select @ElementCount31 = @@rowcount select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @ObjectkeyOrdered = bu.ObjectKey1 , @UID_AccProduct = bu.UID132 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @whereclauseOrg = @whereclauseMuster select @whereclauseOrg = replace(@whereclauseOrg33, N'@ObjectkeyOrdered' , @ObjectkeyOrdered) select @whereclauseOrg = replace(@whereclauseOrg, N'@uid_accproduct' , rtrim(@uid_accproduct))    exec QBM_PJobCreate_HOUpdate_B34 'ITShopOrg', @whereclauseOrg, @GenProcID , @p1 = 'uid_ACCProduct', @v1 = @uid_accproduct , @AdditionalObjectKeysAffected = DEFAULT select @ElementIndex35 += 1 end  end  if update (UID_AccProduct) begin declare @RecalcParameter_OA QBM_YCursorBuffer insert into @RecalcParameter_OA (UID1, ObjectKey1, Ident136) select 'QER-PWODecisionRule-OA', a.XObjectKey, 'I' from inserted i join TSBAccountDef g on i.UID_TSBAccountDef = g.UID_TSBAccountDef join QERAccProductUsage37 u on g.XObjectKey = u.XObjectKey join AccProduct a on u.UID_AccProduct = a.UID_AccProduct union select 'QER-PWODecisionRule-OA', a.XObjectKey, 'I' from38 deleted d join TSBAccountDef g on d.UID_TSBAccountDef = g.UID_TSBAccountDef join QERAccProductUsage u on g.XObjectKey = u.XObjectKey join AccProduct a39 on u.UID_AccProduct = a.UID_AccProduct exec QER_PITShopHelperFill_Recalc @RecalcParameter_OA, @GenprocID declare @RecalcParameter_OA2 QBM_YCursorBuffer40 insert into @RecalcParameter_OA2 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-OA', pwo.XObjectKey, 'I' from inserted i join TSBAccountDef g 41on i.UID_TSBAccountDef = g.UID_TSBAccountDef join AccProduct a on i.UID_AccProduct = a.UID_AccProduct join PersonWantsOrg pwo on g.XObjectKey = pwo.ObjectKeyElementUsedInAssign42 union select 'QER-PWODecisionRule-OA', pwo.XObjectKey, 'I' from deleted d join TSBAccountDef g on d.UID_TSBAccountDef = g.UID_TSBAccountDef join AccProduct43 a on d.UID_AccProduct = a.UID_AccProduct join PersonWantsOrg pwo on g.XObjectKey = pwo.ObjectKeyElementUsedInAssign exec QER_PITShopHelperFill_Recalc 44@RecalcParameter_OA2, @GenprocID end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end45 46