dbo.QER_TUOrgRootAssign

1 create   trigger QER_TUOrgRootAssign on OrgRootAssign for update not for Replication as begin declare @uid_orgroot varchar(38) declare @uid_BaseTreeAssign
2 varchar(38) declare @TableNameMN varchar(30) declare @CountItems int declare @SQLcmd nvarchar(max) declare @UID_DialogTableElement varchar(38) declare
3 @StringPatternDirect varchar(16) declare @ElementLast int declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex
4 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_FGIBitPatternXOrigin
6('|Direct|',0)), 0) if update(IsAssignmentAllowed) or update(IsDirectAssignmentAllowed) begin if exists (select top 1 1 from OrgRootAssign r join deleted
7 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_BaseTreeAssign
11 like x.UID_BaseTreeAssign where (r.IsAssignmentAllowed <> d.IsAssignmentAllowed or r.IsDirectAssignmentAllowed <> d.IsDirectAssignmentAllowed ) ) begin
12 raiserror( '#LDS#Changes cannot be made, otherwise the default assignment possibilities are violated.|', 18, 1) with nowait end end   if update(IsAssignmentAllowed
13) begin delete  @ElementBufferMulti insert into @ElementBufferMulti(UID1, UID2, Ident1, UID3) select r.uid_Orgroot, r.UID_BaseTreeAssign, t.TableName as
14 TableNameMN  , a.UID_DialogTableElement from OrgRootAssign r join deleted d on d.XObjectKey = r.XObjectKey join BaseTreeAssign a on a.UID_BaseTreeAssign
15 = r.UID_BaseTreeAssign join DialogTable t with (readpast) on a.UID_DialogTableMN = t.UID_DialogTable where convert(int, d.IsAssignmentAllowed) & (convert
16(int, d.IsAssignmentAllowed) ^ convert(int, r.IsAssignmentAllowed)) > 0 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount
17 +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @uid_OrgRoot = bu.UID1 , @uid_BaseTreeAssign = bu.UID2 , @TableNameMN
18 = 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_org
22													where b.uid_OrgRoot = ''' + @uid_OrgRoot + '''
23												)' exec @CountItems = QBM_PExecuteSQLWithRetry_LLP
24 @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 if @CountItems
25 > 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_OrgRoot
27 = @uid_OrgRoot ) begin  raiserror( '#LDS#Changes can not be made because the affected roles are used in dynamic groups.|', 18, 1) with nowait end select
28 @ElementIndex += 1 end  end  if update(IsDirectAssignmentAllowed) begin delete  @ElementBufferMulti insert into @ElementBufferMulti(UID1, UID2, Ident1
29, UID3) select r.uid_Orgroot, r.UID_BaseTreeAssign, t.TableName as TableNameMN  , a.UID_DialogTableElement from OrgRootAssign r join deleted d on d.XObjectKey
30 = r.XObjectKey join BaseTreeAssign a on a.UID_BaseTreeAssign = r.UID_BaseTreeAssign join DialogTable t with (readpast) on a.UID_DialogTableMN = t.UID_DialogTable
31 where convert(int, d.IsDirectAssignmentAllowed) & (convert(int, d.IsDirectAssignmentAllowed) ^ convert(int, r.IsDirectAssignmentAllowed)) > 0 select @ElementCount
32 = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top
33 1 @uid_OrgRoot = bu.UID1 , @uid_BaseTreeAssign = bu.UID2 , @TableNameMN = bu.Ident1 , @UID_DialogTableElement = bu.UID3 from @ElementBufferMulti bu where
34 bu.ElementIndex = @ElementIndex select @SQLcmd = concat('select 1 
35									where exists (select top 1 1 
36													from BaseTree b join ' , @TableNameMN
37 , ' pio on b.uid_org = pio.uid_org
38													where b.uid_OrgRoot = ''' , @uid_OrgRoot , '''
39													', case when dbo.QBM_FGIColumnExists(@TableNameMN
40, 'XOrigin' ) = 1 then concat(' and pio.XOrigin & ' , @StringPatternDirect , ' > 0') else '' end , '
41												)' )  exec @CountItems = QBM_PExecuteSQLWithRetry_LLP
42 @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 if @CountItems
43 > 0 begin raiserror( '#LDS#Changes cannot be made because there are direct assignments for the membership.|', 18, 1) with nowait end  select @ElementIndex
44 += 1 end  end   if update(IsAssignmentAllowed) begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid
45) 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 (convert
46(int, d.IsAssignmentAllowed) ^ convert(int, x.IsAssignmentAllowed) ) & 1 > 0 join BaseTree b on b.uid_orgroot = x.uid_orgroot join personinBaseTree pio
47 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_01
48 declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select
49 distinct pio.uid_WorkDesk as uid from OrgRootAssign x join deleted d on x.XObjectKey = d.XObjectKey and (convert(int, d.IsAssignmentAllowed) ^ convert
50(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.XOrigin
51 > 0 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-AllForOneWorkdesk', @DBQueueElements_02 declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03
52 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select distinct pio.uid_Hardware as uid from OrgRootAssign x join deleted d on x.XObjectKey
53 = d.XObjectKey and (convert(int, d.IsAssignmentAllowed) ^ convert(int, x.IsAssignmentAllowed) ) & 1 > 0 join BaseTree b on b.uid_orgroot = x.uid_orgroot
54 join HardwareinBaseTree pio on b.uid_org = pio.uid_org and pio.XOrigin > 0 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-AllForOneHardware', @DBQueueElements_03
55 end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end 
56