dbo.QER_ZDynamicGroupMakeTrigger

1     create   procedure QER_ZDynamicGroupMakeTrigger (@SlotNumberDummy int , @parm1dummy varchar(38) , @parm2dummy varchar(38) , @GenProcIDdummy
2 varchar(38) ) as begin declare @crlf varchar(2) = char(13) + char(10) declare @ElementCount int declare @ElementIndex int declare @TableName varchar(30
3) declare @Body nvarchar(max) declare @Triggername varchar(30) declare @SQLCmd nvarchar(max) declare @DebugSwitch int = 0  declare @Material table (ElementIndex
4 int identity primary key , TableName varchar(30) collate database_default , TriggerInsert nvarchar(max) collate database_default , TriggerUpdate nvarchar
5(max) collate database_default , TriggerDelete nvarchar(max) collate database_default ) BEGIN TRY exec QBM_PTriggerDrop 'GEN_T[I,U,D]DYG%' insert into 
6@Material(TableName, TriggerInsert, TriggerUpdate, TriggerDelete) select z.TableName, STRING_AGG(replace(z.WatchInsertDelete, '%opSynonym%', 'inserted'
7), @crlf) within group (order by z.Columnname) , STRING_AGG(z.Watchupdate, @crlf) within group (order by z.Columnname) , STRING_AGG(replace(z.WatchInsertDelete
8, '%opSynonym%', 'deleted'), @crlf) within group (order by z.Columnname) from ( select case when y.UID_DialogTable <> y.UID_DialogTableObjectClass and 
9y.IsPKMember = 0 and y.JoinColumns > ' ' then concat(convert(nvarchar(max), '--	detail with join'), @crlf , 'if update(', y.ColumnName , ')', @crlf , ' begin'
10, @crlf ,'	insert into @ElementAndRule(Parameter1, Parameter2)',@crlf ,'	select ot.', oc.PKName1 ,' , ''', y.UID_DynamicGroup , ''' --', dyg.DisplayName
11, @crlf ,'	from ', oc.TableName,' ot join inserted wt on ot.', oc.PKName1,' in (', y.JoinColumns,' )', @crlf , '	 union all ' , @crlf  ,'	select ot.', 
12oc.PKName1 ,' , ''', y.UID_DynamicGroup , '''', @crlf ,'	from ', oc.TableName,' ot join deleted wt on ot.', oc.PKName1,' in (', y.JoinColumns,' )', @crlf
13 , ' end', @crlf ) when y.UID_DialogTable <> y.UID_DialogTableObjectClass and y.IsPKMember = 0 and y.JoinColumns is null then concat(convert(nvarchar(max
14), '--	full'), @crlf , 'if update(', y.ColumnName , ')', @crlf , ' begin', @crlf , '	insert into @RuleOnly(Parameter1) select  ''', y.UID_DynamicGroup 
15, ''' --', dyg.DisplayName , @crlf , ' end', @crlf ) when y.UID_DialogTable <> y.UID_DialogTableObjectClass and y.IsPKMember = 1 then null  when y.UID_DialogTable
16 = y.UID_DialogTableObjectClass and y.IsPKMember = 0 then concat(convert(nvarchar(max), '--	detail without join'), @crlf , 'if update(', y.ColumnName ,
17 ')', @crlf , ' begin', @crlf , '	insert into @ElementAndRule(Parameter1, Parameter2) ', @crlf ,'	select wt.', oc.PKName1 ,' , ''', y.UID_DynamicGroup 
18, ''' --', dyg.DisplayName, @crlf ,'	from inserted wt ', @crlf , '	 union all ' , @crlf  ,'	select wt.', oc.PKName1 ,' , ''', y.UID_DynamicGroup , ''''
19, @crlf ,'	from deleted wt ', @crlf , ' end', @crlf ) else null end as WatchUpdate ,case   when y.UID_DialogTable <> y.UID_DialogTableObjectClass and y.JoinColumns
20 > ' ' then concat(convert(nvarchar(max), '--	detail with join') , @crlf , 'insert into @ElementAndRule(Parameter1, Parameter2) select ot.', oc.PKName1
21 ,' , ''', y.UID_DynamicGroup , ''' --', dyg.DisplayName, @crlf ,' from ', oc.TableName,' ot join %opSynonym% wt on ot.', oc.PKName1,' in (', y.JoinColumns
22,' )', @crlf ) when y.UID_DialogTable <> y.UID_DialogTableObjectClass and y.JoinColumns is null then concat(convert(nvarchar(max), '--	full'), @crlf , 
23'insert into @RuleOnly(Parameter1) select  ''', y.UID_DynamicGroup , ''' --', dyg.DisplayName , @crlf ) when y.UID_DialogTable = y.UID_DialogTableObjectClass
24 then concat(convert(nvarchar(max), '--	detail myself'), @crlf , 'insert into @ElementAndRule(Parameter1, Parameter2) select wt.', oc.PKName1 ,' , ''',
25 y.UID_DynamicGroup , ''' --', dyg.DisplayName, @crlf ,' from %opSynonym% wt ', @crlf ) end as WatchInsertDelete , y.* from ( select x.*, STRING_AGG( 'wt.'
26 + r.ChildColumn, ',') as JoinColumns from ( select dg.UID_DynamicGroup , dg.UID_DialogTableObjectClass, isnull(tb.TableName, t.TableName) as TableName
27, isnull(cb.ColumnName, c.ColumnName) as ColumnName, isnull(tb.UID_DialogTable, t.UID_DialogTable) as UID_DialogTable , c.IsPKMember from DynamicGroupHasImmediateColumn
28 gc join DynamicGroup dg on gc.UID_DynamicGroup = dg.UID_DynamicGroup join DialogColumn c on gc.ObjectKeyDialogColumn = c.XObjectKey join DialogTable t
29 with (readpast) on c.UID_DialogTable = t.UID_DialogTable  left outer join DialogColumn cb on c.UID_BaseColumn = cb.UID_DialogColumn left outer join DialogTable
30 tb with (readpast) on t.UID_DialogTableBase = tb.UID_DialogTable where dg.IsCalculateImmediately = 1 and gc.IsInActive = 0 and t.TableType not in ('P'
31, 'U', 'R') and ( dbo.QBM_FGIConfigparmValue('QER\Structures\DynamicGroupCheck\CalculateImmediately' + dbo.QBM_FGITableName(dg.UID_DialogTableObjectClass
32)) = '1' or dbo.QBM_FCVGUIDToModuleOwner(dg.UID_DynamicGroup) <> 'CCC'  ) ) as x left outer join QBM_VQBMRelation r on r.UID_DialogTableParent <> x.UID_DialogTable
33 and r.UID_DialogTableParent = x.UID_DialogTableObjectClass and r.UID_DialogTableChild = x.UID_DialogTable group by x.UID_DynamicGroup, x.UID_DialogTableObjectClass
34, x.TableName, x.ColumnName, x.UID_DialogTable, x.IsPKMember ) as y join DialogTable oc with (readpast)  on y.UID_DialogTableObjectClass = oc.UID_DialogTable
35 join DynamicGroup dyg on y.UID_DynamicGroup = dyg.UID_DynamicGroup ) as z group by z.TableName select @ElementCount = @@ROWCOUNT declare @Vars nvarchar
36(max) = '	
37	declare @RuleOnly QBM_YParameterList	-- Parameter1 die UID_DynamicGroup
38	declare @ElementAndRule QBM_YParameterList	-- Parameter1 UIDPerson(Hardware / Workdesk)
39												-- Parameter2 die UID_DynamicGroup
40
41'
42 declare @PostProcessing nvarchar(max) = '	
43
44	-- postprocessing for all
45
46	if exists (select top 1 1
47				from @ElementAndRule
48			)
49	 begin
50		declare @DBQueueElements_detail QBM_YDBQueueRaw
51	
52		insert into @DBQueueElements_detail (Object, SubObject, GenProcID)
53		select distinct x.Parameter1, x.Parameter2, @GenProcid
54		from @ElementAndRule x
55
56		if @@ROWCOUNT < 10000
57		 begin
58			exec QBM_PDBQueueInsert_Bulk ''QER-K-DynamicGroupSingleBasic'', @DBQueueElements_detail
59		 end
60		else
61		 begin
62			insert into @RuleOnly(Parameter1)
63			select distinct x.Parameter2
64			from @ElementAndRule x
65		 end
66	 end
67
68	if exists (select top 1 1
69				from @RuleOnly
70			)
71	 begin
72		declare @DBQueueElements_full QBM_YDBQueueRaw
73	
74		insert into @DBQueueElements_full (Object, SubObject, GenProcID)
75		select distinct x.Parameter1, null, @GenProcid
76		from @RuleOnly x
77
78		exec QBM_PDBQueueInsert_Bulk ''QER-K-DynamicGroupTest'', @DBQueueElements_full
79	 end
80'