Back to OIM Explorer

dbo.QER_TUHelperHeadOrg

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on HelperHeadOrg. HOCallMethod -> personwantsorg.Abort at line 29; HOCallMethod -> personwantsorg.Abort at line 36; Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 29; References QBM_PJobCreate*

Source: sandbox-db sys.sql_modules

Source size: 6.457 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

  • HOCallMethod -> personwantsorg.Abort at line 29
  • HOCallMethod -> personwantsorg.Abort at line 36
  • Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 29
  • References QBM_PJobCreate*
  • References QBM_PJobCreate_HOCallMethod*
  • References QBM_PDBQueueInsert_Single
  • Trigger parent table: HelperHeadOrg

Typed Edges

  • calls object method personwantsorg.Abort HOCallMethod -> personwantsorg.Abort at line 29
  • queues DBQueue task QBM_ZRecalculate at line 29 Single DBQueue insert -> QBM-K-CommonReCalculate / QBM_ZRecalculate at line 29
  • trigger on table HelperHeadOrg Trigger parent table: HelperHeadOrg
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGIXOriginChanged_Effect source text reference
  • references source dbo.QER_FGIGenProcIDForPWO source text reference
  • references source dbo.QER_FGIPWOStateFinalError source text reference
  • references source dbo.QBM_PDBQueueInsert_Single source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOCallMethod source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd 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

