dbo.RMS_TIESetHasEntitlement

1  create   trigger RMS_TIESetHasEntitlement on ESetHasEntitlement  for Insert not for Replication as begin declare @EsetOperation varchar(38) declare
2 @EsetTable varchar(30) declare @ElementLast int declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare
3 @SQLCmd nvarchar(max) declare @Entitlement varchar(138) , @ObjectkeyESet varchar(138) declare @Pattern nvarchar(max) declare @DebugSwitch int = 0 declare
4 @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 1) declare @DBQueueElements_spezial QBM_YDBQueueRaw declare @UID_TaskFollow
5 varchar(38) BEGIN TRY if exists (select top 1 1 from inserted) goto start return start: declare @GenProcID varchar(38) select @GenProcID = dbo.QBM_FGISessionContext
6('')   if exists (select top 1 1 from inserted i left outer join BaseTreeAssign a on  i.Entitlement like '<Key><T>' + dbo.QBM_FGITableName(a.UID_DialogTableElement
7) + '</T>%' and a.IsEsetEnabled = 1 where a.UID_BaseTreeAssign is null ) begin raiserror( '#LDS#Assignment cannot be made because the assigned Element is not permittet for assignments to system roles.|'
8, 18, 2) with nowait end declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null,
9 @GenProcID from ( select distinct i.UID_ESet as uid from inserted i  where i.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 0  ) as x exec QBM_PDBQueueInsert_Bulk
10 'RMS-K-ESetHasEntitlement', @DBQueueElements_01   delete  @ElementBufferMulti insert into @ElementBufferMulti(UID1, Ident1) select distinct v.UID_TaskBaseTree
11, t.TableName from inserted i join DialogTable t on  i.Entitlement like '<Key><T>' + t.TableName + '</T>%' join BaseTreeAssign v on t.UID_DialogTable =
12 v.UID_DialogTableElement where v.UID_TaskBaseTree > ' ' and i.XIsInEffect = 1 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount
13 +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @EsetOperation = bu.UID1 , @EsetTable = bu.Ident1 from @ElementBufferMulti
14 bu where bu.ElementIndex = @ElementIndex   delete  @DBQueueElements_spezial insert into @DBQueueElements_spezial (object, subobject, genprocid) select
15 x.uid, null, @GenProcID from ( select distinct ehe.uid_org as uid from inserted i join BaseTreehaseset ehe on i.uid_ESet = ehe.uid_ESet and ehe.XOrigin
16 > 0  where  i.Entitlement like '<Key><T>' + @EsetTable + '</T>%' ) as x exec QBM_PDBQueueInsert_Bulk @EsetOperation, @DBQueueElements_spezial select @ElementIndex
17 += 1 end   delete  @ElementBufferMulti insert into @ElementBufferMulti(UID1, Ident1) select distinct v.UID_TaskPerson, t.TableName from inserted i join
18 DialogTable t on  i.Entitlement like '<Key><T>' + t.TableName + '</T>%' join BaseTreeAssign v on t.UID_DialogTable = v.UID_DialogTableElement where v.UID_TaskPerson
19 > ' ' and i.XIsInEffect = 1 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while
20 @ElementIndex <= @ElementLast begin select top 1 @EsetOperation = bu.UID1 , @EsetTable = bu.Ident1 from @ElementBufferMulti bu where bu.ElementIndex =
21 @ElementIndex   delete  @DBQueueElements_spezial insert into @DBQueueElements_spezial (object, subobject, genprocid) select x.uid, null, @GenProcID from
22 ( select distinct ehe.UID_Person as uid from inserted i join PersonHasESet ehe on i.uid_ESet = ehe.uid_ESet and ehe.XOrigin > 0  where  i.Entitlement 
23like '<Key><T>' + @EsetTable + '</T>%' ) as x exec QBM_PDBQueueInsert_Bulk @EsetOperation, @DBQueueElements_spezial select @ElementIndex += 1 end   delete
24  @ElementBufferMulti insert into @ElementBufferMulti (UID1, Ident1) select distinct v.UID_TaskWorkDesk, t.TableName from inserted i join DialogTable t
25 on  i.Entitlement like '<Key><T>' + t.TableName + '</T>%' join BaseTreeAssign v on t.UID_DialogTable = v.UID_DialogTableElement where v.UID_TaskWorkDesk
26 > ' ' and i.XIsInEffect = 1 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while
27 @ElementIndex <= @ElementLast begin select top 1 @EsetOperation = bu.UID1 , @EsetTable = bu.Ident1 from @ElementBufferMulti bu where bu.ElementIndex =
28 @ElementIndex   delete  @DBQueueElements_spezial insert into @DBQueueElements_spezial (object, subobject, genprocid) select x.uid, null, @GenProcID from
29 ( select distinct ehe.UID_WorkDesk as uid from inserted i join WorkDeskHasESet ehe on i.uid_ESet = ehe.uid_ESet and ehe.XOrigin > 0  where  i.Entitlement
30 like '<Key><T>' + @EsetTable + '</T>%' ) as x exec QBM_PDBQueueInsert_Bulk @EsetOperation, @DBQueueElements_spezial select @ElementIndex += 1 end   select
31 @Pattern = '
32 if exists (select top 1 1
33				from @Tabelle@ x with (readpast)
34				where x.XObjectKey = ''@Entitlement@''
35				and dbo.QER_FGIITShopFlagCombineValid(
36														''@ObjectkeyESet@''
37														, @IsForITShop@
38														, @IsITShopOnly@
39														, ''@Entitlement@''
40														, @ElementIsForITShop@
41														, @ElementIsITShopOnly@
42													) = 0
43			)
44	begin
45		raiserror (''#LDS#Assignment is not permitted due to the combination of IT Shop flags.|'', 18, 2) with nowait
46	end
47	'
48  delete  @ElementBufferMulti insert into @ElementBufferMulti(ObjectKey1, ObjectKey2, ContentFull) select e.XObjectKey, i.Entitlement , replace(replace
49(replace(replace(replace(replace(replace(@Pattern , '@tabelle@', dbo.QBM_FCVObjectkeyToElement('TableName', i.Entitlement)) , '@Entitlement@', i.Entitlement
50) , '@ObjectkeyESet@', e.XObjectKey) , '@IsForITShop@', str(e.IsForITShop)) , '@IsITShopOnly@', str(e.IsITShopOnly)) , '@ElementIsForITShop@', case dbo.QBM_FGIColumnExists
51( dbo.QBM_FCVObjectkeyToElement('TableName', i.Entitlement), 'IsForITShop' ) when 1 then 'x.IsForITShop' else '0' end ) , '@ElementIsITShopOnly@', case
52 dbo.QBM_FGIColumnExists( dbo.QBM_FCVObjectkeyToElement('TableName', i.Entitlement), 'IsITShopOnly' ) when 1 then 'x.IsITShopOnly' else '0' end )  from
53 inserted i join ESet e on i.UID_ESet = e.UID_ESet select @ElementCount = @@ROWCOUNT if @ElementCount > 0 begin exec QBM_PExecuteSQLMulti @ElementBufferMulti
54 end declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select
55 d.UID_ESet as uid from inserted d  where d.XIsInEffect = 1 union select p.UID_ESet as uid from inserted d join Eset p on d.Entitlement = p.XObjectKey 
56where d.XIsInEffect = 1 union select ehe.uid_ESet from inserted d join ESet p on d.Entitlement = p.XObjectKey join EsetHasEntitlement ehe on ehe.Entitlement
57 = p.XObjectKey and ehe.XOrigin > 0  where d.XIsInEffect = 1 ) as x  select @UID_TaskFollow = 'RMS-K-ESetCollection' exec QBM_PDBQueueInsert_Bulk @UID_TaskFollow
58, @DBQueueElements_03  declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, @GenProcID
59 from ( select distinct ec.UID_ESet as uid from inserted d join ESetCollection ec on d.uid_ESet = ec.UID_EsetChild where d.XIsInEffect = 1 ) as x exec 
60QBM_PDBQueueInsert_Bulk 'RMS-K-ESetHasEntitlement', @DBQueueElements_02 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH
61 NOWAIT END CATCH ende: return end 
62