Back to OIM Explorer

dbo.QER_TUOrgRootAssign

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on OrgRootAssign. Bulk DBQueue insert -> QER-K-AllForOnePerson / QER_ZAllForOnePerson at line 47; Bulk DBQueue insert -> QER-K-AllForOneWorkdesk / QER_ZAllForOneWorkdesk at line 47; Bulk DBQueue insert -> QER-K-AllForOneHardware / QER_ZAllForOneHardware at line 47; Bulk DBQueue insert -> QER-K-AllForOneWorkdesk / QER_ZAllForOneWorkdesk at line 51

Source: sandbox-db sys.sql_modules

Source size: 7.345 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-AllForOnePerson / QER_ZAllForOnePerson at line 47
  • Bulk DBQueue insert -> QER-K-AllForOneWorkdesk / QER_ZAllForOneWorkdesk at line 47
  • Bulk DBQueue insert -> QER-K-AllForOneHardware / QER_ZAllForOneHardware at line 47
  • Bulk DBQueue insert -> QER-K-AllForOneWorkdesk / QER_ZAllForOneWorkdesk at line 51
  • Bulk DBQueue insert -> QER-K-AllForOneHardware / QER_ZAllForOneHardware at line 51
  • Bulk DBQueue insert -> QER-K-AllForOneHardware / QER_ZAllForOneHardware at line 54
  • References QBM_PDBQueueInsert_Bulk
  • Trigger parent table: OrgRootAssign

Typed Edges

  • queues DBQueue task QER_ZAllForOnePerson at line 47 Bulk DBQueue insert -> QER-K-AllForOnePerson / QER_ZAllForOnePerson at line 47
  • queues DBQueue task QER_ZAllForOneWorkdesk at line 47 Bulk DBQueue insert -> QER-K-AllForOneWorkdesk / QER_ZAllForOneWorkdesk at line 47
  • queues DBQueue task QER_ZAllForOneHardware at line 47 Bulk DBQueue insert -> QER-K-AllForOneHardware / QER_ZAllForOneHardware at line 47
  • queues DBQueue task QER_ZAllForOneWorkdesk at line 51 Bulk DBQueue insert -> QER-K-AllForOneWorkdesk / QER_ZAllForOneWorkdesk at line 51
  • queues DBQueue task QER_ZAllForOneHardware at line 51 Bulk DBQueue insert -> QER-K-AllForOneHardware / QER_ZAllForOneHardware at line 51
  • queues DBQueue task QER_ZAllForOneHardware at line 54 Bulk DBQueue insert -> QER-K-AllForOneHardware / QER_ZAllForOneHardware at line 54
  • trigger on table OrgRootAssign Trigger parent table: OrgRootAssign
  • references source dbo.QBM_FCVBinaryToString source text reference
  • references source dbo.QBM_FGIBitPatternXOrigin source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_TUOrgRoot source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL299 lines
