我们已通过以下方式解决了该问题。
select <list of tracked columns here> into #deleted from deleted;
declare /*const*/ @TABLE_NAME sysname = '[table name]';
declare f cursor
local
forward_only
read_only
for
select c.name, quotename(c.name, '[')
from
sys.columns c
inner join sys.types t on c.system_type_id = t.system_type_id
where
c.object_id = object_id(@TABLE_NAME)
and c.is_computed = 0
and c.is_identity = 0
and t.name not in ('text', 'image', 'timestamp', 'xml')
and (substring(COLUMNS_UPDATED(), ((c.column_id - 1) / 8) + 1, 1) & power(2, (c.column_id - 1) % 8)) > 0
;
declare @field_name sysname, @field_name_sanitised sysname;
create table #results (row_id int not null, field_name sysname not null, oldval nvarchar(150) null, newval nvarchar(150) null);
-- For each changed field, insert what exactly changed into #results
open f;
fetch next from f into @field_name, @field_name_sanitised;
while @@fetch_status = 0
begin
declare @query nvarchar(4000);
set @query = N'insert into #results(row_id, field_name, oldval, newval)
select d.row_id, @field_name, d.' + @field_name_sanitised + N', i.' + @field_name_sanitised + N'
from
#deleted d inner join ' + @TABLE_NAME + N' i on d.row_id = i.row_id
where
(d.' + @field_name_sanitised + N' <> i.' + @field_name_sanitised + N')
or
(case when d.' + @field_name_sanitised + N' is null then 1 else 0 end <> case when i.' + @field_name_sanitised + N' is null then 1 else 0 end);'
;
exec sp_executesql
@stmt = @query,
@params = N'@field_name sysname',
@field_name = @field_name
;
fetch next from f into @field_name, @field_name_sanitised;
end;
close f;
deallocate f;
-- Do something meaningful to #results here
相关阅读：
602392714
清零编程群