dbo.QBM_FSQTriggerWatchColumn

1      create   function dbo.QBM_FSQTriggerWatchColumn(@TableName nvarchar(64), @operation nvarchar(16) ) returns nvarchar(max) as begin declare 
2@columnname varchar(30) declare @TableNameBase varchar(30) declare @realColumnname varchar(30)   declare @uid_dialogcolumn varchar(38) declare @tableview
3 varchar(30) declare @whereclause nvarchar(max) declare @IsAlreadyInsertClause bit = 0 declare @IsAlreadyInsertValue bit = 0 declare @columnname_alt varchar
4(30) = '' declare @erg nvarchar(max) = N'' declare @SchemaDatatype varchar(128) declare @OperationForColumns varchar(16) declare @elements table (ElementIndex
5 int identity NOT NULL , columnname varchar(30) collate database_default , uid_dialogcolumn varchar(38) collate database_default , tableview varchar(30
6) collate database_default , realColumnname varchar(30) collate database_default , TableNameBase varchar(30) collate database_default , SchemaDatatype 
7varchar(128) collate database_default ) declare @ElementCount int declare @ElementIndex int if @operation = 'UpdateDelete' begin select @OperationForColumns
8 = 'Delete' end else begin select @OperationForColumns = @operation end insert into @elements(columnname , uid_dialogcolumn , tableview , realColumnname
9 , TableNameBase , SchemaDatatype ) select wg.columnname , rtrim(c.uid_dialogColumn), wg.TableNameView, wg.ColumnNameView , isnull(bt.TableName, t.TableName
10) , c.SchemaDataType from dbo.QBM_FTColumnsToWatch(@TableName , @OperationForColumns) wg join dialogColumn c with (readpast) on wg.columnnameView = c.columnname
11 join DialogTable t with (readpast) on wg.TableNameView = t.TableName and c.UID_DialogTable = t.UID_DialogTable left outer join DialogTable bt with (readpast
12) on t.UID_DialogTableBase = bt.UID_DialogTable order by wg.ColumnName, wg.TableNameView select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while
13 @ElementIndex <= @ElementCount begin select @whereclause = N'' select top 1 @columnname = e.columnname , @uid_dialogcolumn = e.uid_dialogcolumn , @tableview
14 = e.tableview , @realColumnname = e.realColumnname , @TableNameBase = e.TableNameBase , @SchemaDatatype = e.SchemaDatatype from @elements e where e.ElementIndex
15 = @ElementIndex     if @operation = 'update' and @columnname <> @Columnname_alt and @ElementIndex > 1 begin select @erg = @erg + N'
16		 end -- if update('
17 + @columnname_alt + ')' end if @operation = 'update' and @columnname <> @Columnname_alt begin select @erg = @erg + N'
18		if update(' + @columnname + ')
19		 begin'
20 select @IsAlreadyInsertClause = 0 end if @IsAlreadyInsertClause = 0 begin select @erg = @erg + N'
21			insert into @DialogWatchProperty (UID_DialogColumn, ObjectKeyOfRow
22											   , ContentShort, HasContentFull, ContentFull, DisplayValue)'
23 select @IsAlreadyInsertClause = 1 select @IsAlreadyInsertValue = 0 end if @IsAlreadyInsertValue = 1 begin select @erg = @erg + N'
24			  union all' end
25 select @erg = @erg + N'
26				select ''' + @uid_dialogColumn + N''', d.XObjectKey,  -- ' + @realColumnname + N'
27	' select @IsAlreadyInsertValue = 1 select
28 @erg = @erg + dbo.QBM_FCVStringToIndent( dbo.QBM_FSQCVColumnToBuffer(@TableNameBase, @columnname, 'd'), 8) select @erg = @erg + N', ' + dbo.QBM_FSQTriggerWatchDisplay
29( @TableName , N'd') select @erg = @erg + N'
30					from deleted d ' if @operation in( 'update' , 'updateDelete') begin select @erg = @erg + N' join ' +
31 @TableName + N' t on ' + dbo.QBM_FSQTableJoin(@TableName , 'd', 't') end if @operation = 'UpdateDelete' begin select @whereclause = N'
32					where d.XOrigin > 0 and t.XOrigin = 0'
33 end   if @operation = 'update' begin select @whereclause = N'
34				where ' + dbo.QBM_FSQIsNullClauseCmp(@TableNameBase, @columnname, 'd') + ' <> ' + dbo.QBM_FSQIsNullClauseCmp
35(@TableNameBase, @columnname, 't')  if @SchemaDatatype like '%char%' begin select @whereclause = @whereclause + ' collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/ '
36 end  end   if @TableName <> @tableview begin if len(@whereclause) > 0 begin select @whereclause = @whereclause + N'
37						and' end else begin select 
38@whereclause = N'
39					where' end select @whereclause = @whereclause + N' d.XObjectKey like ''<Key><T>' + @tableview + '</T>%''' end select @erg = @erg
40 + @whereclause select @Columnname_alt = @columnname   if @operation = 'update' and @ElementIndex = @ElementCount begin select @erg = @erg + N'
41		 end -- if update('
42 + @columnname_alt + ')' end select @ElementIndex += 1 end  return (@erg) end 
43