1CREATE trigger QER_TUOrgRootAssign2  ON OrgRootAssign FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @uid_orgroot varchar(38)7  DECLARE @uid_BaseTreeAssign varchar(38)8  DECLARE @TableNameMN varchar(30)9  DECLARE @CountItems int10  DECLARE @SQLcmd nvarchar(max)11  DECLARE @UID_DialogTableElement varchar(38)12  DECLARE @StringPatternDirect varchar(16)13  DECLARE @ElementLast int14  DECLARE @ElementBufferMulti QBM_YCursorBuffer15  DECLARE @ElementCount int16  DECLARE @ElementIndex 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    SELECT30      @StringPatternDirect = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Direct|',31      0)),32      0)33    IF34    UPDATE(IsAssignmentAllowed) OR35    UPDATE(IsDirectAssignmentAllowed)36    BEGIN37      IF EXISTS(38        SELECT TOP 1 139        FROM OrgRootAssign r40        JOIN deleted d41          ON d.XObjectKey = r.XObjectKey42        JOIN(43        SELECT44          'QER-V-ITShopOrg' AS uid_Orgroot, 'QER-T-Hardware' AS UID_BaseTreeAssign45        UNION all46        SELECT47          'QER-V-ITShopOrg', 'QER-T-WorkDesk'48        UNION all49        SELECT50          'QER-V-ITShopOrg', 'QER-T-Person'51        UNION all52        SELECT53          'QER-V-ITShopSrc', 'QER-T-Hardware'54        UNION all55        SELECT56          'QER-V-ITShopSrc', 'QER-T-WorkDesk'57        UNION all58        SELECT59          'QER-V-ITShopSrc', 'QER-T-Person'60        UNION all61        SELECT62          'CPL-V-NonCompliance', '%'63        UNION all64        SELECT65          'QER-V-AERole', 'QER-T-Hardware'66        UNION all67        SELECT68          'QER-V-AERole', 'QER-T-WorkDesk'69        UNION all70        SELECT71          'QER-V-AERole', 'QER-T-Person') AS x72        ON r.UID_OrgRoot = x.uid_Orgroot AND r.UID_BaseTreeAssign LIKE x.UID_BaseTreeAssign73      WHERE74        (r.IsAssignmentAllowed <> d.IsAssignmentAllowed OR r.IsDirectAssignmentAllowed <> d.IsDirectAssignmentAllowed))75      BEGIN76        RAISERROR('#LDS#Changes cannot be made, otherwise the default assignment possibilities are violated.|',77        18,78        1)79          WITH nowait80      END81    END82    IF83    UPDATE(IsAssignmentAllowed)84    BEGIN85      DELETE @ElementBufferMulti86      INSERT INTO @ElementBufferMulti(UID1,87      UID2,88      Ident1,89      UID3)90      SELECT91        r.uid_Orgroot,92        r.UID_BaseTreeAssign,93        t.TableName AS TableNameMN,94        a.UID_DialogTableElement95      FROM OrgRootAssign r96      JOIN deleted d97        ON d.XObjectKey = r.XObjectKey98      JOIN BaseTreeAssign a99        ON a.UID_BaseTreeAssign = r.UID_BaseTreeAssign100      JOIN DialogTable t101        WITH(readpast)102        ON a.UID_DialogTableMN = t.UID_DialogTable103      WHERE104        convert(int,105      d.IsAssignmentAllowed) &(convert(int, d.IsAssignmentAllowed) ^ convert(int, r.IsAssignmentAllowed)) > 0106      SELECT @ElementCount = @@ROWCOUNT107      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1108      SELECT @ElementLast = @@IDENTITY109      WHILE @ElementIndex <= @ElementLast110      BEGIN111        SELECT112          TOP 1 @uid_OrgRoot = bu.UID1,113          @uid_BaseTreeAssign = bu.UID2,114          @TableNameMN = bu.Ident1,115          @UID_DialogTableElement = bu.UID3116        FROM @ElementBufferMulti bu117        WHERE118          bu.ElementIndex = @ElementIndex119        SELECT120          @SQLcmd = 'select 1 121									where exists (select top 1 1 122													from BaseTree b join ' + @TableNameMN + ' pio on b.uid_org = pio.uid_org123													where b.uid_OrgRoot = '''124          + @uid_OrgRoot + '''125												)'126        EXEC @CountItems = QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,127          @LockTimeout_ms = DEFAULT,128          @MaxWaitTimeForLock_s = DEFAULT,129          @ProcIDForJournal = @@procid,130          @HandleErrorSilent = 0131        IF @CountItems > 0132        BEGIN133          RAISERROR('#LDS#Changes cannot be made because there are assignments for the membership.|',134          18,135          1)136            WITH nowait137        END138        IF EXISTS(139          SELECT TOP 1 1140          FROM DynamicGroup dg141          JOIN BaseTree b142            ON dg.ObjectKeyBaseTree = b.XObjectKey143          WHERE144            dg.UID_DialogTableObjectClass = @UID_DialogTableElement AND b.UID_OrgRoot = @uid_OrgRoot)145        BEGIN146          RAISERROR('#LDS#Changes can not be made because the affected roles are used in dynamic groups.|',147          18,148          1)149            WITH nowait150        END151        SELECT @ElementIndex += 1152      END153    END154    IF155    UPDATE(IsDirectAssignmentAllowed)156    BEGIN157      DELETE @ElementBufferMulti158      INSERT INTO @ElementBufferMulti(UID1,159      UID2,160      Ident1,161      UID3)162      SELECT163        r.uid_Orgroot,164        r.UID_BaseTreeAssign,165        t.TableName AS TableNameMN,166        a.UID_DialogTableElement167      FROM OrgRootAssign r168      JOIN deleted d169        ON d.XObjectKey = r.XObjectKey170      JOIN BaseTreeAssign a171        ON a.UID_BaseTreeAssign = r.UID_BaseTreeAssign172      JOIN DialogTable t173        WITH(readpast)174        ON a.UID_DialogTableMN = t.UID_DialogTable175      WHERE176        convert(int,177      d.IsDirectAssignmentAllowed) &(convert(int, d.IsDirectAssignmentAllowed) ^ convert(int, r.IsDirectAssignmentAllowed)) > 0178      SELECT @ElementCount = @@ROWCOUNT179      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1180      SELECT @ElementLast = @@IDENTITY181      WHILE @ElementIndex <= @ElementLast182      BEGIN183        SELECT184          TOP 1 @uid_OrgRoot = bu.UID1,185          @uid_BaseTreeAssign = bu.UID2,186          @TableNameMN = bu.Ident1,187          @UID_DialogTableElement = bu.UID3188        FROM @ElementBufferMulti bu189        WHERE190          bu.ElementIndex = @ElementIndex191        SELECT192          @SQLcmd = CONCAT('select 1 193									where exists (select top 1 1 194													from BaseTree b join ',195          @TableNameMN,196          ' pio on b.uid_org = pio.uid_org197													where b.uid_OrgRoot = ''',198          @uid_OrgRoot,199          '''200													',201        CASE202          WHEN dbo.QBM_FGIColumnExists(@TableNameMN, 'XOrigin') = 1 THEN203        CONCAT(' and pio.XOrigin & ', @StringPatternDirect, ' > 0')204        ELSE ''205        END,206        '207												)')208        EXEC @CountItems = QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,209          @LockTimeout_ms = DEFAULT,210          @MaxWaitTimeForLock_s = DEFAULT,211          @ProcIDForJournal = @@procid,212          @HandleErrorSilent = 0213        IF @CountItems > 0214        BEGIN215          RAISERROR('#LDS#Changes cannot be made because there are direct assignments for the membership.|',216          18,217          1)218            WITH nowait219        END220        SELECT @ElementIndex += 1221      END222    END223    IF224    UPDATE(IsAssignmentAllowed)225    BEGIN226      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw227      INSERT INTO @DBQueueElements_01(object,228      subobject,229      genprocid)230      SELECT231        x.uid,232        NULL,233        @GenProcID234      FROM(235      SELECT236        DISTINCT pio.uid_person AS uid237      FROM OrgRootAssign x238      JOIN deleted d239        ON x.XObjectKey = d.XObjectKey AND(convert(int, d.IsAssignmentAllowed) ^ convert(int, x.IsAssignmentAllowed)) & 1 > 0240      JOIN BaseTree b241        ON b.uid_orgroot = x.uid_orgroot242      JOIN personinBaseTree pio243        ON b.uid_org = pio.uid_org AND pio.XOrigin > 0244      WHERE245        b.IsNoInheriteToPerson = 0) AS x246      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-AllForOnePerson',247        @DBQueueElements_01248      DECLARE @DBQueueElements_02 QBM_YDBQueueRaw249      INSERT INTO @DBQueueElements_02(object,250      subobject,251      genprocid)252      SELECT253        x.uid,254        NULL,255        @GenProcID256      FROM(257      SELECT258        DISTINCT pio.uid_WorkDesk AS uid259      FROM OrgRootAssign x260      JOIN deleted d261        ON x.XObjectKey = d.XObjectKey AND(convert(int, d.IsAssignmentAllowed) ^ convert(int, x.IsAssignmentAllowed)) & 1 > 0262      JOIN BaseTree b263        ON b.uid_orgroot = x.uid_orgroot264      JOIN WorkDeskinBaseTree pio265        ON b.uid_org = pio.uid_org AND pio.XOrigin > 0) AS x266      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-AllForOneWorkdesk',267        @DBQueueElements_02268      DECLARE @DBQueueElements_03 QBM_YDBQueueRaw269      INSERT INTO @DBQueueElements_03(object,270      subobject,271      genprocid)272      SELECT273        x.uid,274        NULL,275        @GenProcID276      FROM(277      SELECT278        DISTINCT pio.uid_Hardware AS uid279      FROM OrgRootAssign x280      JOIN deleted d281        ON x.XObjectKey = d.XObjectKey AND(convert(int, d.IsAssignmentAllowed) ^ convert(int, x.IsAssignmentAllowed)) & 1 > 0282      JOIN BaseTree b283        ON b.uid_orgroot = x.uid_orgroot284      JOIN HardwareinBaseTree pio285        ON b.uid_org = pio.uid_org AND pio.XOrigin > 0) AS x286      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-AllForOneHardware',287        @DBQueueElements_03288    END289  END TRY290  BEGIN CATCH291    EXEC QBM_PSessionErrorAdd DEFAULT292    RAISERROR('',293    18,294    1)295      WITH NOWAIT296  END CATCH297  ende:298  RETURN299END
Open raw exported source
SQL ยท Raw56 lines
1 create   trigger QER_TUOrgRootAssign on OrgRootAssign for update not for Replication as begin declare @uid_orgroot varchar(38) declare @uid_BaseTreeAssign2 varchar(38) declare @TableNameMN varchar(30) declare @CountItems int declare @SQLcmd nvarchar(max) declare @UID_DialogTableElement varchar(38) declare3 @StringPatternDirect varchar(16) declare @ElementLast int declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex4 int BEGIN TRY if exists (select top 1 1 from inserted) goto start if exists (select top 1 1 from deleted) goto start return start: declare @GenProcID 5varchar(38) select @GenProcID = dbo.QBM_FGISessionContext('') select @StringPatternDirect = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin6('|Direct|',0)), 0) if update(IsAssignmentAllowed) or update(IsDirectAssignmentAllowed) begin if exists (select top 1 1 from OrgRootAssign r join deleted7 d on d.XObjectKey = r.XObjectKey join ( select 'QER-V-ITShopOrg' as uid_Orgroot, 'QER-T-Hardware' as UID_BaseTreeAssign union all select 'QER-V-ITShopOrg'8, 'QER-T-WorkDesk' union all select 'QER-V-ITShopOrg', 'QER-T-Person' union all select 'QER-V-ITShopSrc', 'QER-T-Hardware' union all select 'QER-V-ITShopSrc'9, 'QER-T-WorkDesk' union all select 'QER-V-ITShopSrc', 'QER-T-Person' union all select 'CPL-V-NonCompliance', '%' union all select 'QER-V-AERole', 'QER-T-Hardware'10 union all select 'QER-V-AERole', 'QER-T-WorkDesk' union all select 'QER-V-AERole', 'QER-T-Person' ) as x on r.UID_OrgRoot = x.uid_Orgroot and r.UID_BaseTreeAssign11 like x.UID_BaseTreeAssign where (r.IsAssignmentAllowed <> d.IsAssignmentAllowed or r.IsDirectAssignmentAllowed <> d.IsDirectAssignmentAllowed ) ) begin12 raiserror( '#LDS#Changes cannot be made, otherwise the default assignment possibilities are violated.|', 18, 1) with nowait end end   if update(IsAssignmentAllowed13) begin delete  @ElementBufferMulti insert into @ElementBufferMulti(UID1, UID2, Ident1, UID3) select r.uid_Orgroot, r.UID_BaseTreeAssign, t.TableName as14 TableNameMN  , a.UID_DialogTableElement from OrgRootAssign r join deleted d on d.XObjectKey = r.XObjectKey join BaseTreeAssign a on a.UID_BaseTreeAssign15 = r.UID_BaseTreeAssign join DialogTable t with (readpast) on a.UID_DialogTableMN = t.UID_DialogTable where convert(int, d.IsAssignmentAllowed) & (convert16(int, d.IsAssignmentAllowed) ^ convert(int, r.IsAssignmentAllowed)) > 0 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount17 +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @uid_OrgRoot = bu.UID1 , @uid_BaseTreeAssign = bu.UID2 , @TableNameMN18 = bu.Ident1 , @UID_DialogTableElement = bu.UID3 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex select @SQLcmd = 'select 1 19									where exists (select top 1 1 20													from BaseTree b join '21 + @TableNameMN + ' pio on b.uid_org = pio.uid_org22													where b.uid_OrgRoot = ''' + @uid_OrgRoot + '''23												)' exec @CountItems = QBM_PExecuteSQLWithRetry_LLP24 @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 if @CountItems25 > 0 begin raiserror( '#LDS#Changes cannot be made because there are assignments for the membership.|', 18, 1) with nowait end if exists (select top 1 261 from DynamicGroup dg join BaseTree b on dg.ObjectKeyBaseTree = b.XObjectKey where dg.UID_DialogTableObjectClass = @UID_DialogTableElement and b.UID_OrgRoot27 = @uid_OrgRoot ) begin  raiserror( '#LDS#Changes can not be made because the affected roles are used in dynamic groups.|', 18, 1) with nowait end select28 @ElementIndex += 1 end  end  if update(IsDirectAssignmentAllowed) begin delete  @ElementBufferMulti insert into @ElementBufferMulti(UID1, UID2, Ident129, UID3) select r.uid_Orgroot, r.UID_BaseTreeAssign, t.TableName as TableNameMN  , a.UID_DialogTableElement from OrgRootAssign r join deleted d on d.XObjectKey30 = r.XObjectKey join BaseTreeAssign a on a.UID_BaseTreeAssign = r.UID_BaseTreeAssign join DialogTable t with (readpast) on a.UID_DialogTableMN = t.UID_DialogTable31 where convert(int, d.IsDirectAssignmentAllowed) & (convert(int, d.IsDirectAssignmentAllowed) ^ convert(int, r.IsDirectAssignmentAllowed)) > 0 select @ElementCount32 = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top33 1 @uid_OrgRoot = bu.UID1 , @uid_BaseTreeAssign = bu.UID2 , @TableNameMN = bu.Ident1 , @UID_DialogTableElement = bu.UID3 from @ElementBufferMulti bu where34 bu.ElementIndex = @ElementIndex select @SQLcmd = concat('select 1 35									where exists (select top 1 1 36													from BaseTree b join ' , @TableNameMN37 , ' pio on b.uid_org = pio.uid_org38													where b.uid_OrgRoot = ''' , @uid_OrgRoot , '''39													', case when dbo.QBM_FGIColumnExists(@TableNameMN40, 'XOrigin' ) = 1 then concat(' and pio.XOrigin & ' , @StringPatternDirect , ' > 0') else '' end , '41												)' )  exec @CountItems = QBM_PExecuteSQLWithRetry_LLP42 @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 if @CountItems43 > 0 begin raiserror( '#LDS#Changes cannot be made because there are direct assignments for the membership.|', 18, 1) with nowait end  select @ElementIndex44 += 1 end  end   if update(IsAssignmentAllowed) begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid45) select x.uid, null, @GenProcID from ( select distinct pio.uid_person as uid from OrgRootAssign x join deleted d on x.XObjectKey = d.XObjectKey and (convert46(int, d.IsAssignmentAllowed) ^ convert(int, x.IsAssignmentAllowed) ) & 1 > 0 join BaseTree b on b.uid_orgroot = x.uid_orgroot join personinBaseTree pio47 on b.uid_org = pio.uid_org and pio.XOrigin > 0 where b.IsNoInheriteToPerson = 0 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-AllForOnePerson', @DBQueueElements_0148 declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select49 distinct pio.uid_WorkDesk as uid from OrgRootAssign x join deleted d on x.XObjectKey = d.XObjectKey and (convert(int, d.IsAssignmentAllowed) ^ convert50(int, x.IsAssignmentAllowed) ) & 1 > 0 join BaseTree b on b.uid_orgroot = x.uid_orgroot join WorkDeskinBaseTree pio on b.uid_org = pio.uid_org and pio.XOrigin51 > 0 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-AllForOneWorkdesk', @DBQueueElements_02 declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_0352 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select distinct pio.uid_Hardware as uid from OrgRootAssign x join deleted d on x.XObjectKey53 = d.XObjectKey and (convert(int, d.IsAssignmentAllowed) ^ convert(int, x.IsAssignmentAllowed) ) & 1 > 0 join BaseTree b on b.uid_orgroot = x.uid_orgroot54 join HardwareinBaseTree pio on b.uid_org = pio.uid_org and pio.XOrigin > 0 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-AllForOneHardware', @DBQueueElements_0355 end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end 56