我们已通过以下方式解决了该问题。
select <list of tracked columns here> into #deleted from deleted;declare @TABLE_NAME sysname = '[table name]';declare f cursorlocalforward_onlyread_onlyfor 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 #resultsopen f;fetch next from f into @field_name, @field_name_sanitised;while @@fetch_status = 0begin 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相关阅读：
- COLUMNS_UPDATED
- 系统列
