dbo.RMS_TUBaseTreeHasESet

1CREATE trigger RMS_TUBaseTreeHasESet2  ON BaseTreeHasESet FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @EsetOperation varchar(38)7  DECLARE @EsetTable varchar(30)8  DECLARE @ElementBuffer QBM_YCursorBuffer9  DECLARE @ElementCount int10  DECLARE @ElementIndex int11  DECLARE @ChangedOrg QBM_YSingleGUID12  DECLARE @DBQueueElements_spezial QBM_YDBQueueRaw13  BEGIN TRY14    IF EXISTS(15      SELECT TOP 1 116      FROM inserted)17    GOTO start18    IF EXISTS(19      SELECT TOP 1 120      FROM deleted)21    GOTO start22    RETURN start:23    DECLARE @GenProcID varchar(38)24    SELECT @GenProcID = dbo.QBM_FGISessionContext('')25    IF26    UPDATE(XOrigin) OR27    UPDATE(XIsInEffect)28    BEGIN29      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw30      INSERT INTO @DBQueueElements_01(object,31      subobject,32      genprocid)33      SELECT34        x.uid,35        NULL,36        @GenProcID37      FROM(38      SELECT39        DISTINCT a.UID_Org AS uid40      FROM BaseTreeHasESet a41      JOIN deleted d42        ON a.XObjectKey = d.XObjectKey43      WHERE44        (dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, a.XOrigin, d.XIsInEffect, a.XIsInEffect) = 1 OR dbo.QER_FCVXOriginToInheritInfo(d.XOrigin) <>45      dbo.QER_FCVXOriginToInheritInfo(a.XOrigin))) AS x46      EXEC QBM_PDBQueueInsert_Bulk 'RMS-K-BaseTreeHasObject',47        @DBQueueElements_0148      INSERT INTO @ChangedOrg(UID_SingleGuid)49      SELECT50        DISTINCT d.UID_Org AS uid51      FROM BaseTreeHasESet a52      JOIN deleted d53        ON a.XObjectKey = d.XObjectKey54      WHERE55        dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin,56      a.XOrigin,57      d.XIsInEffect,58      a.XIsInEffect) = 159      IF @@ROWCOUNT > 060      BEGIN61        DECLARE @DBQueueElements_02 QBM_YDBQueueRaw62        INSERT INTO @DBQueueElements_02(object,63        subobject,64        genprocid)65        SELECT66          x.uid,67          NULL,68          @GenProcID69        FROM(70        SELECT71          DISTINCT co.uid_org AS uid72        FROM @ChangedOrg c73        JOIN BaseTreecollection co74          ON c.UID_SingleGuid = co.uid_parentorg) AS x75        EXEC QBM_PDBQueueInsert_Bulk 'RMS-K-OrgHasESet',76          @DBQueueElements_0277        DECLARE @DBQueueElements_03 QBM_YDBQueueRaw78        INSERT INTO @DBQueueElements_03(object,79        subobject,80        genprocid)81        SELECT82          x.uid,83          NULL,84          @GenProcID85        FROM(86        SELECT b.UID_Org AS uid87        FROM @ChangedOrg c88        JOIN BaseTree b89          ON c.UID_SingleGuid = b.UID_Org90        WHERE91          b.ITShopInfo = 'BO') AS x92        EXEC QBM_PDBQueueInsert_Bulk 'QER-K-OrgAutoChild',93          @DBQueueElements_0394        DECLARE @DBQueueElements_04 QBM_YDBQueueRaw95        INSERT INTO @DBQueueElements_04(object,96        subobject,97        genprocid)98        SELECT99          x.uid,100          NULL,101          @GenProcID102        FROM(103        SELECT pio.UID_Person AS uid104        FROM @ChangedOrg c105        JOIN PersoninBaseTree pio106          ON c.UID_SingleGuid = pio.UID_Org AND pio.XOrigin > 0107        UNION108        SELECT hpo.UID_Person AS uid109        FROM @ChangedOrg c110        JOIN HelperPersonOrg hpo111          ON c.UID_SingleGuid = hpo.UID_Org) AS x112        EXEC QBM_PDBQueueInsert_Bulk 'RMS-K-PersonHasESet',113          @DBQueueElements_04114        DECLARE @DBQueueElements_05 QBM_YDBQueueRaw115        INSERT INTO @DBQueueElements_05(object,116        subobject,117        genprocid)118        SELECT119          x.uid,120          NULL,121          @GenProcID122        FROM(123        SELECT pio.UID_WorkDesk AS uid124        FROM @ChangedOrg c125        JOIN WorkDeskinBaseTree pio126          ON c.UID_SingleGuid = pio.UID_Org AND pio.XOrigin > 0127        UNION128        SELECT hpo.UID_WorkDesk AS uid129        FROM @ChangedOrg c130        JOIN HelperWorkDeskOrg hpo131          ON c.UID_SingleGuid = hpo.UID_Org) AS x132        EXEC QBM_PDBQueueInsert_Bulk 'RMS-K-WorkdeskHasESet',133          @DBQueueElements_05134      END135      INSERT INTO @ElementBuffer(UID1,136      Ident1)137      SELECT138        DISTINCT v.UID_TaskBaseTree,139        t.TableName140      FROM deleted d141      JOIN BaseTreeHasESet a142        ON d.XObjectKey = a.XObjectKey143      JOIN ESetHasEntitlement ehe144        ON d.uid_ESet = ehe.uid_ESet AND ehe.XOrigin > 0145      JOIN DialogTable t146        ON ehe.Entitlement LIKE '<Key><T>' + t.TableName + '</T>%' AND t.TableName <> 'ESet'147      JOIN BaseTreeAssign v148        ON t.UID_DialogTable = v.UID_DialogTableElement149      WHERE150        v.UID_TaskBaseTree > ' ' AND dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin,151      a.XOrigin,152      d.XIsInEffect,153      a.XIsInEffect) = 1154      SELECT @ElementCount = @@rowcount155      SELECT @ElementIndex = 1156      WHILE @ElementIndex <= @ElementCount157      BEGIN158        SELECT159          TOP 1 @EsetOperation = bu.UID1,160          @EsetTable = bu.Ident1161        FROM @ElementBuffer bu162        WHERE163          bu.ElementIndex = @ElementIndex164        DELETE @DBQueueElements_spezial165        INSERT INTO @DBQueueElements_spezial(object,166        subobject,167        genprocid)168        SELECT169          x.uid,170          NULL,171          @GenProcID172        FROM(173        SELECT174          DISTINCT d.uid_org AS uid175        FROM deleted d176        JOIN BaseTreeHasESet a177          ON d.XObjectKey = a.XObjectKey178        JOIN ESetHasEntitlement ehe179          ON d.uid_ESet = ehe.uid_ESet AND ehe.XOrigin > 0180        WHERE181          ehe.Entitlement LIKE '<Key><T>' + @EsetTable + '</T>%' AND dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin,182        a.XOrigin, d.XIsInEffect, a.XIsInEffect) = 1) AS x183        EXEC QBM_PDBQueueInsert_Bulk @EsetOperation,184          @DBQueueElements_spezial185        SELECT @ElementIndex += 1186      END187    END188  END TRY189  BEGIN CATCH190    EXEC QBM_PSessionErrorAdd DEFAULT191    RAISERROR('',192    18,193    1)194      WITH NOWAIT195  END CATCH196  ende:197  RETURN198END