dbo.RMS_TUESet

1create   trigger RMS_TUESet on ESet  for UPDATE not for Replication as begin  declare @ObjectkeyOrdered varchar(138) declare @uid_accproduct varchar
2(38) declare @whereclauseOrg nvarchar(max) declare @whereclauseMuster nvarchar(max) = ' UID_ITShopOrg in ( select UID_OrgPR 
3							from QER_VPWOProductNodesSlim
4							where ObjectkeyOrdered = ''@ObjectkeyOrdered'' 
5								and isnull(UID_AccProduct, '''') <> ''@UID_AccProduct''
6					)
7'
8 declare @ElementLast int declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int BEGIN TRY if exists (select
9 top 1 1 from inserted) goto start if exists (select top 1 1 from deleted) goto start return start: declare @GenProcID varchar(38) select @GenProcID = 
10dbo.QBM_FGISessionContext('')  declare @vgl QBM_YParameterList   declare @TableName varchar(30) declare @sqlcmd nvarchar(max) = '' if update(isForITShop
11) or update(isITShopOnly) begin declare @FlagTest QBM_YParameterlist insert into @FlagTest(Parameter1, Parameter2, HasContentFull, e.Parameter3) select
12 i.UID_ESet, i.XObjectKey, i.IsForITShop, dbo.QBM_FCVBigIntToString(i.IsITShopOnly) from inserted i exec QER_PIsForITShopFlagCheck 'ESet', @FlagTest  if
13 exists (select top 1 1 from inserted i join ESetHasEntitlement ehe on i.XObjectKey = ehe.Entitlement and ehe.XOrigin > 0  join ESet e on ehe.uid_ESet 
14= e.uid_Eset  and e.IsITShopOnly = 0 where i.IsForITShop = 1 and i.IsITShopOnly = 1 ) begin  raiserror( '#LDS#Changes cannot take place, because assignments to system roles still exist that may not be used exclusively in IT Shop.|'
15, 18, 2) with nowait end     select @sqlcmd = STRING_AGG(convert(nvarchar(max), N'
16		select ehe.uid_ESet, ehe.Entitlement -- , elem.IsForITShop , elem.IsITShopOnly
17			from ESetHasEntitlement ehe join '
18 + t.TableName + ' elem on ehe.Entitlement = elem.XObjectKey 
19																	and ehe.XOrigin > 0 -- ohne XIsInEffect-Test, könnte ja jederzeit wieder angehen
20			where elem.IsForITShop = 1
21			 and elem.IsITShopOnly = 1
22		'
23 )  , concat(nCHAR(13) , NCHAR(10) , NCHAR(9) , 'union all') )  from DialogValidDynamicRef vr join DialogTable t on vr.UID_DialogTableReference = t.UID_DialogTable
24 where vr.UID_DialogColumn in (select c.UID_DialogColumn from DialogColumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable where c.ColumnName
25 = 'Entitlement' and t.TableName = 'EsetHasEntitlement' ) and exists (select top 1 1 from DialogColumn cr where cr.UID_DialogTable = t.UID_DialogTable 
26and cr.ColumnName = 'IsForITShop' )  insert into @vgl (Parameter1  , Parameter2  )  exec sp_executesql @SQLcmd  if exists (select top 1 1 from inserted
27 i join @vgl as x on x.Parameter1  = i.uid_Eset where  i.IsITShopOnly = 0    ) begin  raiserror( '#LDS#Changes cannot take place because products are assigned to the EntitlementSet that are only valid in IT Shop.|'
28, 18, 2) with nowait end end   if update(IsInactive) begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject
29, genprocid) select x.uid, null, @GenProcID from ( select co.uid_Eset as uid from ESet es join deleted d on es.uid_ESet = d.uid_ESet join ESetCollection
30 co on co.UID_EsetChild = es.uid_ESet where es.isinactive <> d.isInactive  union select es.uid_ESet from ESet es join deleted d on es.uid_ESet = d.uid_ESet
31 where es.isinactive <> d.isInactive  union select ehe.uid_ESet from ESet es join deleted d on es.uid_ESet = d.uid_ESet join ESetHasEntitlement ehe on 
32ehe.entitlement = es.XObjectKey and ehe.XOrigin > 0  where es.isinactive <> d.isInactive ) as x  exec QBM_PDBQueueInsert_Bulk 'RMS-K-ESetCollection', @DBQueueElements_01
33  declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select
34 he.UID_Org as uid from deleted d join ESet e on d.uid_ESet = e.uid_ESet join BaseTreeHasESet he on e.UID_ESet = he.UID_ESet where e.IsInActive <> d.IsInActive
35 ) as x exec QBM_PDBQueueInsert_Bulk 'RMS-K-OrgHasESet', @DBQueueElements_02 declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03
36 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select he.UID_Person as uid from deleted d join ESet e on d.uid_ESet = e.uid_ESet
37 join PersonHasESet he on e.UID_ESet = he.UID_ESet where e.IsInActive <> d.IsInActive ) as x exec QBM_PDBQueueInsert_Bulk 'RMS-K-PersonHasESet', @DBQueueElements_03
38 declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select
39 he.UID_WorkDesk as uid from deleted d join ESet e on d.uid_ESet = e.uid_ESet join WorkDeskHasESet he on e.UID_ESet = he.UID_ESet where e.IsInActive <>
40 d.IsInActive ) as x exec QBM_PDBQueueInsert_Bulk 'RMS-K-WorkdeskHasESet', @DBQueueElements_04  end  if update(uid_accproduct) begin delete  @ElementBufferMulti
41 insert into @ElementBufferMulti(ObjectKey1, UID1) select x.XObjectKey, isnull(x.uid_accproduct,'') from ESet x join deleted d on x.uid_ESet = d.uid_ESet
42 and isnull(x.uid_accproduct,'') <> isnull(d.uid_accproduct,'')  and d.uid_accproduct > ' '  join dialogColumn c on c.UID_DialogTable = 'QER-T-ITShopOrg'
43 and c.columnname = 'uid_ACCProduct' and c.IsDeactivatedByPreProcessor = 0  select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount
44 +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @ObjectkeyOrdered = bu.ObjectKey1 , @UID_AccProduct = bu.UID1
45 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex select @whereclauseOrg = @whereclauseMuster select @whereclauseOrg = replace(@whereclauseOrg
46, N'@ObjectkeyOrdered' , @ObjectkeyOrdered) select @whereclauseOrg = replace(@whereclauseOrg, N'@uid_accproduct' , rtrim(@uid_accproduct))    exec QBM_PJobCreate_HOUpdate_B
47 'ITShopOrg', @whereclauseOrg, @GenProcID , @p1 = 'uid_ACCProduct', @v1 = @uid_accproduct , @AdditionalObjectKeysAffected = DEFAULT select @ElementIndex
48 += 1 end  end  if update (UID_AccProduct) begin declare @RecalcParameter_OA QBM_YCursorBuffer insert into @RecalcParameter_OA (UID1, ObjectKey1, Ident1
49) select 'QER-PWODecisionRule-OA', a.XObjectKey, 'I' from inserted i join ESet g on i.UID_ESet = g.UID_ESet join QERAccProductUsage u on g.XObjectKey =
50 u.XObjectKey join AccProduct a on u.UID_AccProduct = a.UID_AccProduct union select 'QER-PWODecisionRule-OA', a.XObjectKey, 'I' from deleted d join ESet
51 g on d.UID_ESet = g.UID_ESet join QERAccProductUsage u on g.XObjectKey = u.XObjectKey join AccProduct a on u.UID_AccProduct = a.UID_AccProduct exec QER_PITShopHelperFill_Recalc
52 @RecalcParameter_OA, @GenprocID declare @RecalcParameter_OA2 QBM_YCursorBuffer insert into @RecalcParameter_OA2 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-OA'
53, pwo.XObjectKey, 'I' from inserted i join ESet g on i.UID_ESet = g.UID_ESet join AccProduct a on i.UID_AccProduct = a.UID_AccProduct join PersonWantsOrg
54 pwo on g.XObjectKey = pwo.ObjectKeyElementUsedInAssign union select 'QER-PWODecisionRule-OA', pwo.XObjectKey, 'I' from deleted d join ESet g on d.UID_ESet
55 = g.UID_ESet join AccProduct a on d.UID_AccProduct = a.UID_AccProduct join PersonWantsOrg pwo on g.XObjectKey = pwo.ObjectKeyElementUsedInAssign exec 
56QER_PITShopHelperFill_Recalc @RecalcParameter_OA2, @GenprocID end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT
57 END CATCH ende: return end 
58