Back to OIM Explorer

dbo.QER_TUDynamicGroup

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on DynamicGroup. Bulk DBQueue insert -> QER-K-DynamicGroupMakeProc / QER_ZDynamicGroupMakeProc at line 22; Bulk DBQueue insert -> QER-K-DynamicGroupTest / QER_ZDynamicGroupTest at line 22; Single DBQueue insert -> QER-K-DynamicGroupMakeTrigger / QER_ZDynamicGroupMakeTrigger at line 22; Single DBQueue insert -> QER-K-DynamicGroupMakeTrigger / QER_ZDynamicGroupMakeTrigger at line 27

Source: sandbox-db sys.sql_modules

Source size: 4.115 characters

Interpretation

  • Database trigger. Treat parent table and enqueue/object-layer calls as the main relation points.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> QER-K-DynamicGroupMakeProc / QER_ZDynamicGroupMakeProc at line 22
  • Bulk DBQueue insert -> QER-K-DynamicGroupTest / QER_ZDynamicGroupTest at line 22
  • Single DBQueue insert -> QER-K-DynamicGroupMakeTrigger / QER_ZDynamicGroupMakeTrigger at line 22
  • Single DBQueue insert -> QER-K-DynamicGroupMakeTrigger / QER_ZDynamicGroupMakeTrigger at line 27
  • References QBM_PDBQueueInsert_Single
  • References QBM_PDBQueueInsert_Bulk
  • Trigger parent table: DynamicGroup

Typed Edges

  • queues DBQueue task QER_ZDynamicGroupMakeProc at line 22 Bulk DBQueue insert -> QER-K-DynamicGroupMakeProc / QER_ZDynamicGroupMakeProc at line 22
  • queues DBQueue task QER_ZDynamicGroupTest at line 22 Bulk DBQueue insert -> QER-K-DynamicGroupTest / QER_ZDynamicGroupTest at line 22
  • queues DBQueue task QER_ZDynamicGroupMakeTrigger at line 22 Single DBQueue insert -> QER-K-DynamicGroupMakeTrigger / QER_ZDynamicGroupMakeTrigger at line 22
  • queues DBQueue task QER_ZDynamicGroupMakeTrigger at line 27 Single DBQueue insert -> QER-K-DynamicGroupMakeTrigger / QER_ZDynamicGroupMakeTrigger at line 27
  • trigger on table DynamicGroup Trigger parent table: DynamicGroup
  • references source dbo.QBM_FCVBinaryToString source text reference
  • references source dbo.QBM_FCVObjectkeyToElement source text reference
  • references source dbo.QBM_FGIBitPatternXOrigin source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PDBQueueInsert_Single source text reference
  • references source dbo.QBM_PExecuteSQLMulti source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSQLCheckExecutable source text reference

References

Referenced By

Complete Source

