dbo.QBM_FSQTriggerWatchXIsInEffect

1   create   function dbo.QBM_FSQTriggerWatchXIsInEffect(@TableName varchar(30) ) returns nvarchar(max)    as begin declare @erg nvarchar(max) = 
2'' select @erg = STRING_AGG(convert(nvarchar(max), concat(N'
3			declare @EntriesToFire_Assign', x.Zeile , ' QBM_YParameterList
4			insert into @EntriesToFire_Assign'
5, x.Zeile , '(Parameter1)
6				select x.XObjectKey
7				from deleted d join ' , @TableName , ' x on d.XObjectKey = x.XObjectKey
8				where x.XOrigin > 0
9					and d.XIsInEffect = 0
10					and x.XIsInEffect = 1
11					 '
12 + case when @TableName = x.TableNameWork then '-- kein XObjectkey-Vergleich nötig' else 'and x.XObjectKey like ''<Key><T>' + x.TableNameWork + '</T>%'''
13 end + '
14			if @@rowcount > 0
15			 begin
16				exec QBM_PJobCreate_HOFireEvent_L ''' , x.TableNameWork , ''', @EntriesToFire_Assign', x.Zeile , '
17						, @EventName = ''Assign''
18						, @GenProcID = @GenProcID
19						, @isToFreezeOnError = 1
20						, @WhereClauseAdditional = '' XIsInEffect = 1 '' 
21						, @AdditionalObjectKeysAffected = @AdditionalEntries
22			 end			
23
24			declare @EntriesToFire_Remove'
25, x.Zeile , ' QBM_YParameterList
26			insert into @EntriesToFire_Remove', x.Zeile , '(Parameter1)
27				select x.XObjectKey
28				from deleted d join ' , 
29@TableName , ' x on d.XObjectKey = x.XObjectKey
30				where x.XOrigin > 0
31					and d.XIsInEffect = 1
32					and x.XIsInEffect = 0
33					 ' + case when @TableName
34 = x.TableNameWork then '-- kein XObjectkey-Vergleich nötig' else 'and x.XObjectKey like ''<Key><T>' + x.TableNameWork + '</T>%''' end + '
35
36			if @@rowcount > 0
37			 begin
38				exec QBM_PJobCreate_HOFireEvent_L '''
39 , x.TableNameWork , ''', @EntriesToFire_Remove', x.Zeile , '
40						, @EventName = ''Remove''
41						, @GenProcID = @GenProcID
42						, @isToFreezeOnError = 1
43						, @WhereClauseAdditional = '' XIsInEffect = 0 '' 
44						, @AdditionalObjectKeysAffected = @AdditionalEntries
45			 end
46			'
47 )  )  , N'' )  from ( select y.TableNameWork, trim(str(ROW_NUMBER() over(order by y.tablenameWork))) as Zeile from ( select t.TableName as TableNameWork
48 from dbo.DialogTable t with (readpast) where t.TableName = @TableName and t.TableType = 'T'  and t.IsDeactivatedByPreProcessor = 0 and t.isAssignmentWithEvent
49 = 1 union select v.TableName from dbo.DialogTable v with (readpast) where v.UID_DialogTableBase in ( Select b.UID_DialogTable from dbo.DialogTable b with
50 (readpast) where b.TableName = @TableName and b.TableType = 'B' )  and v.IsDeactivatedByPreProcessor = 0 and v.isAssignmentWithEvent = 1 ) as y ) as x
51 if @erg > ' ' begin select @erg = concat( '
52---------------------------------------
53-- Fire Events for XIsInEffect
54---------------------------------------
55	if update(XIsInEffect)
56	 begin
57'
58 , @erg , '
59	 end -- if update(XIsInEffect)
60
61---------------------------------------
62-- / Fire Events for XIsInEffect
63---------------------------------------
64'
65 ) end else begin select @erg = '' end endLabel: return(@erg) end 
66