dbo.QER_TDBaseTree

1CREATE trigger QER_TDBaseTree2  ON BaseTree FOR3DELETE NOT FOR Replication4AS5BEGIN6  BEGIN TRY7    IF EXISTS(8      SELECT TOP 1 19      FROM deleted)10    GOTO start11    RETURN start:12    DECLARE @GenProcID varchar(38)13    SELECT @GenProcID = dbo.QBM_FGISessionContext('')14    DECLARE @alt_uid_orgroot varchar(38)15    DECLARE @alt_uid_org varchar(38)16    DECLARE @uid_personwantsorg varchar(38)17    DECLARE @datum datetime18    DECLARE @SQLcmd nvarchar(max)19    DECLARE @alt_uid_ParentOrg varchar(38)20    DECLARE @alt_IsTopDown BIT21    DECLARE @uid_subOrg varchar(38)22    DECLARE @betroffenePWO QBM_YParameterList23    IF EXISTS(24      SELECT TOP 1 125      FROM QERAssign a26      JOIN deleted d27        ON a.ObjectKeyAssignTarget = d.XObjectKey)28    BEGIN29      RAISERROR('#LDS#Cannot delete {1}, because {2} does exist. Rule {0}|DynamicFK|BaseTree|QERAssign|',30      18,31      2)32        WITH NOWAIT33    END34    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw35    INSERT INTO @DBQueueElements_01(Object,36    SubObject,37    GenProcID)38    SELECT39      x.uid,40      NULL,41      @GenProcID42    FROM(43    SELECT44      DISTINCT pwo.UID_PersonWantsOrg AS uid45    FROM PersonWantsOrg pwo46    JOIN deleted d47      ON pwo.UID_Org = d.UID_Org48    WHERE49      pwo.OrderState IN('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe',50    'Waiting')51    UNION52    SELECT53      DISTINCT pwo.UID_PersonWantsOrg AS uid54    FROM PersonWantsOrg pwo55    JOIN deleted d56      ON pwo.UID_OrgParent = d.UID_Org57    WHERE58      pwo.OrderState IN('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe',59    'Waiting')60    UNION61    SELECT62      DISTINCT pwo.UID_PersonWantsOrg AS uid63    FROM PersonWantsOrg pwo64    JOIN deleted d65      ON pwo.UID_OrgParentOfParent = d.UID_Org66    WHERE67      pwo.OrderState IN('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe',68    'Waiting')69    UNION70    SELECT71      DISTINCT pwo.UID_PersonWantsOrg AS uid72    FROM PersonWantsOrg pwo73    JOIN deleted d74      ON pwo.UID_ITShopOrgFinal = d.UID_Org75    WHERE76      pwo.OrderState IN('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe',77    'Waiting')) AS x78    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ITSHOPOrder-ABORT',79      @DBQueueElements_0180    DECLARE @DBQueueElements_02 QBM_YDBQueueRaw81    INSERT INTO @DBQueueElements_02(object,82    subobject,83    genprocid)84    SELECT85      x.uid,86      NULL,87      @GenProcID88    FROM(89    SELECT d.uid_org AS uid90    FROM deleted d) AS x91    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-OrgInherite',92      @DBQueueElements_0293    DECLARE @DBQueueElements_03 QBM_YDBQueueRaw94    INSERT INTO @DBQueueElements_03(object,95    subobject,96    genprocid)97    SELECT98      x.uid,99      NULL,100      @GenProcID101    FROM(102    SELECT d.uid_org AS uid103    FROM deleted d104    WHERE105      d.uid_OrgRoot = 'QER-V-ITShopOrg' AND d.itshopinfo IN('SC', 'SH', 'BO', 'PR')) AS x106    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRack-All',107      @DBQueueElements_03108    DECLARE @DBQueueElements_04 QBM_YDBQueueRaw109    INSERT INTO @DBQueueElements_04(object,110    subobject,111    genprocid)112    SELECT113      x.uid,114      NULL,115      @GenProcID116    FROM(117    SELECT d.uid_parentorg AS uid118    FROM deleted d119    WHERE120      d.uid_parentorg > ' '121    UNION122    SELECT o.uid_org AS uid123    FROM deleted d124    JOIN BaseTree o125      WITH(nolock)126      ON o.uid_parentorg = d.uid_org) AS x127    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-OrgTreeLEVEL',128      @DBQueueElements_04129    DECLARE @DBQueueElements_05 QBM_YDBQueueRaw130    INSERT INTO @DBQueueElements_05(object,131    subobject,132    genprocid)133    SELECT134      x.uid,135      NULL,136      @GenProcID137    FROM(138    SELECT uid_personHead AS uid139    FROM deleted140    WHERE141      uid_personHead > ' '142    UNION143    SELECT uid_personHeadSecond AS uid144    FROM deleted145    WHERE146      uid_personHeadSecond > ' '147    UNION148    SELECT pio.UID_Person149    FROM deleted d150    JOIN PersonInBaseTree pio151      ON d.UID_AERoleManager = pio.UID_Org152    UNION153    SELECT us.UID_PersonReceiver AS uid154    FROM deleted d155    JOIN QERUniversalSubstitute us156      ON us.UID_PersonSender IN(d.UID_PersonHead, d.UID_PersonHeadSecond)157    WHERE158      (d.UID_PersonHeadSecond > ' ' OR d.UID_PersonHead > ' ') AND us.IsCurrentlyActive = 1 AND us.UseForHeadOrg = 1159    UNION160    SELECT us.UID_PersonReceiver AS uid161    FROM deleted d162    JOIN PersonInBaseTree pio163      ON d.UID_AERoleManager = pio.UID_Org164    JOIN QERUniversalSubstitute us165      ON us.UID_PersonSender = pio.UID_Person166    WHERE167      us.IsCurrentlyActive = 1 AND us.UseForHeadOrg = 1) AS x168    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadOrg',169      @DBQueueElements_05170    DECLARE @RecalcParameter_01 QBM_YCursorBuffer171    INSERT INTO @RecalcParameter_01(UID1,172    ObjectKey1,173    Ident1)174    SELECT175      y.DecisionRule,176      x.XObjectKey,177      'I'178    FROM(179    SELECT b.XObjectKey180    FROM deleted d181    JOIN BaseTree b182      ON d.UID_Org = b.UID_OrgDepartment183    WHERE184      b.UID_OrgRoot = 'QER-V-ITShopOrg' AND d.UID_OrgRoot = 'QER-V-Department') AS x185    JOIN(186    SELECT 'QER-PWODecisionRule-D0' AS DecisionRule187    UNION188    SELECT 'QER-PWODecisionRule-D1'189    UNION190    SELECT 'QER-PWODecisionRule-D2') AS y191      ON 1 = 1192    EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_01,193      @GenprocID194  END TRY195  BEGIN CATCH196    EXEC QBM_PSessionErrorAdd DEFAULT197    RAISERROR('',198    18,199    1)200      WITH NOWAIT201  END CATCH202  ende:203  RETURN204END