dbo.QER_TIBaseTree

1CREATE trigger QER_TIBaseTree2  ON BaseTree FOR3INSERT NOT FOR Replication4AS5BEGIN6  BEGIN TRY7    IF EXISTS(8      SELECT TOP 1 19      FROM inserted)10    GOTO start11    RETURN start:12    DECLARE @GenProcID varchar(38)13    SELECT @GenProcID = dbo.QBM_FGISessionContext('')14    IF EXISTS(15      SELECT TOP 1 116      FROM inserted i17      JOIN DialogGroup g18        ON i.UID_DialogGroup = g.UID_DialogGroup19      WHERE20        g.IsRoleBasedOnly = 0)21    BEGIN22      RAISERROR('#LDS#Assigment is only permitted for DialogGroup.IsRoleBasedOnly true.|',23      18,24      2)25        WITH nowait26    END27    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw28    INSERT INTO @DBQueueElements_01(object,29    subobject,30    genprocid)31    SELECT32      x.uid,33      NULL,34      @GenProcID35    FROM(36    SELECT i.uid_org AS uid37    FROM inserted i) AS x38    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-OrgTreeLEVEL',39      @DBQueueElements_0140    DECLARE @DBQueueElements_02 QBM_YDBQueueRaw41    INSERT INTO @DBQueueElements_02(object,42    subobject,43    genprocid)44    SELECT45      x.uid,46      NULL,47      @GenProcID48    FROM(49    SELECT i.uid_org AS uid50    FROM inserted i) AS x51    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-OrgInherite',52      @DBQueueElements_0253    INSERT INTO BaseTreeCollectionF(UID_Org,54    UID_ParentOrg)55    SELECT56      x.UID_Org,57      x.UID_ParentOrg58    FROM(59    SELECT60      i.UID_Org,61      i.UID_Org AS UID_ParentOrg62    FROM inserted i63    UNION64    SELECT65      i.UID_Org,66      e.UID_ParentOrg67    FROM inserted i68    JOIN BaseTreeCollectionF e69      ON i.UID_ParentOrg = e.UID_Org) AS x70    WHERE71      NOT EXISTS(72    SELECT TOP 1 173    FROM BaseTreeCollectionF f74    WHERE75      f.UID_Org = x.UID_Org AND f.UID_ParentOrg = x.UID_ParentOrg)76    DECLARE @DBQueueElements_03 QBM_YDBQueueRaw77    INSERT INTO @DBQueueElements_03(object,78    subobject,79    genprocid)80    SELECT81      x.uid,82      NULL,83      @GenProcID84    FROM(85    SELECT i.uid_org AS uid86    FROM inserted i) AS x87    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-BaseTreeCollectionF',88      @DBQueueElements_0389    DECLARE @DBQueueElements_05 QBM_YDBQueueRaw90    INSERT INTO @DBQueueElements_05(object,91    subobject,92    genprocid)93    SELECT94      x.uid,95      NULL,96      @GenProcID97    FROM(98    SELECT i.uid_org AS uid99    FROM inserted i100    WHERE101      i.uid_orgroot = 'QER-V-ITShopOrg' AND i.itshopinfo IN('SC', 'SH', 'BO', 'PR')) AS x102    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRack-All',103      @DBQueueElements_05104    IF EXISTS(105      SELECT TOP 1 1106      FROM inserted i107      WHERE108        i.ITShopInfo > ' ' AND i.UID_OrgRoot NOT IN('QER-V-ITShopOrg', 'QER-V-ITShopSrc'))109    BEGIN110      RAISERROR('#LDS#Cannot set IT Shop information, because role class is not suitable for IT Shop.|',111      18,112      2)113        WITH nowait114    END115    IF EXISTS(116      SELECT TOP 1 1117      FROM inserted i118    LEFT119    OUTER120      JOIN BaseTree p121        ON i.UID_ParentOrg = p.UID_Org AND i.UID_OrgRoot = p.UID_OrgRoot122      WHERE123        i.UID_ParentOrg > ' ' AND p.UID_Org IS NULL)124    BEGIN125      RAISERROR('#LDS#Cannot insert node, because predecessor does not exist in the same role class.|',126      18,127      2)128        WITH nowait129    END130    INSERT INTO BaseTreecollection(uid_org,131    uid_parentorg)132    SELECT133      x.uid_org,134      x.uid_parentorg135    FROM(136    SELECT137      i.UID_Org AS UID_Org,138      i.UID_Org AS UID_ParentOrg139    FROM inserted i140    JOIN OrgRoot r141      ON i.UID_OrgRoot = r.UID_OrgRoot142    UNION143    SELECT144      i.UID_Org,145      c.UID_ParentOrg146    FROM BaseTreeCollection c147    JOIN inserted i148      ON c.UID_Org = i.UID_ParentOrg149    JOIN OrgRoot r150      ON i.UID_OrgRoot = r.UID_OrgRoot151    WHERE152      i.IsCutNode = 0 AND r.IsTopDown = 1 AND i.UID_ParentOrg > ' ') AS x153    WHERE154      NOT EXISTS(155    SELECT TOP 1 1156    FROM BaseTreecollection bc157    WHERE158      bc.uid_org = x.uid_org AND bc.uid_parentorg = x.uid_parentorg)159    DECLARE @DBQueueElements_06 QBM_YDBQueueRaw160    INSERT INTO @DBQueueElements_06(Object,161    SubObject,162    GenProcID)163    SELECT164      x.uid,165      NULL,166      @GenProcID167    FROM(168    SELECT i.UID_Org AS uid169    FROM inserted i) AS x170    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-AllForOneOrg',171      @DBQueueElements_06172    DECLARE @DBQueueElements_07 QBM_YDBQueueRaw173    INSERT INTO @DBQueueElements_07(object,174    subobject,175    genprocid)176    SELECT177      x.uid,178      NULL,179      @GenProcID180    FROM(181    SELECT uid_personHead AS uid182    FROM inserted183    WHERE184      uid_personHead > ' '185    UNION186    SELECT uid_personHeadSecond AS uid187    FROM Inserted188    WHERE189      uid_personHeadSecond > ' '190    UNION191    SELECT us.UID_PersonReceiver AS uid192    FROM inserted i193    JOIN QERUniversalSubstitute us194      ON us.UID_PersonSender IN(i.UID_PersonHead, i.UID_PersonHeadSecond)195    WHERE196      us.IsCurrentlyActive = 1 AND us.UseForHeadOrg = 1197    UNION198    SELECT pio.UID_Person199    FROM inserted i200    JOIN PersonInBaseTree pio201      ON i.UID_AERoleManager = pio.UID_Org202    UNION203    SELECT us.UID_PersonReceiver AS uid204    FROM inserted i205    JOIN PersonInBaseTree pio206      ON i.UID_AERoleManager = pio.UID_Org207    JOIN QERUniversalSubstitute us208      ON us.UID_PersonSender = pio.UID_Person209    WHERE210      us.IsCurrentlyActive = 1 AND us.UseForHeadOrg = 1) AS x211    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadOrg',212      @DBQueueElements_07213  END TRY214  BEGIN CATCH215    EXEC QBM_PSessionErrorAdd DEFAULT216    RAISERROR('',217    18,218    1)219      WITH NOWAIT220  END CATCH221  ende:222  RETURN223END