dbo.QER_TIDynamicGroup

1CREATE trigger QER_TIDynamicGroup2  ON DynamicGroup FOR3INSERT NOT FOR Replication4AS5BEGIN6  DECLARE @SQLToCheck QBM_YParameterList7  DECLARE @ErrorMessage nvarchar(4000)8  DECLARE @ErrorSeverity int9  DECLARE @ErrorState int10  BEGIN TRY11    IF EXISTS(12      SELECT TOP 1 113      FROM inserted)14    GOTO start15    RETURN start:16    DECLARE @GenProcID varchar(38)17    SELECT @GenProcID = dbo.QBM_FGISessionContext('')18    IF EXISTS(19      SELECT TOP 1 120      FROM inserted i21      JOIN BaseTree b22        ON i.ObjectKeyBaseTree = b.XObjectKey23      WHERE24        b.IsInvalidForDynamicGroup = 1)25    BEGIN26      RAISERROR('#LDS#Selected BaseTree is invalid for dynamic groups.|',27      18,28      1)29        WITH nowait30    END31    IF32    UPDATE(WhereClause)33    BEGIN34      INSERT INTO @SQLToCheck(Parameter1,35      ContentFull)36      SELECT37        c.XObjectKey,38        'select top 1 1 as columnvalue from ' + t.TableName + ' 39		where(40		' + c.WhereClause + '41		)'42      FROM inserted c43      JOIN DialogTable t44        ON c.UID_DialogTableObjectClass = t.uid_dialogtable45      WHERE46        c.WhereClause > ' '47      UPDATE @SQLToCheck48      SET ContentFull = REPLACE(contentFull,49      '@UID_Org',50      '''#''')51      EXEC QBM_PSQLCheckExecutable @SQLToCheck,52        @RaiseError = 153    END54    IF EXISTS(55      SELECT56        i.UID_DynamicGroup,57    i.UID_DialogTableObjectClass,58    a.*59      FROM inserted i60      JOIN BaseTree b61        ON i.ObjectKeyBaseTree = b.XObjectKey62      JOIN orgrootassign a63        ON b.UID_OrgRoot = a.UID_OrgRoot64      JOIN BaseTreeAssign ba65        ON ba.UID_BaseTreeAssign = a.UID_BaseTreeAssign66      JOIN DialogTable t67        ON ba.UID_DialogTableElement = t.UID_DialogTable AND i.UID_DialogTableObjectClass = t.UID_DialogTable68      WHERE69        a.IsAssignmentAllowed = 0) AND dbo.QBM_FGISessionContext('Transport') = ''70    BEGIN71      RAISERROR('#LDS#Combinations of role and object classes are not permitted because (direct) assignment are not allowed according to OrgRoot.|'72      ,73      18,74      1)75        WITH nowait76    END77    IF EXISTS(78      SELECT TOP 1 179      FROM inserted i80      JOIN BaseTree b181        ON i.ObjectKeyBaseTree = b1.XObjectKey82      JOIN BaseTreeExcludesBaseTree exc83        ON exc.uid_org = b1.UID_Org84      JOIN BaseTree b285        ON b2.UID_Org = exc.uid_orgExcluded86      JOIN DynamicGroup d87        ON b2.XObjectKey = d.ObjectKeyBaseTree) OR EXISTS(88    SELECT TOP 1 189    FROM inserted i90    JOIN BaseTree b191      ON i.ObjectKeyBaseTree = b1.XObjectKey92    JOIN BaseTreeExcludesBaseTree exc93      ON exc.uid_orgExcluded = b1.UID_Org94    JOIN BaseTree b295      ON b2.UID_Org = exc.uid_org96    JOIN DynamicGroup d97      ON b2.XObjectKey = d.ObjectKeyBaseTree)98    BEGIN99      RAISERROR('#LDS#Cannot make a definition because there is a role exclusion with another dynamic role.|',100      18,101      1)102        WITH nowait103    END104    IF EXISTS(105      SELECT TOP 1 1106      FROM inserted i107      JOIN DialogTable t108        ON i.UID_DialogTableObjectClass = t.UID_DialogTable109      WHERE110        t.TableName NOT IN('Hardware', 'Person', 'WorkDesk'))111    BEGIN112      SELECT @ErrorMessage = '#LDS#Object category is not permitted for dynamic groups:.|'113      RAISERROR(@ErrorMessage,114      18,115      1)116        WITH nowait117    END118    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw119    INSERT INTO @DBQueueElements_01(object,120    subobject,121    genprocid)122    SELECT123      x.uid,124      NULL,125      @GenProcID126    FROM(127    SELECT UID_DynamicGroup AS uid128    FROM inserted) AS x129    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-DynamicGroupMakeProc',130      @DBQueueElements_01131    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-DynamicGroupTest',132      @DBQueueElements_01133    EXEC QBM_PDBQueueInsert_Single 'QER-K-DynamicGroupMakeTrigger',134    '',135    '',136      @GenProcID137  END TRY138  BEGIN CATCH139    EXEC QBM_PSessionErrorAdd DEFAULT140    RAISERROR('',141    18,142    1)143      WITH NOWAIT144  END CATCH145END