如何创建通用SQL Server存储过程，以根据触发器中的插入和删除 *** 作执行插入审计表的 *** 作

如何创建通用SQL Server存储过程，以根据触发器中的插入和删除 *** 作执行插入审计表的 *** 作

我们已通过以下方式解决了该问题。

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
• 系统列