dbo.ADS_TUAdsGroup

1create   trigger ADS_TUAdsGroup on AdsGroup  for Update not for Replication as begin  declare @Parameter nvarchar(256) declare @LoadLimit int = 
275000 declare @LoadSumme int declare @GroupsChanged QBM_YParameterList declare @DebugSwitch int = 0 declare @ObjectkeyOrdered varchar(138) declare @UID_AccProduct
3 varchar(38) declare @whereclauseOrg nvarchar(max) declare @whereclauseMuster nvarchar(max) = ' UID_ITShopOrg in ( select UID_OrgPR 
4							from QER_VPWOProductNodesSlim
5							where ObjectkeyOrdered = ''@ObjectkeyOrdered'' 
6								and isnull(UID_AccProduct, '''') <> ''@UID_AccProduct''
7					)
8'
9 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @QBM_BitPatternXMarkedForDel_Out int = dbo.QBM_FGIBitPatternXMarkedForDel
10('|OutStanding|', 0)  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
11 @GenProcID varchar(38) select @GenProcID = dbo.QBM_FGISessionContext('') if update(HasReadOnlyMemberships) begin if exists (select top 1 1 from deleted
12 d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships = 0 and g.HasReadOnlyMemberships = 1 join ADSGroupExclusion bhg on g.uid_ADSGroup
13 = bhg.uid_ADSGroupLower ) or exists (select top 1 1 from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships = 0
14 and g.HasReadOnlyMemberships = 1 join ADSGroupExclusion bhg on g.uid_ADSGroup = bhg.uid_ADSGroupHigher ) begin if dbo.QBM_FGISessionContext ('Fullsync'
15) > ' ' begin delete ADSGroupExclusion from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships = 0 and g.HasReadOnlyMemberships
16 = 1 join ADSGroupExclusion bhg on g.uid_ADSGroup in( bhg.uid_ADSGroupLower, bhg.UID_ADSGroupHigher) end else begin raiserror( '#LDS#Changes can not be made because there are still assignments in group exclusion.|'
17, 18, 2) with nowait end end  if exists (select top 1 1 from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships 
18= 0 and g.HasReadOnlyMemberships = 1 join BaseTreeHasADSGroup bhg on g.uid_ADSGroup = bhg.uid_ADSGroup and bhg.XOrigin > 0  ) begin if dbo.QBM_FGISessionContext
19 ('Fullsync') > ' ' begin update BaseTreeHasADSGroup set XOrigin = 0 , XIsInEffect = 0 from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup
20 and d.HasReadOnlyMemberships = 0 and g.HasReadOnlyMemberships = 1 join BaseTreeHasADSGroup bhg on g.uid_ADSGroup = bhg.uid_ADSGroup end else begin raiserror
21( '#LDS#Changes cannot be made because assignment still exist for {0}.|BaseTree|', 18, 1) with nowait end end if exists (select top 1 1 from deleted d 
22join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships = 0 and g.HasReadOnlyMemberships = 1 join ADSAccountInADSGroup bhg on g.uid_ADSGroup
23 = bhg.uid_ADSGroup and bhg.XOrigin > 0  ) begin if dbo.QBM_FGISessionContext ('Fullsync') > ' ' begin update ADSAccountInADSGroup set XOrigin = 0 , XIsInEffect
24 = 0 from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships = 0 and g.HasReadOnlyMemberships = 1 join ADSAccountInADSGroup
25 bhg on g.uid_ADSGroup = bhg.uid_ADSGroup end else begin raiserror( '#LDS#Changes cannot be made because assignment still exist for {0}.|ADSAccount|', 
2618, 1) with nowait end end if exists (select top 1 1 from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships = 0
27 and g.HasReadOnlyMemberships = 1 join ADSContactInADSGroup bhg on g.uid_ADSGroup = bhg.uid_ADSGroup and bhg.XOrigin > 0  ) begin if dbo.QBM_FGISessionContext
28 ('Fullsync') > ' ' begin update ADSContactInADSGroup set XOrigin = 0 , XIsInEffect = 0 from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup
29 and d.HasReadOnlyMemberships = 0 and g.HasReadOnlyMemberships = 1 join ADSContactInADSGroup bhg on g.uid_ADSGroup = bhg.uid_ADSGroup end else begin raiserror
30( '#LDS#Changes cannot be made because assignment still exist for {0}.|ADSContact|', 18, 1) with nowait end end if exists (select top 1 1 from deleted 
31d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships = 0 and g.HasReadOnlyMemberships = 1 join ADSGroupInADSGroup bhg on g.uid_ADSGroup
32 = bhg.UID_ADSGroupParent ) begin if dbo.QBM_FGISessionContext ('Fullsync') > ' ' begin delete ADSGroupInADSGroup from deleted d join ADSGroup g on d.uid_ADSGroup
33 = g.uid_ADSGroup and d.HasReadOnlyMemberships = 0 and g.HasReadOnlyMemberships = 1 join ADSGroupInADSGroup bhg on g.uid_ADSGroup = bhg.UID_ADSGroupParent
34 end else begin raiserror( '#LDS#Changes cannot be made because assignment still exist for {0}.|ADSGroupInADSGroup|', 18, 1) with nowait end end if exists
35 (select top 1 1 from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships = 0 and g.HasReadOnlyMemberships = 1 join
36 ADSMachineInADSGroup bhg on g.uid_ADSGroup = bhg.uid_ADSGroup and bhg.XOrigin > 0  ) begin if dbo.QBM_FGISessionContext ('Fullsync') > ' ' begin update
37 ADSMachineInADSGroup set XOrigin = 0 , XIsInEffect = 0 from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships 
38= 0 and g.HasReadOnlyMemberships = 1 join ADSMachineInADSGroup bhg on g.uid_ADSGroup = bhg.uid_ADSGroup end else begin raiserror( '#LDS#Changes cannot be made because assignment still exist for {0}.|ADSMachine|'
39, 18, 1) with nowait end end if exists (select top 1 1 from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup and d.HasReadOnlyMemberships =
40 0 and g.HasReadOnlyMemberships = 1 join EsetHasEntitlement bhg on g.XObjectKey = bhg.Entitlement and bhg.XOrigin > 0  ) begin if dbo.QBM_FGISessionContext
41 ('Fullsync') > ' ' begin update EsetHasEntitlement set XOrigin = 0 , XIsInEffect = 0 from deleted d join ADSGroup g on d.uid_ADSGroup = g.uid_ADSGroup
42 and d.HasReadOnlyMemberships = 0 and g.HasReadOnlyMemberships = 1 join EsetHasEntitlement bhg on g.XObjectKey = bhg.Entitlement end else begin raiserror
43( '#LDS#Changes cannot be made because assignment still exist for {0}.|Eset|', 18, 1) with nowait end end end   if update (isITShopOnly) begin if exists
44 (select top 1 1 from deleted d join ADSGroup g on d.UID_ADSGroup = g.UID_ADSGroup where g.IsITshopOnly = 1 and exists (select top 1 1 from ADSAccount 
45u where u.UID_ADSGroupPrimary = g.UID_ADSGroup ) ) begin raiserror( '#LDS#The definition of a group as "IsITshopOnly = 1" and simultaneous use as primary group is not allowed.|'
46, 18, 2) with nowait end end if update(ObjectSID) begin  if exists (select top 1 1 from ADSGroup x join deleted d on x.uid_ADSGroup= d.uid_ADSGroup where
47 isnull(d.ObjectSID, N'') <> isnull(x.ObjectSID, N'') ) begin insert into ADSOtherSID (UID_ADSOtherSID, ObjectSID , DistinguishedName, canonicalName, Ident_ADSOtherSID
48 , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey) select x.UID_ADSOtherSID, x.ObjectSID , x.DistinguishedName, x.canonicalName, 
49x.Ident_ADSOtherSID , x.XDateInserted, x.XDateUpdated, x.XUserInserted, x.XUserUpdated, dbo.QBM_FCVElementToObjectKey1('ADSOtherSID', 'UID_ADSOtherSID'
50, x.UID_ADSOtherSID) from ( select newid() as UID_ADSOtherSID, d.ObjectSID , d.DistinguishedName, d.canonicalName, d.cn as Ident_ADSOtherSID , d.XDateInserted
51, d.XDateUpdated, d.XUserInserted, d.XUserUpdated from ADSGroup x join deleted d on x.uid_ADSGroup= d.uid_ADSGroup where d.ObjectSID > ' ' and isnull(d.ObjectSID
52, N'') <> isnull(x.ObjectSID, N'') and not exists (select top 1 1 from ADSOtherSID x where x.ObjectSID = d.ObjectSID ) ) as x end   if exists (select top
53 1 1 from ADSOtherSID a join inserted d on a.ObjectSID = d.ObjectSID ) begin delete ADSOtherSID where objectSID in (select x.ObjectSID from ADSGroup x 
54join deleted d on x.uid_ADSGroup = d.uid_ADSGroup where isnull(x.ObjectSID, N'') <> isnull(d.ObjectSID, N'') ) and UID_ADSOtherSID not like 'ADS-%' end
55  end  if update(isForITShop) or update(isITShopOnly) begin declare @FlagTest QBM_YParameterlist insert into @FlagTest(Parameter1, Parameter2, HasContentFull
56, e.Parameter3) select i.UID_AdsGroup, i.XObjectKey, i.IsForITShop, dbo.QBM_FCVBigIntToString(i.IsITShopOnly) from inserted i exec QER_PIsForITShopFlagCheck
57 'AdsGroup', @FlagTest, 'ADSAccountInADSGroup|ADSMachineInADSGroup' end  if update(uid_accproduct) begin insert into @elementbuffer( ObjectKey1, uid1) 
58select x.XObjectKey, isnull(x.uid_accproduct,'') from ADSGroup x join deleted d on x.uid_ADSGroup = d.uid_ADSGroup and isnull(x.uid_accproduct,'') <> isnull
59(d.uid_accproduct,'')  and d.uid_accproduct > ' '  join dialogColumn c on c.UID_DialogTable = 'QER-T-ITShopOrg' and c.columnname = 'uid_ACCProduct' and
60 c.IsDeactivatedByPreProcessor = 0  select @ElementCount = @@rowcount select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 
61@ObjectkeyOrdered = bu.Objectkey1 , @UID_AccProduct = bu.uid1 from @elementbuffer bu where bu.ElementIndex = @ElementIndex select @whereclauseOrg = @whereclauseMuster
62 select @whereclauseOrg = replace(@whereclauseOrg, N'@ObjectkeyOrdered' , @ObjectkeyOrdered) select @whereclauseOrg = replace(@whereclauseOrg, N'@uid_accproduct'
63 , rtrim(@uid_accproduct))    exec QBM_PJobCreate_HOUpdate_B N'ITShopOrg', @whereclauseOrg, @GenProcID , @p1 = 'uid_ACCProduct', @v1 = @uid_accproduct 
64, @AdditionalObjectKeysAffected = DEFAULT select @ElementIndex += 1 end   end  if update (UID_AccProduct) begin declare @RecalcParameter_OA QBM_YCursorBuffer
65 insert into @RecalcParameter_OA (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-OA', a.XObjectKey, 'I' from inserted i join ADSGroup g on i.UID_ADSGroup
66 = g.UID_ADSGroup join QERAccProductUsage u on g.XObjectKey = u.XObjectKey join AccProduct a on u.UID_AccProduct = a.UID_AccProduct union select 'QER-PWODecisionRule-OA'
67, a.XObjectKey, 'I' from deleted d join ADSGroup g on d.UID_ADSGroup = g.UID_ADSGroup join QERAccProductUsage u on g.XObjectKey = u.XObjectKey join AccProduct
68 a on u.UID_AccProduct = a.UID_AccProduct exec QER_PITShopHelperFill_Recalc @RecalcParameter_OA, @GenprocID declare @RecalcParameter_OA2 QBM_YCursorBuffer
69 insert into @RecalcParameter_OA2 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-OA', pwo.XObjectKey, 'I' from inserted i join ADSGroup g on i.UID_ADSGroup
70 = g.UID_ADSGroup join AccProduct a on i.UID_AccProduct = a.UID_AccProduct join PersonWantsOrg pwo on g.XObjectKey = pwo.ObjectKeyElementUsedInAssign union
71 select 'QER-PWODecisionRule-OA', pwo.XObjectKey, 'I' from deleted d join ADSGroup g on d.UID_ADSGroup = g.UID_ADSGroup join AccProduct a on d.UID_AccProduct
72 = a.UID_AccProduct join PersonWantsOrg pwo on g.XObjectKey = pwo.ObjectKeyElementUsedInAssign exec QER_PITShopHelperFill_Recalc @RecalcParameter_OA2, 
73@GenprocID end  if update(islocal) or update(isuniversal)  or update(isGlobal) or update(isSecurity) or update(IsDistributionGroup)  or update(MatchPatternForMembership
74)  or update(XMarkedForDeletion) begin insert into @GroupsChanged (Parameter1, Parameter2) select g.uid_ADSGroup as uid, g.XObjectKey from ADSGroup g join
75 deleted d on g.UID_ADSGroup = d.UID_ADSGroup where ( g.islocal <> d.islocal or g.isGlobal <> d.isGlobal or g.isuniversal <> d.isuniversal or g.isSecurity
76 <> d.isSecurity or g.IsDistributionGroup <> d.IsDistributionGroup or g.MatchPatternForMembership <> d.MatchPatternForMembership or (g.XMarkedForDeletion
77 & @QBM_BitPatternXMarkedForDel_Out = 0 and d.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out > 0 ) ) if @DebugSwitch > 0 begin print 'Groups ' select
78 Parameter1, parameter2, parameter3 from @GroupsChanged end if (select count(*) from @GroupsChanged) > 0 begin    declare @DBQueueElements_01 QBM_YDBQueueRaw
79 insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from (  select distinct top (@LoadLimit) aig.uid_ADSAccount
80 as uid from @GroupsChanged g join adsAccountInADSGroup aig on g.Parameter1 = aig.uid_ADSGroup and aig.XOrigin > 0  ) as x select @LoadSumme = @@ROWCOUNT