如何创建通用SQL Server存储过程，以根据触发器中的插入和删除操作执行插入审计表的操作 [英] How To Create Generic SQL Server Stored Procedure To Perform Inserts Into Audit Table Based on Inserted and Deleted In Trigger

问题描述

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

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.

我的插入和删除审核触发器非常简单.但是，更新触发器要复杂得多，因为触发器必须检查以确定每个列是否都在审核控制之下，然后根据插入"和已删除"列中的列值是否相等执行插入操作，因为我不想写不必要的审核记录.最终，我想知道是否有一种编写存储过程的方法，该方法允许我动态执行下面的insert语句，从而减少触发器中的代码量.基本上，我预想触发器将使用受审计控制的每个列名来触发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

• COLUMNS_UPDATED
• sys.columns

这篇关于如何创建通用SQL Server存储过程，以根据触发器中的插入和删除操作执行插入审计表的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！