如何在触发器中插入和删除的基础上创建通用的SQL Server存储过程来执行对审计表的插入

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

我已根据以下职位的第一个答案所提供的资料，实施了一个审计跟踪框架:

SQL Server history table - populate through SP or Trigger?

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.

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

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.

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

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

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

如何访问#结果?我需要使用光标吗?

2 个解决方案

#1

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

#2

Ran into a similar problem... figured it out this way... may not be the most elegant solution but works for the compliance guys... So here goes...

遇到了类似的问题……我是这么想的……可能不是最优雅的解决方案，但对合规人员有效……这里是……

Based on the solution given here

基于这里给出的解

The xml is extracted with FOR XML from the trigger that updated the table... The "OldValues" come from the DELETED table and the "NewValues" from the INSERTED table... so the final xml looks like this...

xml是从更新表的触发器中提取的xml。“OldValues”来自删除表，“NewValues”来自插入表…最终的xml是这样的。

            DECLARE @x XML= '<FieldData>
              <UpdatedColumns>
                <trType>OldValues</trType>
                <ID>5</ID>
                <def_label>TEST_TIE</def_label>
                <def_code />
              </UpdatedColumns>
              <UpdatedColumns>
                <trType>OldValues</trType>
                <ID>4</ID>
                <def_label>RP_TIE</def_label>
                <def_code />
              </UpdatedColumns>
              <UpdatedColumns>
                <trType>OldValues</trType>
                <ID>3</ID>
                <def_label>ERR_TIE</def_label>
                <def_code />
              </UpdatedColumns><UpdatedColumns>
                <trType>NewValues</trType>
                <ID>5</ID>
                <def_label>TEST_TIE</def_label>
                <def_code>A</def_code>
              </UpdatedColumns>
              <UpdatedColumns>
                <trType>NewValues</trType>
                <ID>4</ID>
                <def_label>RP_TIE</def_label>
                <def_code>A</def_code>
              </UpdatedColumns>
              <UpdatedColumns>
                <trType>NewValues</trType>
                <ID>3</ID>
                <def_label>ERR_TIE</def_label>
                <def_code>A</def_code>
              </UpdatedColumns>
            </FieldData>'

            declare @timestamp datetime2= SYSDATETIME()

            select 
                     ID = identity(int,1,1), 
                     T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
                     T.N.value('../ID[1]','nvarchar(100)') AS table_ID,
                     T.N.value('.', 'nvarchar(100)') as OldValue
            INTO #old
            from @x.nodes('//UpdatedColumns/*') as T(N)
            WHERE T.N.value('../trType[1]', 'nvarchar(100)') ='OldValues'


            select 
                     ID = identity(int,1,1),
                     T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
                     T.N.value('../ID[1]','nvarchar(100)') AS Table_ID,
                     T.N.value('.', 'nvarchar(100)') as NewValue
            into #new
            from @x.nodes('//UpdatedColumns/*') as T(N)
            WHERE T.N.value('../trType[1]', 'nvarchar(100)') ='NewValues'



            SELECT n.table_ID, n.NodeName, o.OldValue, n.NewValue,@timestamp as transation_time FROM #new n
            left outer JOIN #old o ON n.NodeName = o.NodeName AND n.ID = o.ID 
            WHERE isnull(o.[OldValue],'') <> isnull(n.[newValue],'') AND n.NodeName <> 'trType'



            DROP TABLE #new,#old 
            GO

#1