dbo.QBM_FSQTriggerSplittedLookup

1          create   function dbo.QBM_FSQTriggerSplittedLookup (@Tablename varchar(30) , @Operation varchar(16) ) returns nvarchar(max) as begin declare
2 @erg nvarchar(max) = '' declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @SourceSynonym nvarchar(16) declare @Body nvarchar(max
3) = '' declare @Body_sub nvarchar(max) = '' insert into @ElementBuffer (UID1 , Ident1  , Bit1   , Ident3  , ContentFull  , LongIdent2  ) select c.UID_DialogColumn
4 , c.ColumnName, c.IsMultiValued , c.SplittedLookupSupport , dbo.QBM_FSQJoinsForSplittedLookup(t.UID_DialogTable) , isnull(ac.AdditionalColumn, '') from
5 DialogTable t with (readpast) join DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable  join sys.tables st with (readpast) on st.name
6 = t.TableName  outer apply ( select r.ChildColumn as AdditionalColumn from dbo.QBM_FCVStringToList(dbo.QBM_FCVFKDollarToRelationMVP(t.TableName, t.SplittedLookupSupport
7), char(7), 1, 0 ) sp join QBM_VQBMRelation r on sp.ParameterValue = r.UID_QBMRelation where sp.OrderNumber = 1 and r.ChildTable = @tablename and t.SplittedLookupSupport
8 like 'FK(%' ) as ac  where t.TableName = @Tablename and c.SplittedLookupSupport > ' ' select @ElementCount = @@ROWCOUNT if @ElementCount > 0 and @Operation
9 = 'insert' begin select @Body = string_agg ( convert(nvarchar(max) , concat( '
10declare @SplittedElements','_', bu.Ident1,' QBM_YParameterList 
11
12insert into @SplittedElements'
13,'_', bu.Ident1,' (Parameter1, Parameter2, Parameter3, HasContentFull, ', case bu.Bit1  when 1 then 'ContentFull' else 'ContentShort' end , ')
14 select '
15 , case when bu.ContentFull  > ' ' then 'p.UID_Person' else 'Null' end , ', ''', bu.UID1 , ''', s.XObjectKey, ', str(bu.Bit1 ) , ', s.', bu.Ident1 , '
16	from inserted s '
17, case when bu.ContentFull  > ' ' then bu.ContentFull  else '' end , '
18	where s.', bu.Ident1 , ' > '' ''
19
20exec QBM_PSplittedElementProcess @SplittedElements'
21,'_', bu.Ident1,', ''' , bu.Ident3  , '''
22' ) )  , N'' )  from @ElementBuffer bu end if @ElementCount > 0 and @Operation = 'update' begin select @SourceSynonym
23 = case @TableName when 'Person' then 'p' else 'w0' end select @Body = string_agg( convert(nvarchar(max) , concat('
24if update (', bu.Ident1 , ') ', case
25 when @TableName <> 'Person' and dbo.QBM_FGIColumnExists(@TableName, 'UID_Person') = 1 then ' or update(UID_Person)'  when @Tablename <> 'Person' and bu.LongIdent2
26 <> 'UID_Person' and bu.LongIdent2 > ' ' then ' or update('+ bu.LongIdent2 + ')'  else '' end , '
27 begin 
28	declare @SplittedElements','_', bu.Ident1,
29' QBM_YParameterList 
30
31	insert into @SplittedElements','_', bu.Ident1,' (Parameter1, Parameter2, Parameter3, HasContentFull, ', case bu.Bit1  when 1 
32then 'ContentFull' else 'ContentShort' end , ')
33		select ' , case when bu.ContentFull  > ' ' then 'p.UID_Person' else 'Null' end , ', ''', bu.UID1 , ''', '
34, @SourceSynonym ,'.XObjectKey, ', str(bu.Bit1 ) , ', ',@SourceSynonym,'.', bu.Ident1 , '
35		from deleted s ', case when bu.ContentFull  > ' ' then bu.ContentFull
36  else 'join ' + @TableName + ' w0 on  ' + dbo.QBM_FSQTableJoin(@Tablename , 's', 'w0') end , '
37		where isnull(s.', bu.Ident1 , ', '''') <> isnull(', 
38@SourceSynonym,'.', bu.Ident1 , ', '''')
39			', case when @TableName <> 'Person' and dbo.QBM_FGIColumnExists(@TableName, 'UID_Person') = 1 then ' or isnull(s.UID_Person, '''') <> isnull('
40 + @SourceSynonym + '.UID_Person, '''')'  when @Tablename <> 'Person' and bu.LongIdent2 <> 'UID_Person' and bu.LongIdent2 > ' ' then ' or isnull(s.'+ bu.LongIdent2
41 + ', '''') <> isnull(' + @SourceSynonym + '.'+ bu.LongIdent2 + ', '''')'  else '' end , '
42	exec QBM_PSplittedElementProcess @SplittedElements','_', bu.Ident1
43,', ''' , bu.Ident3 , '''
44
45 end
46
47' ) )  , N'' )  from @ElementBuffer bu end if @ElementCount > 0 and @Operation = 'delete' begin select @Body = '
48delete QBMSplittedLookup 
49	from QBMSplittedLookup s join deleted d on s.ObjectKeyOwner = d.XObjectKey
50'
51 end   if @Operation = 'update' begin select @SourceSynonym = case @TableName when 'Person' then 'p' else 'w0' end select @Body_sub = string_agg( convert
52(nvarchar(max) , concat('
53if update (UID_Person) 
54 begin 
55	declare @SplittedElements','_', roh.TableToSelectFrom,'_', roh.ColumnToSelect, ' QBM_YParameterList 
56
57	insert into @SplittedElements'
58,'_', roh.TableToSelectFrom,'_', roh.ColumnToSelect, ' (Parameter1, Parameter2, Parameter3, HasContentFull, ', case roh.IsMultiValued when 1 then 'ContentFull'
59 else 'ContentShort' end , ')
60		select p.UID_Person	,  ''', roh.UID_DialogColumn, ''', ', @SourceSynonym ,'.XObjectKey, ', str(roh.IsMultiValued ) , ', '
61,@SourceSynonym,'.', roh.ColumnToSelect , '
62		from ', roh.TableToSelectFrom ,' s ', replace(roh.joins , concat('left outer join ', @tablename) , concat
63( ' join  (select t.', roh.PKNameOfTable ,', t.UID_Person
64										from deleted d join ', @tablename, ' t on  d.', roh.PKNameOfTable ,' = t.', roh.PKNameOfTable
65 ,'
66										where ISNULL(d.UID_Person, '''' ) <> ISNULL(t.UID_Person, '''' )
67										) ' ) ) , '
68
69	exec QBM_PSplittedElementProcess @SplittedElements'
70,'_', roh.TableToSelectFrom,'_', roh.ColumnToSelect, ', ''' , roh.SplittedLookupSupport, '''
71
72 end
73
74' ) )  , N'' )  from ( select ti.*, dbo.QBM_FSQJoinsForSplittedLookup
75(ti.UID_DialogTable) as joins, dbo.QBM_FGIPrimaryKeyName(@tablename , 1) as PKNameOfTable from (  select t.TableName as TableToSelectFrom, c.ColumnName
76 as ColumnToSelect, c.UID_DialogColumn, c.IsMultiValued, c.SplittedLookupSupport , t.SplittedLookupSupport as TableSplittedLookupSupport, t.UID_DialogTable
77 from DialogTable t join DialogColumn c on c.UID_DialogTable = t.UID_DialogTable where t.SplittedLookupSupport like 'FK(%' and c.SplittedLookupSupport 
78> ' ' and dbo.QBM_FGIColumnExists(t.TableName, 'XObjectKey') = 1 ) as ti where exists (select top 1 1 from dbo.QBM_FCVStringToList(dbo.QBM_FCVFKDollarToRelationMVP
79(ti.TableToSelectFrom, ti.TableSplittedLookupSupport), char(7), 1, 0 ) sp join QBM_VQBMRelation r on sp.ParameterValue = r.UID_QBMRelation where sp.OrderNumberReverse
80 = 1 and r.ChildTable = @tablename ) and dbo.QBM_FGIColumnExists(@TableName, 'UID_Person') = 1 ) as roh end if @Body_sub > ' ' begin select @Body = concat