SQL175 lines
1CREATE trigger QER_TUDynamicGroup2  ON DynamicGroup FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @SQLToCheck QBM_YParameterList7  DECLARE @StringPatternDynamic varchar(16) = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Dynamic|',8  0)),9  0)10  DECLARE @StringPatternDynamicInv varchar(16) = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Dynamic|',11  1)),12  0)13  DECLARE @ElementBufferMulti QBM_YCursorBuffer14  DECLARE @ErrorMessage nvarchar(4000)15  DECLARE @ErrorSeverity int16  DECLARE @ErrorState int17  BEGIN TRY18    IF EXISTS(19      SELECT TOP 1 120      FROM inserted)21    GOTO start22    IF EXISTS(23      SELECT TOP 1 124      FROM deleted)25    GOTO start26    RETURN start:27    DECLARE @GenProcID varchar(38)28    SELECT @GenProcID = dbo.QBM_FGISessionContext('')29    IF30    UPDATE(ObjectKeyBaseTree)31    BEGIN32      IF EXISTS(33        SELECT TOP 1 134        FROM deleted d35        JOIN DynamicGroup g36          ON d.UID_DynamicGroup = g.UID_DynamicGroup37        JOIN BaseTree b38          ON g.ObjectKeyBaseTree = b.XObjectKey39        WHERE40          b.IsInvalidForDynamicGroup = 1 AND isnull(d.ObjectKeyBaseTree, '') <> isnull(g.ObjectKeyBaseTree,41      ''))42      BEGIN43        RAISERROR('#LDS#Selected BaseTree is invalid for dynamic groups.|',44        18,45        1)46          WITH nowait47      END48    END49    IF50    UPDATE(WhereClause)51    BEGIN52      INSERT INTO @SQLToCheck(Parameter1,53      ContentFull)54      SELECT55        c.XObjectKey,56        'select top 1 1 as columnvalue from ' + t.TableName + '57		 where(58		' + c.WhereClause + '59		)'60      FROM inserted c61      JOIN DialogTable t62        ON c.UID_DialogTableObjectClass = t.uid_dialogtable63      WHERE64        c.WhereClause > ' '65      UPDATE @SQLToCheck66      SET ContentFull = REPLACE(contentFull,67      '@UID_Org',68      '''#''')69      EXEC QBM_PSQLCheckExecutable @SQLToCheck,70        @RaiseError = 171    END72    IF73    UPDATE(ObjectKeyBaseTree) OR74    UPDATE(UID_DialogTableObjectClass) OR75    UPDATE(WhereClause)76    BEGIN77      IF EXISTS(78        SELECT79          i.UID_DynamicGroup,80      i.UID_DialogTableObjectClass,81      a.*82        FROM inserted i83        JOIN BaseTree b84          ON i.ObjectKeyBaseTree = b.XObjectKey85        JOIN orgrootassign a86          ON b.UID_OrgRoot = a.UID_OrgRoot87        JOIN BaseTreeAssign ba88          ON ba.UID_BaseTreeAssign = a.UID_BaseTreeAssign89        JOIN DialogTable t90          ON ba.UID_DialogTableElement = t.UID_DialogTable AND i.UID_DialogTableObjectClass = t.UID_DialogTable91        WHERE92          a.IsAssignmentAllowed = 0) AND dbo.QBM_FGISessionContext('Transport') = ''93      BEGIN94        RAISERROR('#LDS#Combinations of role and object classes are not permitted because (direct) assignment are not allowed according to OrgRoot.|'95        ,96        18,97        1)98          WITH nowait99      END100      IF EXISTS(101        SELECT TOP 1 1102        FROM inserted i103        JOIN DialogTable t104          ON i.UID_DialogTableObjectClass = t.UID_DialogTable105        WHERE106          t.TableName NOT IN('Hardware', 'Person', 'WorkDesk'))107      BEGIN108        SELECT @ErrorMessage = '#LDS#Object category is not permitted for dynamic groups:.|'109        RAISERROR(@ErrorMessage,110        18,111        1)112          WITH nowait113      END114      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw115      INSERT INTO @DBQueueElements_01(object,116      subobject,117      genprocid)118      SELECT119        x.uid,120        NULL,121        @GenProcID122      FROM(123      SELECT t.UID_DynamicGroup AS uid124      FROM DynamicGroup t125      JOIN deleted d126        ON t.UID_DynamicGroup = d.UID_DynamicGroup) AS x127      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-DynamicGroupMakeProc',128        @DBQueueElements_01129      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-DynamicGroupTest',130        @DBQueueElements_01131      DELETE @ElementBufferMulti132      INSERT INTO @ElementBufferMulti(ContentFull)133      SELECT134        CONCAT('update ',135      CASE d.UID_DialogTableObjectClass136        WHEN 'QER-T-Hardware' THEN137      'HardwareInBaseTree'138        WHEN 'QER-T-WorkDesk' THEN139      'WorkDeskInBaseTree'140        WHEN 'QER-T-Person' THEN141      'PersonInBaseTree'142      ELSE ''143      END,144      ' set XOrigin = XOrigin & ',145      @StringPatternDynamicInv,146      ' where uid_org = ''',147      rtrim(dbo.QBM_FCVObjectkeyToElement('Columnvalue1', d.ObjectKeyBaseTree)),148      ''' ')149      FROM DynamicGroup dg150      JOIN deleted d151        ON dg.uid_DynamicGroup = d.UID_DynamicGroup152      WHERE153        dg.ObjectKeyBaseTree <> d.ObjectKeyBaseTree OR dg.UID_DialogTableObjectClass <> d.UID_DialogTableObjectClass154      EXEC QBM_PExecuteSQLMulti @ElementBufferMulti155    END156    IF157    UPDATE(whereclause) OR158    UPDATE(IsCalculateImmediately)159    BEGIN160      EXEC QBM_PDBQueueInsert_Single 'QER-K-DynamicGroupMakeTrigger',161      '',162      '',163        @GenProcID164    END165  END TRY166  BEGIN CATCH167    EXEC QBM_PSessionErrorAdd DEFAULT168    RAISERROR('',169    18,170    1)171      WITH NOWAIT172  END CATCH173  ende:174  RETURN175END
Open raw exported source
SQL ยท Raw29 lines
1create   trigger QER_TUDynamicGroup on DynamicGroup  for Update not for Replication as begin  declare @SQLToCheck QBM_YParameterList declare @StringPatternDynamic2 varchar(16) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Dynamic|',0)), 0) declare @StringPatternDynamicInv varchar(163) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Dynamic|',1)), 0) declare @ElementBufferMulti QBM_YCursorBuffer declare4 @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int BEGIN TRY if exists (select top 1 1 from inserted) goto start if exists5 (select top 1 1 from deleted) goto start return start: declare @GenProcID varchar(38) select @GenProcID = dbo.QBM_FGISessionContext('') if update(ObjectKeyBaseTree6) begin if exists (select top 1 1 from deleted d join DynamicGroup g on d.UID_DynamicGroup = g.UID_DynamicGroup join BaseTree b on g.ObjectKeyBaseTree 7= b.XObjectKey where b.IsInvalidForDynamicGroup = 1 and isnull(d.ObjectKeyBaseTree, '') <> isnull(g.ObjectKeyBaseTree, '') ) begin raiserror( '#LDS#Selected BaseTree is invalid for dynamic groups.|'8, 18, 1) with nowait end end  if update(WhereClause) begin insert into @SQLToCheck(Parameter1, ContentFull) select c.XObjectKey, 'select top 1 1 as columnvalue from '9 + t.TableName + '10		 where(11		' + c.WhereClause + '12		)' from inserted c  join DialogTable t on c.UID_DialogTableObjectClass = t.uid_dialogtable where13 c.WhereClause > ' '  update @SQLToCheck set ContentFull = REPLACE(contentFull , '@UID_Org', '''#''') exec QBM_PSQLCheckExecutable @SQLToCheck, @RaiseError14 = 1 end if update(ObjectKeyBaseTree) or update(UID_DialogTableObjectClass) or update(WhereClause) begin  if exists (select i.UID_DynamicGroup, i.UID_DialogTableObjectClass15, a.* from inserted i join BaseTree b on i.ObjectKeyBaseTree = b.XObjectKey join orgrootassign a on b.UID_OrgRoot = a.UID_OrgRoot join BaseTreeAssign ba16 on ba.UID_BaseTreeAssign = a.UID_BaseTreeAssign join DialogTable t on ba.UID_DialogTableElement = t.UID_DialogTable and i.UID_DialogTableObjectClass =17 t.UID_DialogTable where a.IsAssignmentAllowed = 0  )  and dbo.QBM_FGISessionContext ('Transport') = '' begin  raiserror( '#LDS#Combinations of role and object classes are not permitted because (direct) assignment are not allowed according to OrgRoot.|'18, 18, 1) with nowait end  if exists (select top 1 1 from inserted i join DialogTable t on i.UID_DialogTableObjectClass = t.UID_DialogTable where t.TableName19 not in ('Hardware' , 'Person' , 'WorkDesk' ) ) begin select @ErrorMessage = '#LDS#Object category is not permitted for dynamic groups:.|'  raiserror( 20@ErrorMessage, 18, 1) with nowait end   declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select21 x.uid, null, @GenProcID from ( select t.UID_DynamicGroup as uid from DynamicGroup t join deleted d on t.UID_DynamicGroup = d.UID_DynamicGroup ) as x exec22 QBM_PDBQueueInsert_Bulk 'QER-K-DynamicGroupMakeProc', @DBQueueElements_01 exec QBM_PDBQueueInsert_Bulk 'QER-K-DynamicGroupTest', @DBQueueElements_01  23delete  @ElementBufferMulti insert into @ElementBufferMulti(ContentFull) select concat( 'update ' , case d.UID_DialogTableObjectClass when 'QER-T-Hardware'24 then 'HardwareInBaseTree' when 'QER-T-WorkDesk' then 'WorkDeskInBaseTree' when 'QER-T-Person' then 'PersonInBaseTree' else '' end , ' set XOrigin = XOrigin & '25 , @StringPatternDynamicInv , ' where uid_org = ''' , rtrim(dbo.QBM_FCVObjectkeyToElement('Columnvalue1', d.ObjectKeyBaseTree)) , ''' ' ) from DynamicGroup26 dg join deleted d on dg.uid_DynamicGroup = d.UID_DynamicGroup where dg.ObjectKeyBaseTree <> d.ObjectKeyBaseTree or dg.UID_DialogTableObjectClass <> d.UID_DialogTableObjectClass27 exec QBM_PExecuteSQLMulti @ElementBufferMulti end  if UPDATE(whereclause) or UPDATE(IsCalculateImmediately) begin  exec QBM_PDBQueueInsert_Single 'QER-K-DynamicGroupMakeTrigger'28, '', '', @GenProcID end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end 29