dbo.RMS_TUESet

1CREATE trigger RMS_TUESet2  ON ESet FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @ObjectkeyOrdered varchar(138)7  DECLARE @uid_accproduct varchar(38)8  DECLARE @whereclauseOrg nvarchar(max)9  DECLARE @whereclauseMuster nvarchar(max) = ' UID_ITShopOrg in ( select UID_OrgPR 10							from QER_VPWOProductNodesSlim11							where ObjectkeyOrdered = ''@ObjectkeyOrdered'' 12								and isnull(UID_AccProduct, '''') <> ''@UID_AccProduct''13					)14'15  DECLARE @ElementLast int16  DECLARE @ElementBufferMulti QBM_YCursorBuffer17  DECLARE @ElementCount int18  DECLARE @ElementIndex int19  BEGIN TRY20    IF EXISTS(21      SELECT TOP 1 122      FROM inserted)23    GOTO start24    IF EXISTS(25      SELECT TOP 1 126      FROM deleted)27    GOTO start28    RETURN start:29    DECLARE @GenProcID varchar(38)30    SELECT @GenProcID = dbo.QBM_FGISessionContext('')31    DECLARE @vgl QBM_YParameterList32    DECLARE @TableName varchar(30)33    DECLARE @sqlcmd nvarchar(max) = ''34    IF35    UPDATE(isForITShop) OR36    UPDATE(isITShopOnly)37    BEGIN38      DECLARE @FlagTest QBM_YParameterlist39      INSERT INTO @FlagTest(Parameter1,40      Parameter2,41      HasContentFull,42      e.Parameter3)43      SELECT44        i.UID_ESet,45        i.XObjectKey,46        i.IsForITShop,47        dbo.QBM_FCVBigIntToString(i.IsITShopOnly)48      FROM inserted i49      EXEC QER_PIsForITShopFlagCheck 'ESet',50        @FlagTest51      IF EXISTS(52        SELECT TOP 1 153        FROM inserted i54        JOIN ESetHasEntitlement ehe55          ON i.XObjectKey = ehe.Entitlement AND ehe.XOrigin > 056        JOIN ESet e57          ON ehe.uid_ESet = e.uid_Eset AND e.IsITShopOnly = 058        WHERE59          i.IsForITShop = 1 AND i.IsITShopOnly = 1)60      BEGIN61        RAISERROR('#LDS#Changes cannot take place, because assignments to system roles still exist that may not be used exclusively in IT Shop.|',62        18,63        2)64          WITH nowait65      END66      SELECT67        @sqlcmd = STRING_AGG(convert(nvarchar(max), N '68		select ehe.uid_ESet, ehe.Entitlement -- , elem.IsForITShop , elem.IsITShopOnly69			from ESetHasEntitlement ehe join '70        + t.TableName + ' elem on ehe.Entitlement = elem.XObjectKey 71																	and ehe.XOrigin > 0 -- ohne XIsInEffect-Test, könnte ja jederzeit wieder angehen72			where elem.IsForITShop = 173			 and elem.IsITShopOnly = 174		'75        ),76        CONCAT(nCHAR(13), NCHAR(10), NCHAR(9), 'union all'))77      FROM DialogValidDynamicRef vr78      JOIN DialogTable t79        ON vr.UID_DialogTableReference = t.UID_DialogTable80      WHERE81        vr.UID_DialogColumn IN(82      SELECT c.UID_DialogColumn83      FROM DialogColumn c84      JOIN DialogTable t85        ON c.UID_DialogTable = t.UID_DialogTable86      WHERE87        c.ColumnName = 'Entitlement' AND t.TableName = 'EsetHasEntitlement') AND EXISTS(88      SELECT TOP 1 189      FROM DialogColumn cr90      WHERE91        cr.UID_DialogTable = t.UID_DialogTable AND cr.ColumnName = 'IsForITShop')92      INSERT INTO @vgl(Parameter1,93      Parameter2)94      EXEC sp_executesql @SQLcmd95      IF EXISTS(96        SELECT TOP 1 197        FROM inserted i98        JOIN @vgl AS x99          ON x.Parameter1 = i.uid_Eset100        WHERE101          i.IsITShopOnly = 0)102      BEGIN103        RAISERROR('#LDS#Changes cannot take place because products are assigned to the EntitlementSet that are only valid in IT Shop.|',104        18,105        2)106          WITH nowait107      END108    END109    IF110    UPDATE(IsInactive)111    BEGIN112      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw113      INSERT INTO @DBQueueElements_01(object,114      subobject,115      genprocid)116      SELECT117        x.uid,118        NULL,119        @GenProcID120      FROM(121      SELECT co.uid_Eset AS uid122      FROM ESet es123      JOIN deleted d124        ON es.uid_ESet = d.uid_ESet125      JOIN ESetCollection co126        ON co.UID_EsetChild = es.uid_ESet127      WHERE128        es.isinactive <> d.isInactive129      UNION130      SELECT es.uid_ESet131      FROM ESet es132      JOIN deleted d133        ON es.uid_ESet = d.uid_ESet134      WHERE135        es.isinactive <> d.isInactive136      UNION137      SELECT ehe.uid_ESet138      FROM ESet es139      JOIN deleted d140        ON es.uid_ESet = d.uid_ESet141      JOIN ESetHasEntitlement ehe142        ON ehe.entitlement = es.XObjectKey AND ehe.XOrigin > 0143      WHERE144        es.isinactive <> d.isInactive) AS x145      EXEC QBM_PDBQueueInsert_Bulk 'RMS-K-ESetCollection',146        @DBQueueElements_01147      DECLARE @DBQueueElements_02 QBM_YDBQueueRaw148      INSERT INTO @DBQueueElements_02(object,149      subobject,150      genprocid)151      SELECT152        x.uid,153        NULL,154        @GenProcID155      FROM(156      SELECT he.UID_Org AS uid157      FROM deleted d158      JOIN ESet e159        ON d.uid_ESet = e.uid_ESet160      JOIN BaseTreeHasESet he161        ON e.UID_ESet = he.UID_ESet162      WHERE163        e.IsInActive <> d.IsInActive) AS x164      EXEC QBM_PDBQueueInsert_Bulk 'RMS-K-OrgHasESet',165        @DBQueueElements_02166      DECLARE @DBQueueElements_03 QBM_YDBQueueRaw167      INSERT INTO @DBQueueElements_03(object,168      subobject,169      genprocid)170      SELECT171        x.uid,172        NULL,173        @GenProcID174      FROM(175      SELECT he.UID_Person AS uid176      FROM deleted d177      JOIN ESet e178        ON d.uid_ESet = e.uid_ESet179      JOIN PersonHasESet he180        ON e.UID_ESet = he.UID_ESet181      WHERE182        e.IsInActive <> d.IsInActive) AS x183      EXEC QBM_PDBQueueInsert_Bulk 'RMS-K-PersonHasESet',184        @DBQueueElements_03185      DECLARE @DBQueueElements_04 QBM_YDBQueueRaw186      INSERT INTO @DBQueueElements_04(object,187      subobject,188      genprocid)189      SELECT190        x.uid,191        NULL,192        @GenProcID193      FROM(194      SELECT he.UID_WorkDesk AS uid195      FROM deleted d196      JOIN ESet e197        ON d.uid_ESet = e.uid_ESet198      JOIN WorkDeskHasESet he199        ON e.UID_ESet = he.UID_ESet200      WHERE201        e.IsInActive <> d.IsInActive) AS x202      EXEC QBM_PDBQueueInsert_Bulk 'RMS-K-WorkdeskHasESet',203        @DBQueueElements_04204    END205    IF206    UPDATE(uid_accproduct)207    BEGIN208      DELETE @ElementBufferMulti209      INSERT INTO @ElementBufferMulti(ObjectKey1,210      UID1)211      SELECT212        x.XObjectKey,213        isnull(x.uid_accproduct,214        '')215      FROM ESet x216      JOIN deleted d217        ON x.uid_ESet = d.uid_ESet AND isnull(x.uid_accproduct,218      '') <> isnull(d.uid_accproduct,219      '') AND d.uid_accproduct > ' '220      JOIN dialogColumn c221        ON c.UID_DialogTable = 'QER-T-ITShopOrg' AND c.columnname = 'uid_ACCProduct' AND c.IsDeactivatedByPreProcessor = 0222      SELECT @ElementCount = @@ROWCOUNT223      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1224      SELECT @ElementLast = @@IDENTITY225      WHILE @ElementIndex <= @ElementLast226      BEGIN227        SELECT228          TOP 1 @ObjectkeyOrdered = bu.ObjectKey1,229          @UID_AccProduct = bu.UID1230        FROM @ElementBufferMulti bu231        WHERE232          bu.ElementIndex = @ElementIndex233        SELECT @whereclauseOrg = @whereclauseMuster234        SELECT235          @whereclauseOrg = replace(@whereclauseOrg,236          N '@ObjectkeyOrdered',237          @ObjectkeyOrdered)238        SELECT239          @whereclauseOrg = replace(@whereclauseOrg,240          N '@uid_accproduct',241          rtrim(@uid_accproduct))242        EXEC QBM_PJobCreate_HOUpdate_B 'ITShopOrg',243          @whereclauseOrg,244          @GenProcID,245          @p1 = 'uid_ACCProduct',246          @v1 = @uid_accproduct,247          @AdditionalObjectKeysAffected = DEFAULT248        SELECT @ElementIndex += 1249      END250    END251    IF252    UPDATE(UID_AccProduct)253    BEGIN254      DECLARE @RecalcParameter_OA QBM_YCursorBuffer255      INSERT INTO @RecalcParameter_OA(UID1,256      ObjectKey1,257      Ident1)258      SELECT259        'QER-PWODecisionRule-OA',260        a.XObjectKey,261        'I'262      FROM inserted i263      JOIN ESet g264        ON i.UID_ESet = g.UID_ESet265      JOIN QERAccProductUsage u266        ON g.XObjectKey = u.XObjectKey267      JOIN AccProduct a268        ON u.UID_AccProduct = a.UID_AccProduct269      UNION270      SELECT271        'QER-PWODecisionRule-OA',272        a.XObjectKey,273        'I'274      FROM deleted d275      JOIN ESet g276        ON d.UID_ESet = g.UID_ESet277      JOIN QERAccProductUsage u278        ON g.XObjectKey = u.XObjectKey279      JOIN AccProduct a280        ON u.UID_AccProduct = a.UID_AccProduct281      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_OA,282        @GenprocID283      DECLARE @RecalcParameter_OA2 QBM_YCursorBuffer284      INSERT INTO @RecalcParameter_OA2(UID1,285      ObjectKey1,286      Ident1)287      SELECT288        'QER-PWODecisionRule-OA',289        pwo.XObjectKey,290        'I'291      FROM inserted i292      JOIN ESet g293        ON i.UID_ESet = g.UID_ESet294      JOIN AccProduct a295        ON i.UID_AccProduct = a.UID_AccProduct296      JOIN PersonWantsOrg pwo297        ON g.XObjectKey = pwo.ObjectKeyElementUsedInAssign298      UNION299      SELECT300        'QER-PWODecisionRule-OA',301        pwo.XObjectKey,302        'I'303      FROM deleted d304      JOIN ESet g305        ON d.UID_ESet = g.UID_ESet306      JOIN AccProduct a307        ON d.UID_AccProduct = a.UID_AccProduct308      JOIN PersonWantsOrg pwo309        ON g.XObjectKey = pwo.ObjectKeyElementUsedInAssign310      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_OA2,311        @GenprocID312    END313  END TRY314  BEGIN CATCH315    EXEC QBM_PSessionErrorAdd DEFAULT316    RAISERROR('',317    18,318    1)319      WITH NOWAIT320  END CATCH321  ende:322  RETURN323END