dbo.QBM_FTColumnsToWatch

1create function dbo.QBM_FTColumnsToWatch(@TableName varchar(32) , @Operation varchar(16) ) returns @erg table ( TableName nvarchar(32) collate database_default
2 , ColumnName nvarchar(32) collate database_default , TableNameView nvarchar(32) collate database_default , ColumnNameView nvarchar(32) collate database_default
3 ) as begin declare @UeberhauptWas int = 0 if '' = dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog') begin goto ende end if not exists (select
4 top 1 1 from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where t.TableName = @TableName
5 and c.ColumnName = 'XObjectKey' ) begin goto ende end   insert into @erg (TableName, ColumnName, TableNameView, ColumnNameView) select bt.TableName, bc.ColumnName
6, t.TableName, c.ColumnName from DialogColumn bc with (readpast) join DialogTable bt with (readpast) on bc.UID_DialogTable = bt.UID_DialogTable and bt.TableName
7 = @TableName and bc.IsDeactivatedByPreProcessor = 0 and bt.IsDeactivatedByPreProcessor = 0 join DialogTable t with (readpast) on ( t.TableType = 'T' and
8 t.UID_DialogTable = bt.UID_DialogTable or t.TableType = 'V' and t.UID_DialogTableBase = bt.UID_DialogTable ) and t.IsDeactivatedByPreProcessor = 0 join
9 DialogColumn c with (readpast) on c.UID_DialogTable = t.UID_DialogTable and ( t.TableType = 'T' and c.ColumnName = bc.ColumnName or t.TableType = 'V' 
10and c.UID_BaseColumn = bc.UID_DialogColumn ) and c.IsDeactivatedByPreProcessor = 0 where ( (c.IsToWatch = 1 and @Operation in ('INSERT', 'UPDATE') and 
11t.UsageType in ('CONFIGURATION', 'USERDATA', 'MATERIAL') ) or (c.IsToWatchDelete = 1 and @Operation in ('DELETE') and ( t.UsageType in ('CONFIGURATION'
12, 'USERDATA', 'MATERIAL') or ( t.UsageType = 'HISTORY'  and left(t.UID_DialogTable, 3) <> 'QBM' ) ) ) ) select @UeberhauptWas = @@ROWCOUNT if @UeberhauptWas
13 > 0 begin insert into @erg (TableName, ColumnName, TableNameView, ColumnNameView) select bt.TableName, bc.ColumnName, t.TableName, c.ColumnName from DialogColumn
14 bc with (readpast) join DialogTable bt with (readpast) on bc.UID_DialogTable = bt.UID_DialogTable and bt.TableName = @TableName join DialogTable t with
15 (readpast) on ( t.TableType = 'T' and t.UID_DialogTable = bt.UID_DialogTable or t.TableType = 'V' and t.UID_DialogTableBase = bt.UID_DialogTable ) join
16 DialogColumn c with (readpast) on c.UID_DialogTable = t.UID_DialogTable and ( t.TableType = 'T' and c.ColumnName = bc.ColumnName or t.TableType = 'V' 
17and c.UID_BaseColumn = bc.UID_DialogColumn ) where c.IsAlternatePKMember = 1 and t.IsTransportDisabled = 0 and ( left(t.UID_DialogTable, 3) = 'QBM' and
18 '1' = dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog\AutoTrackAlternatePK') or left(t.UID_DialogTable, 3) <> 'QBM' and '1' = dbo.QBM_FGIConfigparmValue
19('Common\ProcessState\PropertyLog\AutoTrackAlternatePK\PayLoad') ) and Not exists (select top 1 1 from @erg e where e.ColumnName = c.ColumnName and e.TableName
20 = @TableName ) end  if @UeberhauptWas > 0 and @Operation = 'Delete' begin insert into @erg (TableName, ColumnName, TableNameView, ColumnNameView) select
21 bt.TableName, bc.ColumnName, t.TableName, c.ColumnName from DialogColumn bc with (readpast) join DialogTable bt with (readpast) on bc.UID_DialogTable 
22= bt.UID_DialogTable and bt.TableName = @TableName and bc.IsDeactivatedByPreProcessor = 0 and bt.IsDeactivatedByPreProcessor = 0 join DialogTable t with
23 (readpast) on ( t.TableType = 'T' and t.UID_DialogTable = bt.UID_DialogTable or t.TableType = 'V' and t.UID_DialogTableBase = bt.UID_DialogTable ) and
24 t.IsDeactivatedByPreProcessor = 0 join DialogColumn c with (readpast) on c.UID_DialogTable = t.UID_DialogTable and ( t.TableType = 'T' and c.ColumnName
25 = bc.ColumnName or t.TableType = 'V' and c.UID_BaseColumn = bc.UID_DialogColumn ) and c.IsDeactivatedByPreProcessor = 0 where c.IsPKMember = 1 and not
26 exists (Select top 1 1 from @erg e where e.TableName = bt.TableName and e.ColumnName = bc.ColumnName and e.TableNameView = t.TableName and e.ColumnNameView
27 = c.ColumnName ) end delete  @erg from @erg e where exists (select top 1 1 from DialogColumn cc with (readpast) join DialogTable t with (readpast) on 
28cc.UID_DialogTable = t.UID_DialogTable where e.TableName = t.TableName and e.ColumnName= cc.COLUMNNAME and cc.SchemaDataType in ('text', 'ntext', 'image'
29)  ) ende: return end 
30