dbo.QER_TUPerson

1create   trigger QER_TUPerson on Person  for Update not for Replication as begin  declare @Parameter nvarchar(1000) declare @Elements QBM_YParameterList
2 declare @UID_Person varchar(38) declare @JournalMessage nvarchar(1000) declare @QBM_BitPatternXMarkedForDel_Delay int = dbo.QBM_FGIBitPatternXMarkedForDel
3('|Delay|', 0) declare @QBM_BitPatternXMarkedForDel_OutStanding int = dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 0) declare @PWOTaskToUse varchar
4(38) = dbo.QER_FGIPWOTaskToUse() declare @AbortWhereAddOn nvarchar(1000) = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'
5 declare @PWOToAbort QBM_YParameterList declare @DebugLevel varchar(1) = 'I' BEGIN TRY if exists (select top 1 1 from inserted) goto start if exists (select
6 top 1 1 from deleted) goto start return start: declare @GenProcID varchar(38) select @GenProcID = dbo.QBM_FGISessionContext('') if update(UID_PersonMasterIdentity
7) begin  if @PWOTaskToUse > ' ' begin  exec QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate', @PWOTaskToUse, '', @GenProcID end  if exists (select top
8 1 1 from inserted i where i.UID_PersonMasterIdentity > ' ' ) begin insert into @PWOToAbort(Parameter1) select x.XObjectKey from ( select pwo.XObjectKey
9 from inserted p join QER_VPersonsAreMe me on p.UID_Person = me.UID_PersonOrigin join QERUniversalSubstitute su on su.UID_PersonSender = me.UID_PersonOrigin
10 and su.UID_PersonReceiver = me.UID_PersonAlsoMe join PersonWantsOrg pwo on su.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg union select pwo.XObjectKey 
11from inserted p join QER_VPersonsAreMe me on p.UID_Person = me.UID_PersonOrigin join QERUniversalSubstitute su on su.UID_PersonReceiver = me.UID_PersonOrigin
12 and su.UID_PersonSender = me.UID_PersonAlsoMe join PersonWantsOrg pwo on su.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg union select pwo.XObjectKey from
13 inserted p join QER_VPersonsAreMe me on p.UID_PersonMasterIdentity = me.UID_PersonOrigin join QERUniversalSubstitute su on su.UID_PersonSender = me.UID_PersonOrigin
14 and su.UID_PersonReceiver = me.UID_PersonAlsoMe join PersonWantsOrg pwo on su.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg union select pwo.XObjectKey 
15from inserted p join QER_VPersonsAreMe me on p.UID_PersonMasterIdentity = me.UID_PersonOrigin join QERUniversalSubstitute su on su.UID_PersonReceiver =
16 me.UID_PersonOrigin and su.UID_PersonSender = me.UID_PersonAlsoMe join PersonWantsOrg pwo on su.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg ) as x if 
17@@ROWCOUNT > 0 begin  exec QBM_PJobCreate_HOCallMethod_L 'PersonWantsOrg', @pwoToAbort, 1, 'Abort' , @GenProcID , @AdditionalObjectKeysAffected = DEFAULT
18 , @param1 = '#LDS#Substitution no longer allowed because of definition of subidentities.|' , @Retries = 3 , @checkForExisting = 1 , @WhereClauseAdditional
19 = @AbortWhereAddOn end end  declare @DBQueueElements_updateMasterIdentity QBM_YDBQueueRaw insert into @DBQueueElements_updateMasterIdentity (object, subobject
20, genprocid) select x.uid, null, @GenProcID from ( select UID_PersonMasterIdentity as uid from inserted where UID_PersonMasterIdentity > ' ' union select
21 UID_PersonMasterIdentity as uid from deleted where UID_PersonMasterIdentity > ' ' union select p.UID_Person as uid from deleted d join Person p on d.UID_Person
22 = p.UID_Person where isnull(d.UID_PersonMasterIdentity, '') <> isnull(p.UID_PersonMasterIdentity,'') ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadPerson'
23, @DBQueueElements_updateMasterIdentity end   if update(uid_personHead) begin                   declare @DBQueueElements_01 QBM_YDBQueueRaw insert into
24 @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select uid_personHead as uid from inserted where uid_personHead
25 > ' ' union select uid_personHead as uid from deleted where uid_personHead > ' '  union select us.UID_PersonReceiver as uid from inserted i join QERUniversalSubstitute
26 us on i.UID_PersonHead = us.UID_PersonSender where i.UID_PersonHead > ' ' and us.IsCurrentlyActive = 1 and us.UseForHeadPerson = 1 union select us.UID_PersonReceiver
27 as uid from deleted d join QERUniversalSubstitute us on d.UID_PersonHead = us.UID_PersonSender where d.UID_PersonHead > ' ' and us.IsCurrentlyActive =
28 1 and us.UseForHeadPerson = 1 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadPerson', @DBQueueElements_01 end           if update(UID_PersonMasterIdentity
29) begin declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, @GenProcID from 
30( select UID_PersonMasterIdentity as uid from inserted where UID_PersonMasterIdentity > ' ' union select UID_PersonMasterIdentity as uid from deleted where
31 UID_PersonMasterIdentity > ' ' ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-PersonHasObject', @DBQueueElements_02 end                  if update(IsInActive
32) or update(IsExternal) or update(UID_PersonMasterIdentity) begin declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object, 
33subobject, genprocid) select x.uid, y.UID_DynamicGroup, @GenProcID from ( select p.uid_person as uid from deleted d join Person p on d.UID_Person = p.UID_Person
34 where ( isnull(d.IsInActive, 0) <> isnull(p.IsInActive, 0) or isnull(d.IsExternal, 0) <> isnull(p.IsExternal, 0) or isnull(d.UID_PersonMasterIdentity,
35 '') <> isnull(p.UID_PersonMasterIdentity, '') ) ) as x , (select g.UID_DynamicGroup from DynamicGroup g where dbo.QBM_FCVGUIDToModuleOwner(g.UID_DynamicGroup
36 ) <> 'CCC' and g.UID_DialogTableObjectClass = 'QER-T-Person' ) as y exec QBM_PDBQueueInsert_Bulk 'QER-K-DynamicGroupSingleBasic', @DBQueueElements_03 
37end  if dbo.QBM_FGIColumnUpdatedOthers ('Person', 'XTouched', columns_updated()) = 1 and (dbo.QBM_FGIColumnUpdatedOthers ('Person', 'XMarkedForDeletion'
38, columns_updated()) = 1 or exists ( select top 1 1 from deleted i join person p on i.uid_person = p.uid_person  where  sign(i.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay
39) <> sign(p.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay) ) ) begin   declare @maxAnzahl int select @maxAnzahl = 100 if 100 < (select count(*
40) from inserted) + (select count(*) from deleted) begin select top 1 @maxAnzahl = ( (Count(*)) /10 + 1) from person end else begin select @maxAnzahl = 
411073741824  end end if update(IsInActive) or update(IsTemporaryDeactivated) or update(XMarkedForDeletion) or update(IsSecurityIncident) or update(IsNoInherite
42) begin declare @DBQueueElements_05 QBM_YDBQueueRaw insert into @DBQueueElements_05 (object, subobject, genprocid) select x.uid, null, @GenProcID from 
43( select p.uid_person as uid from person p join deleted d on p.uid_person = d.uid_person where (   isnull(p.IsInActive,0) <> isnull(d.IsInActive,0) or 
44isnull(p.IsTemporaryDeactivated,0) <> isnull(d.IsTemporaryDeactivated,0) or isnull(p.IsSecurityIncident,0) <> isnull(d.IsSecurityIncident,0) or isnull(p.IsNoInherite
45,0) <> isnull(d.IsNoInherite,0)  or  sign(p.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay) <> sign(d.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay
46) ) ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-AllForOnePerson', @DBQueueElements_05 end if update(XMarkedForDeletion) begin declare @DBQueueElements_06
47 QBM_YDBQueueRaw insert into @DBQueueElements_06 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select p.uid_person as uid from person
48 p join deleted d on p.uid_person = d.uid_person where p.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding = 0 and d.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding
49 > 0 union  select us.UID_PersonReceiver as uid from Person p join deleted d on p.UID_Person = d.UID_Person join QERUniversalSubstitute us on p.UID_Person
50 = us.UID_PersonSender where p.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding = 0 and d.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding
51 > 0 and us.IsCurrentlyActive = 1 and (us.UseForHeadPerson = 1 or us.UseForHeadOrg = 1 ) ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-AllForOnePerson', @DBQueueElements_06
52 exec QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadPerson', @DBQueueElements_06 exec QBM_PDBQueueInsert_Bulk 'QER-K-HelperPersonOrg', @DBQueueElements_06 exec
53 QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadOrg', @DBQueueElements_06 end  if update(uid_department) or update(uid_ProfitCenter) or update(uid_locality) 
54begin declare @DBQueueElements_07 QBM_YDBQueueRaw insert into @DBQueueElements_07 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( 
55select p.uid_person as uid from person p join deleted d on p.uid_person = d.uid_person where (isnull(p.uid_department,'') <> isnull(d.uid_department,''
56) or isnull(p.uid_ProfitCenter,'') <> isnull(d.uid_ProfitCenter,'') or isnull(p.uid_locality,'') <> isnull(d.uid_locality,'') ) ) as x exec QBM_PDBQueueInsert_Bulk
57 'QER-K-HelperPersonOrg', @DBQueueElements_07 end  if update(Passcode) begin delete  @Elements insert into @Elements (Parameter1, ContentFull) select d.UID_Person
58, isnull(p.InternalName, '<no InternalName>') from deleted d join Person p on d.UID_Person = p.UID_Person where d.Passcode is null and p.Passcode > ' '
59 while exists (select top 1 1 from @Elements) begin select top 1 @UID_Person = e.Parameter1 , @JournalMessage = concat('#LDS#Passcode issued for identity: {0}, UID: {1}.|'
60 , e.ContentFull , '|' , e.Parameter1 , '|' ) from @Elements e exec QBM_PJournal @JournalMessage, @@ProcID, 'I', @DebugLevel delete  @elements where Parameter1
61 = @UID_Person end  end  pwotest:   if update(uid_department) begin declare @RecalcParameter_01 QBM_YCursorBuffer  insert into @RecalcParameter_01 (UID1
62, ObjectKey1, Ident1) select 'QER-PWODecisionRule-DM', i.XobjectKey, 'I' from inserted i union all select 'QER-PWODecisionRule-ID', i.XobjectKey, 'I' from
63 inserted i union all select 'QER-PWODecisionRule-RD', i.XobjectKey, 'I' from inserted i exec QER_PITShopHelperFill_Recalc @RecalcParameter_01, @GenprocID
64 end  if update(uid_ProfitCenter) begin declare @RecalcParameter_02 QBM_YCursorBuffer  insert into @RecalcParameter_02 (UID1, ObjectKey1, Ident1) select
65 'QER-PWODecisionRule-IP', i.XobjectKey, 'I' from inserted i union all select 'QER-PWODecisionRule-PM', i.XobjectKey, 'I' from inserted i union all select
66 'QER-PWODecisionRule-RP', i.XobjectKey, 'I' from inserted i exec QER_PITShopHelperFill_Recalc @RecalcParameter_02, @GenprocID end  if update(uid_Locality
67) begin declare @RecalcParameter_03 QBM_YCursorBuffer  insert into @RecalcParameter_03 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-IL', i.XobjectKey
68, 'I' from inserted i union all select 'QER-PWODecisionRule-RL', i.XobjectKey, 'I' from inserted i exec QER_PITShopHelperFill_Recalc @RecalcParameter_03
69, @GenprocID end  if update(IsInActive) begin if dbo.QBM_FGIConfigparmValue('QER\ITSHop\AutoCloseInactivePerson') > ' ' begin insert into @Elements(Parameter1
70) select pio.XObjectKey from Person p join deleted d on p.UID_Person = d.UID_Person and p.IsInActive = 1 and d.IsInActive = 0 join PersonInBaseTree pio
71 on pio.UID_Person = p.UID_Person join BaseTree b on pio.UID_Org = b.UID_Org left outer join DynamicGroup dg on b.XObjectKey = dg.ObjectKeyBaseTree where
72 b.ITShopInfo = 'CU' and b.UID_OrgRoot = 'QER-V-ITShopOrg' and dg.UID_DynamicGroup is null exec QBM_PJobCreate_HODelete_L 'PersonInITShopOrg', @elements
73, @GenProcID , DEFAULT end  declare @DBQueueElements_08 QBM_YDBQueueRaw insert into @DBQueueElements_08 (object, subobject, genprocid) select x.uid, null
74, @GenProcID from ( select distinct ph.UID_PersonWantsOrg as uid from deleted d join Person p on d.UID_Person = p.UID_Person join PWOHelperPWO ph on ph.UID_PersonHead
75 = p.UID_Person where d.IsInActive = 0 and p.IsInActive = 1 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPWOHelperPWO', @DBQueueElements_08  
76end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end 
77