dbo.QER_TIBaseTreeExcludesBaseTree

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on BaseTreeExcludesBasetree. Bulk DBQueue insert -> QER-K-DynamicGroupTest / QER_ZDynamicGroupTest at line 20; References QBM_PDBQueueInsert_Bulk; Trigger parent table: BaseTreeExcludesBasetree

Source: sandbox-db sys.sql_modules

Source size: 3.376 characters

Interpretation

Database trigger. Treat parent table and enqueue/object-layer calls as the main relation points.
DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

Bulk DBQueue insert -> QER-K-DynamicGroupTest / QER_ZDynamicGroupTest at line 20
References QBM_PDBQueueInsert_Bulk
Trigger parent table: BaseTreeExcludesBasetree

Typed Edges

queues DBQueue task QER_ZDynamicGroupTest at line 20 Bulk DBQueue insert -> QER-K-DynamicGroupTest / QER_ZDynamicGroupTest at line 20
trigger on table BaseTreeExcludesBasetree Trigger parent table: BaseTreeExcludesBasetree
references source dbo.QBM_FGISessionContext source text reference
references source dbo.QBM_PDBQueueInsert_Bulk source text reference
references source dbo.QBM_PSessionErrorAdd source text reference
references source dbo.QER_TIBaseTree source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE trigger QER_TIBaseTreeExcludesBaseTree2  ON BaseTreeExcludesBaseTree FOR3INSERT NOT FOR Replication4AS5BEGIN6  DECLARE @GenProcID varchar(38) = dbo.QBM_FGISessionContext('')7  BEGIN TRY8    IF EXISTS(9      SELECT TOP 1 110      FROM inserted)11    GOTO start12    RETURN start:13    IF EXISTS(14      SELECT TOP 1 115      FROM inserted i16      JOIN personinBaseTree pio117        ON i.uid_org = pio1.uid_org AND pio1.XOrigin > 018      JOIN personinBaseTree pio219        ON pio1.uid_person = pio2.uid_person AND pio2.uid_org = i.uid_orgExcluded AND pio2.XOrigin > 020      JOIN BaseTree b121        ON pio1.UID_Org = b1.UID_Org22      JOIN BaseTree b223        ON pio2.UID_Org = b2.UID_Org24    LEFT25    OUTER26      JOIN DynamicGroup d127        ON b1.XObjectKey = d1.ObjectKeyBaseTree28    LEFT29    OUTER30      JOIN DynamicGroup d231        ON b2.XObjectKey = d2.ObjectKeyBaseTree32      WHERE33        d1.UID_DynamicGroup IS NULL AND d2.UID_DynamicGroup IS NULL)34    BEGIN35      RAISERROR('#LDS#Cannot make a definition because there are already identity assignments to both roles.|',36      18,37      3)38        WITH nowait39    END40    IF EXISTS(41      SELECT TOP 1 142      FROM inserted i43      JOIN WorkDeskinBaseTree pio144        ON i.uid_org = pio1.uid_org45      JOIN WorkDeskinBaseTree pio246        ON pio1.uid_WorkDesk = pio2.uid_WorkDesk AND pio2.uid_org = i.uid_orgExcluded47      JOIN BaseTree b148        ON pio1.UID_Org = b1.UID_Org49      JOIN BaseTree b250        ON pio2.UID_Org = b2.UID_Org51    LEFT52    OUTER53      JOIN DynamicGroup d154        ON b1.XObjectKey = d1.ObjectKeyBaseTree55    LEFT56    OUTER57      JOIN DynamicGroup d258        ON b2.XObjectKey = d2.ObjectKeyBaseTree59      WHERE60        d1.UID_DynamicGroup IS NULL AND d2.UID_DynamicGroup IS NULL)61    BEGIN62      RAISERROR('#LDS#Cannot make a definition because there are already WorkDesk assignments to both roles.|',63      18,64      3)65        WITH nowait66    END67    IF EXISTS(68      SELECT TOP 1 169      FROM inserted i70      JOIN HardwareinBaseTree pio171        ON i.uid_org = pio1.uid_org AND pio1.XOrigin > 072      JOIN HardwareinBaseTree pio273        ON pio1.uid_Hardware = pio2.uid_Hardware AND pio2.XOrigin > 0 AND pio2.uid_org = i.uid_orgExcluded74      JOIN BaseTree b175        ON pio1.UID_Org = b1.UID_Org76      JOIN BaseTree b277        ON pio2.UID_Org = b2.UID_Org78    LEFT79    OUTER80      JOIN DynamicGroup d181        ON b1.XObjectKey = d1.ObjectKeyBaseTree82    LEFT83    OUTER84      JOIN DynamicGroup d285        ON b2.XObjectKey = d2.ObjectKeyBaseTree86      WHERE87        d1.UID_DynamicGroup IS NULL AND d2.UID_DynamicGroup IS NULL)88    BEGIN89      RAISERROR('#LDS#Cannot make a definition because there are already device assignments to both roles.|',90      18,91      3)92        WITH nowait93    END94    IF EXISTS(95      SELECT TOP 1 196      FROM inserted i97      JOIN BaseTree b198        ON i.uid_org = b1.UID_Org99      JOIN BaseTree b2100        ON i.uid_orgExcluded = b2.UID_Org101      JOIN DynamicGroup d1102        ON b1.XObjectKey = d1.ObjectKeyBaseTree103      JOIN DynamicGroup d2104        ON b2.XObjectKey = d2.ObjectKeyBaseTree)105    BEGIN106      RAISERROR('#LDS#Cannot make a definition because there are already dynamic role assignments to both roles.|',107      18,108      3)109        WITH nowait110    END111    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw112    INSERT INTO @DBQueueElements_01(object,113    subobject,114    genprocid)115    SELECT116      x.uid,117      NULL,118      @GenProcID119    FROM(120    SELECT g.UID_DynamicGroup AS uid121    FROM inserted i122    JOIN BaseTree b123      ON i.UID_Org = b.UID_Org124    JOIN DynamicGroup g125      ON b.XObjectKey = g.ObjectKeyBaseTree126    UNION127    SELECT g.UID_DynamicGroup AS uid128    FROM inserted i129    JOIN BaseTree b130      ON i.UID_OrgExcluded = b.UID_Org131    JOIN DynamicGroup g132      ON b.XObjectKey = g.ObjectKeyBaseTree) AS x133    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-DynamicGroupTest',134      @DBQueueElements_01135  END TRY136  BEGIN CATCH137    EXEC QBM_PSessionErrorAdd DEFAULT138    RAISERROR('',139    18,140    1)141      WITH NOWAIT142  END CATCH143  ende:144  RETURN145END

