如何创建通用 SQL Server 存储过程以基于触发器中的插入和删除执行插入到审计表中

2021-11-30 00:00:00 sql sql-server stored-procedures triggers audit

我根据以下帖子的第一个答案提供的信息实施了一个审计跟踪框架:

I have implemented an audit trail framework based on the information provided by the first answer to the following post:

SQL Server 历史表 - 通过 SP 或触发器填充?

最终，我实现的框架对每个表使用三个触发器，根据表的更改插入审计信息.

Ultimately, the framework that I have implemented uses three triggers per table that insert audit information based on changes to the tables.

我的插入和删除审核触发器相当简单.但是，更新触发器要复杂得多，因为触发器必须检查以确定每列是否受审计控制，然后根据 Inserted 和 Deleted 列中的列值是否相等来执行插入我不想写不必要的审计记录.最后，我想知道是否有一种方法可以编写一个存储过程，通过允许我动态执行下面的插入语句来减少触发器中的代码量.基本上，我设想触发器使用受审计控制的每个列名触发 sproc，然后存储过程将使用列名来执行下面的代码片段.目前，我在审计控制下的每一列都有以下代码，不幸的是，这会导致大量冗余代码.

My insert and delete audit triggers are fairly simple. However, the update triggers are far more complex because the trigger has to check to determine whether or not each column is under audit control and then perform an insert based on whether or not the column values in the Inserted and Deleted columns are equal or not since I don't want to write unnecessary audit records. Ultimately, I want to know if there is a way to write a stored procedure that will reduce the amount of code in my trigger by allowing me to dynamically perform the insert statement below. Basically, I envision the trigger firing the sproc with each column name that is under audit control and then the stored procedure will used the column name to perform the code snippet below. Currently, I have the code below for every column under audit control which unfortunately results in lots of redundant code.

在建议更改后修订触发器

Revised Trigger After Suggested Changes

CREATE TRIGGER [dbo].[Audit_Customers_Update] ON [dbo].[Customers]
FOR UPDATE AS

select FirstName,LastName 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

我如何访问#results?我必须使用游标吗?

How do I access #results? Do I have to use a cursor?

推荐答案

我们已经通过以下方式解决了这个问题.

We've solved that problem in the following way.

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

相关阅读:

  • COLUMNS_UPDATED
  • sys.columns

相关文章