SQL275 lines
1CREATE trigger QER_TUHelperHeadOrg2  ON HelperHeadOrg FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @GenProcID varchar(38)7  DECLARE @GenProcIDToUse varchar(38)8  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))9  DECLARE @SQLcmd nvarchar(1000)10  DECLARE @uid_personwantsorg varchar(38)11  DECLARE @BasisObjectKey varchar(138)12  DECLARE @UID_PWOStateFinalError varchar(38)13  DECLARE @Parameter nvarchar(1000)14  DECLARE @DebugSwitch int = 015  DECLARE @ElementBuffer QBM_YCursorBuffer16  DECLARE @ElementCount int17  DECLARE @ElementIndex int18  DECLARE @DebugLevel char(1) = 'W'19  DECLARE @ChangedPersonHead QBM_YSingleGUID20  BEGIN TRY21    IF EXISTS(22      SELECT TOP 1 123      FROM deleted)24    GOTO start25    IF EXISTS(26      SELECT TOP 1 127      FROM inserted)28    GOTO start29    RETURN start:30    SELECT @GenProcID = dbo.QBM_FGISessionContext('')31    IF @DebugSwitch > 032    BEGIN33      EXEC QBM_PJournal 'Update trigger HelperheadOrg',34        @@procid,35      'D',36        @DebugLevel37    END38    IF39    UPDATE(XOrigin)40    BEGIN41      IF @DebugSwitch > 042      BEGIN43        EXEC QBM_PJournal 'In der XOrigin-Klausel',44          @@procid,45        'D',46          @DebugLevel47      END48      INSERT INTO @ChangedPersonHead(UID_SingleGuid)49      SELECT50        DISTINCT d.UID_PersonHead AS uid51      FROM HelperHeadOrg a52      JOIN deleted d53        ON a.XObjectKey = d.XObjectKey54      WHERE55        dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin,56      a.XOrigin,57      1,58      1) = 159      IF @@ROWCOUNT > 060      BEGIN61        IF @DebugSwitch > 062        BEGIN63          EXEC QBM_PJournal 'In der Exists-Klausel',64            @@procid,65          'D',66            @DebugLevel67        END68        DECLARE @RecalcParameter_01 QBM_YCursorBuffer69        INSERT INTO @RecalcParameter_01(UID1,70        ObjectKey1,71        Ident1)72        SELECT73          y.DecisionRule,74          x.XObjectKey,75          'I'76        FROM(77        SELECT e.XObjectKey78        FROM HelperHeadOrg a79        JOIN deleted d80          ON a.XObjectKey = d.XObjectKey81        JOIN Basetree e82          ON d.UID_Org = e.UID_Org AND e.UID_OrgRoot = 'QER-V-Department'83        WHERE84          dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) = 1) AS x85        JOIN(86        SELECT 'QER-PWODecisionRule-D0' AS DecisionRule87        UNION88        SELECT 'QER-PWODecisionRule-D1'89        UNION90        SELECT 'QER-PWODecisionRule-D2'91        UNION92        SELECT 'QER-PWODecisionRule-DM'93        UNION94        SELECT 'QER-PWODecisionRule-DP') AS y95          ON 1 = 196        EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_01,97          @GenprocID98        DECLARE @RecalcParameter_02 QBM_YCursorBuffer99        INSERT INTO @RecalcParameter_02(UID1,100        ObjectKey1,101        Ident1)102        SELECT103          y.DecisionRule,104          x.XObjectKey,105          'I'106        FROM(107        SELECT e.XObjectKey108        FROM HelperHeadOrg a109        JOIN deleted d110          ON a.XObjectKey = d.XObjectKey111        JOIN Basetree e112          ON d.UID_Org = e.UID_Org AND e.UID_OrgRoot = 'QER-V-ITShopOrg'113        WHERE114          dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) = 1) AS x115        JOIN(116        SELECT 'QER-PWODecisionRule-H0' AS DecisionRule117        UNION118        SELECT 'QER-PWODecisionRule-H1'119        UNION120        SELECT 'QER-PWODecisionRule-H2') AS y121          ON 1 = 1122        EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_02,123          @GenprocID124        DECLARE @RecalcParameter_03 QBM_YCursorBuffer125        INSERT INTO @RecalcParameter_03(UID1,126        ObjectKey1,127        Ident1)128        SELECT129          y.DecisionRule,130          x.XObjectKey,131          'I'132        FROM(133        SELECT a.XObjectKey134        FROM HelperHeadOrg a135        JOIN deleted d136          ON a.XObjectKey = d.XObjectKey137        WHERE138          dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) = 1) AS x139        JOIN(140        SELECT 'QER-PWODecisionRule-MS' AS DecisionRule) AS y141          ON 1 = 1142        EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_03,143          @GenprocID144        DECLARE @RecalcParameter_04 QBM_YCursorBuffer145        INSERT INTO @RecalcParameter_04(UID1,146        ObjectKey1,147        Ident1)148        SELECT149          y.DecisionRule,150          x.XObjectKey,151          'I'152        FROM(153        SELECT e.XObjectKey154        FROM HelperHeadOrg a155        JOIN deleted d156          ON a.XObjectKey = d.XObjectKey157        JOIN Basetree e158          ON d.UID_Org = e.UID_Org159        WHERE160          dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) = 1) AS x161        JOIN(162        SELECT 'QER-PWODecisionRule-OM' AS DecisionRule) AS y163          ON 1 = 1164        EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_04,165          @GenprocID166        DECLARE @RecalcParameter_05 QBM_YCursorBuffer167        INSERT INTO @RecalcParameter_05(UID1,168        ObjectKey1,169        Ident1)170        SELECT171          y.DecisionRule,172          x.XObjectKey,173          'I'174        FROM(175        SELECT e.XObjectKey176        FROM HelperHeadOrg a177        JOIN deleted d178          ON a.XObjectKey = d.XObjectKey179        JOIN Basetree e180          ON d.UID_Org = e.UID_Org AND e.UID_OrgRoot = 'QER-V-ProfitCenter'181        WHERE182          dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) = 1) AS x183        JOIN(184        SELECT 'QER-PWODecisionRule-P0' AS DecisionRule185        UNION186        SELECT 'QER-PWODecisionRule-P1'187        UNION188        SELECT 'QER-PWODecisionRule-P2'189        UNION190        SELECT 'QER-PWODecisionRule-PM'191        UNION192        SELECT 'QER-PWODecisionRule-PP') AS y193          ON 1 = 1194        EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_05,195          @GenprocID196        EXEC QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate',197        'QER-K-ShoppingRackPWODecisionMaker',198        '',199          @GenProcID200      END201      INSERT INTO @ElementBuffer(UID1,202      ObjectKey1,203      UID2)204      SELECT205        pwo.uid_personwantsorg,206        pwo.XObjectKey,207        dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,208        @GenProcID,209        @CfgUseGenProcID) AS GenProcID210      FROM HelperHeadOrg a211      JOIN deleted y212        ON a.XObjectKey = y.XObjectKey213      JOIN Delegation d214        ON d.ObjectKeyDelegated = y.XObjectKey215      JOIN personwantsorg pwo216        ON d.uid_personwantsorg = pwo.uid_personwantsorg217      WHERE218        dbo.QBM_FGIXOriginChanged_Effect(y.XOrigin,219      a.XOrigin,220      1,221      1) = 1 AND pwo.OrderState IN('Assigned',222      'Granted',223      'New',224      'OrderProduct',225      'OrderProlongate',226      'OrderUnsubscribe',227      'Waiting')228      SELECT @ElementCount = @@ROWCOUNT229      SELECT @ElementIndex = 1230      WHILE @ElementIndex <= @ElementCount231      BEGIN232        SELECT233          TOP 1 @uid_personwantsorg = bu.UID1,234          @BasisObjectKey = bu.ObjectKey1,235          @GenProcIDToUse = bu.UID2236        FROM @ElementBuffer bu237        WHERE238          bu.ElementIndex = @ElementIndex239        SELECT @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''''240        SELECT @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg)241        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',242          @whereclause = @SQLcmd,243          @save = 1,244          @MethodName = 'Abort',245          @GenProcID = @GenProcIDToUse,246          @ObjectKeysAffected = DEFAULT,247          @param1 = '#LDS#Closing delegation because its source was deleted.|',248          @param2 = @UID_PWOStateFinalError,249          @Retries = 3,250          @isToFreezeOnError = 1,251          @BasisObjectKey = @BasisObjectKey,252          @checkForExisting = 1,253          @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'254        SELECT @ElementIndex += 1255      END256      DELETE Delegation257      FROM deleted d258      JOIN HelperHeadOrg i259        ON i.XObjectKey = d.XObjectKey260      JOIN Delegation dl261        ON d.XObjectKey = dl.ObjectKeyDelegated262      WHERE263        i.XOrigin = 0 AND d.XOrigin > 0264    END265  END TRY266  BEGIN CATCH267    EXEC QBM_PSessionErrorAdd DEFAULT268    RAISERROR('',269    18,270    1)271      WITH NOWAIT272  END CATCH273  ende:274  RETURN275END
Open raw exported source
SQL ยท Raw42 lines
1create   trigger QER_TUHelperHeadOrg on HelperHeadOrg  for update not for Replication as begin  declare @GenProcID varchar(38) declare @GenProcIDToUse2 varchar(38) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO')) declare @SQLcmd3 nvarchar(1000) declare @uid_personwantsorg varchar(38) declare @BasisObjectKey varchar(138) declare @UID_PWOStateFinalError varchar(38) declare @Parameter4 nvarchar(1000) declare @DebugSwitch int = 0 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DebugLevel5 char(1) = 'W' declare @ChangedPersonHead QBM_YSingleGUID  BEGIN TRY if exists (select top 1 1 from deleted) goto start if exists (select top 1 1 from 6inserted) goto start return start: select @GenProcID = dbo.QBM_FGISessionContext('') if @DebugSwitch > 0 begin exec QBM_PJournal 'Update trigger HelperheadOrg'7, @@procid, 'D', @DebugLevel end if update(XOrigin) begin if @DebugSwitch > 0 begin exec QBM_PJournal 'In der XOrigin-Klausel', @@procid, 'D', @DebugLevel8 end insert into @ChangedPersonHead (UID_SingleGuid) select distinct d.UID_PersonHead as uid from HelperHeadOrg a join deleted d on a.XObjectKey = d.XObjectKey9 where dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) = 1 if @@ROWCOUNT > 0  begin if @DebugSwitch > 0 begin exec QBM_PJournal 'In der Exists-Klausel'10, @@procid, 'D', @DebugLevel end declare @RecalcParameter_01 QBM_YCursorBuffer  insert into @RecalcParameter_01 (UID1, ObjectKey1, Ident1) select y.DecisionRule11, x.XObjectKey, 'I' from (select e.XObjectKey from HelperHeadOrg a join deleted d on a.XObjectKey = d.XObjectKey join Basetree e on d.UID_Org = e.UID_Org12 and e.UID_OrgRoot = 'QER-V-Department' where dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) = 1 ) as x join (select 'QER-PWODecisionRule-D0'13 as DecisionRule union select 'QER-PWODecisionRule-D1' union select 'QER-PWODecisionRule-D2' union select 'QER-PWODecisionRule-DM' union select 'QER-PWODecisionRule-DP'14 ) as y on 1 = 1 exec QER_PITShopHelperFill_Recalc @RecalcParameter_01, @GenprocID declare @RecalcParameter_02 QBM_YCursorBuffer insert into @RecalcParameter_0215 (UID1, ObjectKey1, Ident1) select y.DecisionRule, x.XObjectKey, 'I' from (select e.XObjectKey from HelperHeadOrg a join deleted d on a.XObjectKey = d.XObjectKey16 join Basetree e on d.UID_Org = e.UID_Org and e.UID_OrgRoot = 'QER-V-ITShopOrg' where dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) = 1 17) as x join (select 'QER-PWODecisionRule-H0' as DecisionRule union select 'QER-PWODecisionRule-H1' union select 'QER-PWODecisionRule-H2' ) as y on 1 = 181 exec QER_PITShopHelperFill_Recalc @RecalcParameter_02, @GenprocID declare @RecalcParameter_03 QBM_YCursorBuffer insert into @RecalcParameter_03 (UID119, ObjectKey1, Ident1) select y.DecisionRule, x.XObjectKey, 'I' from (select a.XObjectKey from HelperHeadOrg a join deleted d on a.XObjectKey = d.XObjectKey20 where dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) = 1 ) as x join (select 'QER-PWODecisionRule-MS' as DecisionRule ) as y on 1 = 1 exec21 QER_PITShopHelperFill_Recalc @RecalcParameter_03, @GenprocID declare @RecalcParameter_04 QBM_YCursorBuffer insert into @RecalcParameter_04 (UID1, ObjectKey122, Ident1) select y.DecisionRule, x.XObjectKey, 'I' from (select e.XObjectKey from HelperHeadOrg a join deleted d on a.XObjectKey = d.XObjectKey join Basetree23 e on d.UID_Org = e.UID_Org where dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) = 1 ) as x join (select 'QER-PWODecisionRule-OM' as DecisionRule24 ) as y on 1 = 1 exec QER_PITShopHelperFill_Recalc @RecalcParameter_04, @GenprocID declare @RecalcParameter_05 QBM_YCursorBuffer insert into @RecalcParameter_0525 (UID1, ObjectKey1, Ident1) select y.DecisionRule, x.XObjectKey, 'I' from (select e.XObjectKey from HelperHeadOrg a join deleted d on a.XObjectKey = d.XObjectKey26 join Basetree e on d.UID_Org = e.UID_Org and e.UID_OrgRoot = 'QER-V-ProfitCenter' where dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, 1, 1) =27 1 ) as x join (select 'QER-PWODecisionRule-P0' as DecisionRule union select 'QER-PWODecisionRule-P1' union select 'QER-PWODecisionRule-P2' union select28 'QER-PWODecisionRule-PM' union select 'QER-PWODecisionRule-PP' ) as y on 1 = 1 exec QER_PITShopHelperFill_Recalc @RecalcParameter_05, @GenprocID exec 29QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate', 'QER-K-ShoppingRackPWODecisionMaker', '', @GenProcID  end   insert into @ElementBuffer(UID1, ObjectKey130, UID2) select pwo.uid_personwantsorg, pwo.XObjectKey, dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID) as GenProcID from HelperHeadOrg31 a join deleted y on a.XObjectKey = y.XObjectKey join Delegation d on d.ObjectKeyDelegated = y.XObjectKey join personwantsorg pwo on d.uid_personwantsorg32 = pwo.uid_personwantsorg    where dbo.QBM_FGIXOriginChanged_Effect(y.XOrigin, a.XOrigin, 1, 1) = 1 and pwo.OrderState in ('Assigned', 'Granted', 'New'33, 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount34 begin select top 1 @uid_personwantsorg = bu.UID1 , @BasisObjectKey = bu.ObjectKey1 , @GenProcIDToUse = bu.UID2 from @ElementBuffer bu where bu.ElementIndex35 = @ElementIndex select @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N'''' select @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError36(@UID_PersonWantsOrg) exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'Abort' , @GenProcID37 = @GenProcIDToUse , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Closing delegation because its source was deleted.|' , @param2 = @UID_PWOStateFinalError38 , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'39  select @ElementIndex += 1 end    delete Delegation from deleted d join HelperHeadOrg i on i.XObjectKey = d.XObjectKey join Delegation dl on d.XObjectKey40 = dl.ObjectKeyDelegated where i.XOrigin = 0 and d.XOrigin > 0  end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT41 END CATCH ende: return end 42