Open raw exported source

1  create   trigger QER_TIBaseTreeExcludesBaseTree on BaseTreeExcludesBaseTree  for Insert not for Replication as begin declare @GenProcID varchar2(38) = dbo.QBM_FGISessionContext('') BEGIN TRY if exists (select top 1 1 from inserted) goto start return start: if exists (select top 1 1 from inserted3 i join personinBaseTree pio1 on i.uid_org = pio1.uid_org and pio1.XOrigin > 0 join personinBaseTree pio2 on pio1.uid_person = pio2.uid_person and pio2.uid_org4 = i.uid_orgExcluded and pio2.XOrigin > 0  join BaseTree b1 on pio1.UID_Org = b1.UID_Org join BaseTree b2 on pio2.UID_Org = b2.UID_Org left outer join 5DynamicGroup d1 on b1.XObjectKey = d1.ObjectKeyBaseTree left outer join DynamicGroup d2 on b2.XObjectKey = d2.ObjectKeyBaseTree where d1.UID_DynamicGroup6 is null and d2.UID_DynamicGroup is null )  begin  raiserror( '#LDS#Cannot make a definition because there are already identity assignments to both roles.|'7, 18, 3) with nowait end if exists (select top 1 1 from inserted i join WorkDeskinBaseTree pio1 on i.uid_org = pio1.uid_org  join WorkDeskinBaseTree pio28 on pio1.uid_WorkDesk = pio2.uid_WorkDesk and pio2.uid_org = i.uid_orgExcluded   join BaseTree b1 on pio1.UID_Org = b1.UID_Org join BaseTree b2 on pio2.UID_Org9 = b2.UID_Org left outer join DynamicGroup d1 on b1.XObjectKey = d1.ObjectKeyBaseTree left outer join DynamicGroup d2 on b2.XObjectKey = d2.ObjectKeyBaseTree10 where d1.UID_DynamicGroup is null and d2.UID_DynamicGroup is null )  begin  raiserror( '#LDS#Cannot make a definition because there are already WorkDesk assignments to both roles.|'11, 18, 3) with nowait end if exists (select top 1 1 from inserted i join HardwareinBaseTree pio1 on i.uid_org = pio1.uid_org and pio1.XOrigin > 0 join HardwareinBaseTree12 pio2 on pio1.uid_Hardware = pio2.uid_Hardware and pio2.XOrigin > 0 and pio2.uid_org = i.uid_orgExcluded  join BaseTree b1 on pio1.UID_Org = b1.UID_Org13 join BaseTree b2 on pio2.UID_Org = b2.UID_Org left outer join DynamicGroup d1 on b1.XObjectKey = d1.ObjectKeyBaseTree left outer join DynamicGroup d2 14on b2.XObjectKey = d2.ObjectKeyBaseTree where d1.UID_DynamicGroup is null and d2.UID_DynamicGroup is null )  begin  raiserror( '#LDS#Cannot make a definition because there are already device assignments to both roles.|'15, 18, 3) with nowait end if exists (select top 1 1 from inserted i join BaseTree b1 on i.uid_org = b1.UID_Org join BaseTree b2 on i.uid_orgExcluded = b2.UID_Org16 join DynamicGroup d1 on b1.XObjectKey = d1.ObjectKeyBaseTree join DynamicGroup d2 on b2.XObjectKey = d2.ObjectKeyBaseTree ) begin raiserror( '#LDS#Cannot make a definition because there are already dynamic role assignments to both roles.|'17, 18, 3) with nowait end  declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null18, @GenProcID from ( select g.UID_DynamicGroup as uid from inserted i join BaseTree b on i.UID_Org = b.UID_Org join DynamicGroup g on b.XObjectKey = g.ObjectKeyBaseTree19 union select g.UID_DynamicGroup as uid from inserted i join BaseTree b on i.UID_OrgExcluded = b.UID_Org join DynamicGroup g on b.XObjectKey = g.ObjectKeyBaseTree20 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-DynamicGroupTest', @DBQueueElements_01 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('',21 18, 1) WITH NOWAIT END CATCH ende: return end 22