在SQL Server中创建审计触发器

I need to implement change tracking on two tables in my SQL Server 2005 database. I need to audit additions, deletions, updates (with detail on what was updated). I was planning on using a trigger to do this, but after poking around on Google I found that it was incredibly easy to do this incorrectly, and I wanted to avoid that on the get-go.

我需要在SQL Server 2005数据库中的两个表上实现更改跟踪。我需要审计添加、删除、更新(以及更新内容的细节)。我本打算用一个触发器来做这件事，但在谷歌上翻了一翻之后，我发现不正确地做这件事非常容易，我想在一开始就避免这样做。

Can anybody post an example of an update trigger that accomplishes this successfully and in an elegant manner? I am hoping to end up with an audit table with the following structure:

有没有人可以发布一个更新触发器的例子，以一种优雅的方式成功地完成这个任务?我希望以以下结构的审计表作为结束:

• ID
• ID
• LogDate
• LogDate
• TableName
• 的表
• TransactionType (update/insert/delete)
• TransactionType(插入/更新/删除)
• RecordID
• RecordID
• FieldName
• 字段名
• OldValue
• OldValue
• NewValue
• NewValue

... but I am open for suggestions.

…但我愿意接受建议。

Thanks!

谢谢!

10 个解决方案

#1

I just want to call out couple of points:

我想说几点:

Use code generators You can't have a single procedure to track all tables, you will need to generate similar but distinct triggers on each tracked table. This kind of job is best suited for automated code generation. In your place I would use an XSLT transformation to generate the code from XML, and the XML can be generated automatically from metadata. This allows you to easily maintain the triggers by regenerating them each time you make a change to the audit logic/structure or a target table is added/altered.

使用代码生成器不能使用单个过程来跟踪所有表，您需要在每个被跟踪的表上生成类似但不同的触发器。这种工作最适合自动生成代码。在这里，我将使用XSLT转换从XML生成代码，XML可以从元数据自动生成。这允许您在每次更改审计逻辑/结构或添加/更改目标表时重新生成触发器，从而方便地维护触发器。

Consider capacity planning for the audit. An audit table that tracks all value changes will be, by far, the biggest table in the database: it will contain all the current data and all the history of the current data. Such a table will increase the database size by 2-3 orders of magnitude (x10, x100). And the audit table will quickly become the bottleneck of everything:

考虑审计的容量规划。跟踪所有值更改的审计表将是数据库中最大的表:它将包含所有当前数据和当前数据的所有历史。这样的表将使数据库大小增加2-3个数量级(x10、x100)。而审核表将迅速成为一切的瓶颈:

• every DML operation will require locks in the audit table
• 每个DML操作都需要审计表中的锁
• all administrative and maintenance operations will have to accommodate the size of the database due to audit
• 所有的管理和维护操作都必须适应由于审计而导致的数据库的大小。

Take into account the schema changes. A table named 'Foo' may be dropped and later a different table named 'Foo' may be created. The audit trail has to be able to distinguish the two different objects. Better use a slow changing dimension approach.

考虑到模式的变化。可以删除一个名为“Foo”的表，然后创建另一个名为“Foo”的表。审计跟踪必须能够区分这两个不同的对象。最好使用缓慢变化的维度方法。

Consider the need to efficiently delete audit records. When the retention period dictated by your application subject policies is due, you need to be able to delete the due audit records. It may not seem such a big deal now, but 5 years later when the first records are due the audit table has grown to 9.5TB it may be a problem.

考虑需要有效地删除审计记录。当您的应用程序主题策略规定的保留期到期时，您需要能够删除应有的审计记录。现在看来这似乎不是什么大问题，但5年后当第一批记录到期时，审计表已经增长到9.5TB，这可能是个问题。

Consider the need to query the audit. The audit table structure has to be prepared to respond efficiently to the queries on audit. If your audit cannot be queried then it has no value. The queries will be entirely driven by your requirements and only you know those, but most audit records are queried for time intervals ('what changes occurred between 7pm and 8pm yesterday?'), by object ('what changes occurred to this record in this table?') or by author ('what changes did Bob in the database?').

考虑查询审计的需要。审计表结构必须准备好有效地响应审计查询。如果你的审计不能被查询，那么它就没有价值。查询将完全由您的需求,只有你知道,但大多数审计记录查询时间间隔(“什么变化发生昨天7点到8点之间?”),通过对象(“发生什么变化,这个记录在这张桌子吗?”)和作者(“鲍勃在数据库中什么变化?”)。

#2

We are using ApexSQL Audit that generates audit triggers and below are data structures used by this tool. If you don’t plan on buying a 3rd party solution you can install this tool in trial mode, see how they implemented triggers and storage and then create something similar for yourself.

我们使用ApexSQL审计生成审计触发器，下面是该工具使用的数据结构。如果您不打算购买第三方解决方案，您可以在试用模式中安装此工具，看看它们是如何实现触发器和存储的，然后为您自己创建类似的东西。

I didn’t bother getting into too many details on how these tables work but hopefully this will get you started.

关于这些表格是如何工作的，我没有过多的细节，但希望这能让你们开始。

#3

There is no generic way to do it the way you want. Ultimately you end up writing reams of code for each table. Not to mention it can be fairy slow if you need to compare each column for change.

没有什么通用的方法可以按照你想要的方式来做。最终，您将为每个表编写大量的代码。更不用说，如果您需要比较每一列以进行更改，那么它可能会很慢。

Also the fact that you might be updating multiple rows at the same time implies you need to open a cursor to loop through all the records.

另外，您可能同时更新多个行，这意味着您需要打开游标来循环所有记录。

The way I'd do it will be using table with structure identical to the tables you are tracking and unpivot it later to show which columns have actually changed. I'd also keep track of the session that actually did the change. This assumes that you have primary key in the table being tracked.

我使用的方法是使用结构与您跟踪的表相同的表，并在稍后取消数据透视，以显示哪些列实际上发生了更改。我也会记录下实际改变的会话。这假定您在被跟踪的表中有主键。

So given a table like this

给定一个这样的表

CREATE TABLE TestTable  
(ID INT NOT NULL CONSTRAINT PK_TEST_TABLE PRIMARY KEY,
Name1 NVARCHAR(40) NOT NULL,  
Name2 NVARCHAR(40))

I'd create an audit table like this in the audit schmea.

我将在audit schmea中创建一个这样的审计表。

CREATE TABLE Audit.TestTable  
(SessionID UNIQUEIDENTIFER NOT NULL,  
ID INT NOT NULL,
Name1  NVARCHAR(40) NOT NULL,  
Name2  NVARCHAR(40),  
Action NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTION CHECK(Action In 'Deleted','Updated'),  
RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK (RowType in 'New','Old','Deleted'),  
ChangedDate DATETIME NOT NULL Default GETDATE(),  
ChangedBy SYSNHAME NOT NULL DEFAULT USER_NAME())

And a trigger for Update like this

像这样的更新的触发器

CREATE Trigger UpdateTestTable ON DBO.TestTable FOR UPDATE AS  
BEGIN  
    SET NOCOUNT ON
    DECLARE @SessionID UNIQUEIDENTIFER
    SET @SessionID = NEWID()
    INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
    SELECT ID,name1,Name2,'Updated','Old',@SessionID FROM Deleted

    INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
    SELECT ID,name1,Name2,'Updated','New',@SessionID FROM Inserted

END

This runs quite fast. During reporting , you simply join the rows based on sessionID, and Primary key and produce a report. Alternatively you can have a batch job that periodically goes through all the tables in the audit table and prepare a name-value pair showing the changes.

这跑的非常快。在报告期间，您只需根据sessionID和主键加入行并生成报告。或者，您也可以有一个批处理作业，它定期遍历审计表中的所有表，并准备一个名称-值对来显示更改。

HTH

HTH

#4

Mike, we are using www.auditdatabase.com tool, this free tool generates audit triggers and it works well with SQL Server 2008 and 2005 and 2000. Its a sophisticated and madure tool that allows custom audit triggers for a table.

Mike，我们正在使用www.auditdatabase.com工具，这个免费工具可以生成审计触发器，它可以很好地与SQL Server 2008、2005和2000配合使用。它是一种复杂的工具，允许为表定制审计触发器。

Another excellent tool is Apex SQL Audit

另一个优秀的工具是Apex SQL审计

#5

I'll throw in my approach and suggestions to the mix.

我将介绍我的方法和建议。

I have a very similar table to your proposed design that I have used for the last seven years on a SQL 2005 (now 2008) database.

我有一个与您建议的设计非常相似的表，我在SQL 2005(现在是2008)数据库上使用了七年。

I added insert, update and delete triggers to selected tables, and then checked for changes to selected fields. At the time it was simple and works well.

我向选定的表添加了插入、更新和删除触发器，然后检查对选定字段的更改。当时它很简单，效果很好。

Here are the issues I find with this approach:

以下是我在这种方法中发现的问题:

1. The audit table old/new value fields had to be varchar(MAX) types to be able to handle all the different values that could be audited: int,bool,decimal,float,varchar, etc. all have to fit

   审计表旧值/新值字段必须是varchar(MAX)类型，才能处理所有可审计的不同值:int、bool、decimal、float、varchar等等

2. The code to check for each field is tedious to write an maintain. It's also easy to miss things (like changing a null field to a value didn't get caught, because NULL != value is NULL.

   对每个字段进行检查的代码编写维护非常繁琐。也很容易忽略一些事情(比如将一个空字段更改为一个值没有被捕获，因为null != value是null。

3. Delete record: how do you record this? All fields? Selected ones? It gets complicated

   删除记录:您如何记录这个?所有字段?选择的吗?它变得复杂

My future vision is to use some SQL-CLR code and write a generic trigger that is executed and checks table meta-data to see what to audit. Secondly, the New/Old values will be converted to XML fields and the whole object recorded: this results in more data but a delete has a whole record. There are several articles on the web about XML audit triggers.

我未来的愿景是使用一些SQL-CLR代码并编写一个通用触发器，该触发器将被执行，并检查表元数据以查看要审计的内容。其次，新/旧值将转换为XML字段，并记录整个对象:这将产生更多的数据，但删除具有完整的记录。web上有几篇关于XML审计触发器的文章。

#6

CREATE TRIGGER TriggerName 
ON TableName 
FOR INSERT, UPDATE, DELETE AS 
BEGIN
 SET NOCOUNT ON

 DECLARE @ExecStr varchar(50), @Qry nvarchar(255)

 CREATE TABLE #inputbuffer 
 (
  EventType nvarchar(30), 
  Parameters int, 
  EventInfo nvarchar(255)
 )

 SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'

 INSERT INTO #inputbuffer 
 EXEC (@ExecStr)

 SET @Qry = (SELECT EventInfo FROM #inputbuffer)

 SELECT @Qry AS 'Query that fired the trigger', 
 SYSTEM_USER as LoginName, 
 USER AS UserName, 
 CURRENT_TIMESTAMP AS CurrentTime
END

#7

Trigger is used to If you modify or insert in a particular table this will execute, and you can check the particular column in trigger. Full Example with explanation is in the following website. http://www.allinworld99.blogspot.com/2015/04/triggers-in-sql.html

如果您在特定的表中修改或插入这个将执行，那么您就可以在触发器中检查特定的列。完整的例子和解释在以下的网站。http://www.allinworld99.blogspot.com/2015/04/triggers-in-sql.html

#8

I finally found a universal solution, that does not require dynamic sql and logs changes of all columns.

我最终找到了一个通用的解决方案，它不需要动态sql并记录所有列的更改。

Its not needed to change the trigger if the table changes.

如果表发生更改，则不需要更改触发器。

This is the audit log:

这是审计日志:

CREATE TABLE [dbo].[Audit](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Type] [char](1) COLLATE Latin1_General_CI_AS NULL,
    [TableName] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL,
    [PK] [int] NULL,
    [FieldName] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL,
    [OldValue] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
    [NewValue] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
    [UpdateDate] [datetime] NULL,
    [Username] [nvarchar](8) COLLATE Latin1_General_CI_AS NULL,
 CONSTRAINT [PK_AuditB] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

This is the trigger for one table:

这是一张表的触发器:

INSERT INTO ILSe.dbo.Audit ([Type], TableName, PK, FieldName, OldValue, NewValue, Username)
      SELECT 
            CASE  WHEN NOT EXISTS (SELECT ID FROM deleted WHERE ID = ISNULL(ins.PK,del.PK)) THEN 'I' 
                WHEN NOT EXISTS (SELECT ID FROM inserted WHERE ID = ISNULL(ins.PK,del.PK)) THEN 'D' 
                  ELSE 'U' END as [Type],
            'AGB' as TableName, 
            ISNULL(ins.PK,del.PK) as PK,
            ISNULL(ins.FieldName,del.FieldName) as FieldName,
            del.FieldValue as OldValue,
            ins.FieldValue as NewValue,
            ISNULL(ins.Username,del.Username) as Username 
FROM (SELECT
      insRowTbl.PK,
      insRowTbl.Username,
      attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
      attr.insRow.value('.', 'nvarchar(max)') as FieldValue
  FROM (Select
            i.ID as PK,
            i.LastModifiedBy as Username,
            convert(xml, (select i.* for xml raw)) as insRowCol
        from inserted as i
       ) as insRowTbl
       CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow)
  ) as ins
FULL OUTER JOIN (SELECT
      delRowTbl.PK,
      delRowTbl.Username,
      attr.delRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
      attr.delRow.value('.', 'nvarchar(max)') as FieldValue
  FROM (Select      
               d.ID as PK,
               d.LastModifiedBy as Username,
               convert(xml, (select d.* for xml raw)) as delRowCol
         from deleted as d
         ) as delRowTbl
        CROSS APPLY delRowTbl.delRowCol.nodes('/row/@*') as attr(delRow)
      ) as del
            on ins.PK = del.PK and ins.FieldName = del.FieldName
 WHERE 
      isnull(ins.FieldName,del.FieldName) not in ('LastModifiedBy', 'ID', 'TimeStamp') 
 and  ((ins.FieldValue is null and del.FieldValue is not null) 
      or (ins.FieldValue is not null and del.FieldValue is null) 
      or (ins.FieldValue != del.FieldValue))

This trigger is for one Table named AGB. The Table with the name AGB has a primary Key Column with the name ID and a Column with the Name LastModifiedBy which contains the username that made the last edit.

这个触发器用于一个名为AGB的表。名为AGB的表有一个主键列，其中有name ID和一个名为LastModifiedBy的列，其中包含了上次编辑的用户名。

The trigger consists of two parts, first it converts columns of inserted and deleted tables into rows. This is explained in detail here: https://*.com/a/43799776/4160788

触发器由两个部分组成，首先它将插入和删除的表的列转换为行。这里有详细的解释:https://*.com/a/43799776/4160788。

Then it joins the rows (one row per column) of the inserted and deleted tables by primary key and field name, and logs a line for each changed column. It does NOT log changes of ID, TimeStamp or LastModifiedByColumn.

然后，通过主键和字段名连接插入和删除表的行(每行一行)，并为每个已更改的列记录一行。它不记录ID、时间戳或LastModifiedByColumn的更改。

You can insert your own TableName, Columns names.

可以插入自己的表名，列名。

You can also create the following stored procedure, and then call this stored procedure to generate your triggers:

您还可以创建以下存储过程，然后调用此存储过程来生成触发器:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_create_audit_trigger]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[_create_audit_trigger] AS' 
END
ALTER PROCEDURE [dbo].[_create_audit_trigger]
     @TableName varchar(max),
     @IDColumnName varchar(max) = 'ID',
     @LastModifiedByColumnName varchar(max) = 'LastModifiedBy',
     @TimeStampColumnName varchar(max) = 'TimeStamp'
AS
BEGIN  

PRINT 'start ' + @TableName + ' (' + @IDColumnName + ', ' + @LastModifiedByColumnName + ', ' + @TimeStampColumnName + ')'

/* if you have other audit trigger on this table and want to disable all triggers, enable this: 
EXEC ('ALTER TABLE ' + @TableName + ' DISABLE TRIGGER ALL')*/

IF EXISTS (SELECT * FROM sys.objects WHERE [type] = 'TR' AND [name] = 'tr_audit_'+@TableName)
    EXEC ('DROP TRIGGER [dbo].tr_audit_'+@TableName)


EXEC ('
CREATE TRIGGER [dbo].[tr_audit_'+@TableName+'] ON [ILSe].[dbo].['+@TableName+'] FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

      INSERT INTO ILSe.dbo.Audit ([Type], TableName, PK, FieldName, OldValue, NewValue, Username)
      SELECT CASE  WHEN NOT EXISTS (SELECT '+@IDColumnName+' FROM deleted WHERE '+@IDColumnName+' = ISNULL(ins.PK,del.PK)) THEN ''I'' WHEN NOT EXISTS (SELECT '+@IDColumnName+' FROM inserted WHERE '+@IDColumnName+' = ISNULL(ins.PK,del.PK)) THEN ''D'' ELSE ''U'' END as [Type],
        '''+@TableName+''' as TableName, ISNULL(ins.PK,del.PK) as PK, ISNULL(ins.FieldName,del.FieldName) as FieldName, del.FieldValue as OldValue, ins.FieldValue as NewValue, ISNULL(ins.Username,del.Username) as Username FROM 
      (SELECT insRowTbl.PK, insRowTbl.Username, attr.insRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName, attr.insRow.value(''.'', ''nvarchar(max)'') as FieldValue FROM (Select      
                  i.'+@IDColumnName+' as PK,
                  i.'+@LastModifiedByColumnName+' as Username,
                  convert(xml, (select i.* for xml raw)) as insRowCol
                from inserted as i) as insRowTbl
                CROSS APPLY insRowTbl.insRowCol.nodes(''/row/@*'') as attr(insRow)) as ins
            FULL OUTER JOIN 
      (SELECT delRowTbl.PK, delRowTbl.Username, attr.delRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName, attr.delRow.value(''.'', ''nvarchar(max)'') as FieldValue FROM (Select      
                  d.'+@IDColumnName+' as PK,
                  d.'+@LastModifiedByColumnName+' as Username,
                  convert(xml, (select d.* for xml raw)) as delRowCol
                from deleted as d) as delRowTbl
                CROSS APPLY delRowTbl.delRowCol.nodes(''/row/@*'') as attr(delRow)) as del on ins.PK = del.PK and ins.FieldName = del.FieldName
    WHERE isnull(ins.FieldName,del.FieldName) not in ('''+@LastModifiedByColumnName+''', '''+@IDColumnName+''', '''+@TimeStampColumnName+''') and
    ((ins.FieldValue is null and del.FieldValue is not null) or (ins.FieldValue is not null and del.FieldValue is null) or (ins.FieldValue != del.FieldValue))

END
')

PRINT 'end ' + @TableName

PRINT ''

END

#9

Each table one wants to monitor, will need its own trigger. It is pretty obvious, that - as pointed out in the accepted answer - code generation will be a good thing.

每个要监视的表都需要自己的触发器。很明显，正如在公认的答案中指出的那样，生成代码将是一件好事。

If you like this approach, it might be an idea to use this trigger and replace some generic steps with generated code for each table separately.

如果您喜欢这种方法，那么可以使用这个触发器，并使用每个表的生成代码替换一些通用步骤。

Nevertheless I created a fully generic Audit-Trigger. The observed table must have a PK, but this PK might even be multi-column.

尽管如此，我创建了一个完全通用的审计触发器。观察到的表必须有一个PK，但是这个PK甚至可能是多列的。

Some column types (like BLOBs) might not work, but you could easily exclude them.

有些列类型(如BLOBs)可能不能工作，但是您可以很容易地排除它们。

This will not be the best in performance :-D

这将不是最好的表现:-D

To be honest: This is more kind of an exercise...

老实说，这更像是一种练习……

SET NOCOUNT ON;
GO
CREATE TABLE AuditTest(ID UNIQUEIDENTIFIER
                      ,LogDate DATETIME
                      ,TableSchema VARCHAR(250)
                      ,TableName VARCHAR(250)
                      ,AuditType VARCHAR(250),Content XML);
GO

--Some table to test this (used quirky PK columns on purpose...)

-一些表来测试这个(故意使用古怪的PK列…)

CREATE TABLE dbo.Testx(ID1 DATETIME NOT NULL
                      ,ID2 UNIQUEIDENTIFIER NOT NULL
                      ,Test1 VARCHAR(100)
                      ,Test2 DATETIME);
--Add a two column PK
ALTER TABLE dbo.Testx ADD CONSTRAINT PK_Test PRIMARY KEY(ID1,ID2);

--Some test data

——一些测试数据

INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES
 ({d'2000-01-01'},NEWID(),'Test1',NULL)
,({d'2000-02-01'},NEWID(),'Test2',{d'2002-02-02'});

--This is the current content

——这是当前的内容

SELECT * FROM dbo.Testx;
GO

--The trigger for the audit

——审核的触发器

    CREATE TRIGGER [dbo].[UpdateTestTrigger]
    ON [dbo].[Testx]
    FOR UPDATE,INSERT,DELETE
    AS 
    BEGIN

        IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) RETURN;

        SET NOCOUNT ON;
        DECLARE @tableSchema VARCHAR(250);
        DECLARE @tableName   VARCHAR(250);
        DECLARE @AuditID UNIQUEIDENTIFIER=NEWID();
        DECLARE @LogDate DATETIME=GETDATE();

        SELECT @tableSchema = sch.name
              ,@tableName   = tb.name
        FROM sys.triggers AS tr
        INNER JOIN sys.tables AS tb ON tr.parent_id=tb.object_id 
        INNER JOIN sys.schemas AS sch ON tb.schema_id=sch.schema_id
        WHERE tr.object_id = @@PROCID

       DECLARE @tp VARCHAR(10)=CASE WHEN EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted) THEN 'upd'
                               ELSE CASE WHEN EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) THEN 'del' ELSE 'ins' END END;

       SELECT * INTO #tmpInserted FROM inserted;
       SELECT * INTO #tmpDeleted FROM deleted;

       SELECT kc.ORDINAL_POSITION, kc.COLUMN_NAME
       INTO #tmpPKColumns
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kc ON tc.TABLE_CATALOG=kc.TABLE_CATALOG
                                                            AND tc.TABLE_SCHEMA=kc.TABLE_SCHEMA
                                                            AND tc.TABLE_NAME=kc.TABLE_NAME
                                                            AND tc.CONSTRAINT_NAME=kc.CONSTRAINT_NAME
                                                            AND tc.CONSTRAINT_TYPE='PRIMARY KEY'
       WHERE tc.TABLE_SCHEMA=@tableSchema
         AND tc.TABLE_NAME=@tableName
       ORDER BY kc.ORDINAL_POSITION;

       DECLARE @pkCols VARCHAR(MAX)=
       STUFF
       (
       (
        SELECT 'UNION ALL SELECT ''' + pc.COLUMN_NAME + ''' AS [@name] , CAST(COALESCE(i.' + QUOTENAME(pc.COLUMN_NAME) + ',d.' + QUOTENAME(pc.COLUMN_NAME) + ') AS VARCHAR(MAX)) AS [@value] '
        FROM #tmpPKColumns AS pc
        ORDER BY pc.ORDINAL_POSITION
        FOR XML PATH('')
       ),1,16,'');

       DECLARE @pkColsCompare VARCHAR(MAX)=
       STUFF
       (
       (
        SELECT 'AND i.' + QUOTENAME(pc.COLUMN_NAME) + '=d.' + QUOTENAME(pc.COLUMN_NAME) 
        FROM #tmpPKColumns AS pc
        ORDER BY pc.ORDINAL_POSITION
        FOR XML PATH('')
       ),1,3,'');

       DECLARE @cols VARCHAR(MAX)=
       STUFF
       (
       (
        SELECT ',' + CASE WHEN @tp='upd' THEN 
               'CASE WHEN (i.[' + COLUMN_NAME + ']!=d.[' + COLUMN_NAME + '] ' +
               'OR (i.[' + COLUMN_NAME + '] IS NULL AND d.[' + COLUMN_NAME + '] IS NOT NULL) ' + 
               'OR (i.['+ COLUMN_NAME + '] IS NOT NULL AND d.[' + COLUMN_NAME + '] IS NULL)) ' +
               'THEN ' ELSE '' END +
               '(SELECT ''' + COLUMN_NAME + ''' AS [@name]' + 
                             CASE WHEN @tp IN ('upd','del') THEN ',ISNULL(CAST(d.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)),N''##NULL##'') AS [@old]' ELSE '' END + 
                             CASE WHEN @tp IN ('ins','upd') THEN ',ISNULL(CAST(i.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)),N''##NULL##'') AS [@new] ' ELSE '' END + 
                      ' FOR XML PATH(''Column''),TYPE) ' + CASE WHEN @tp='upd' THEN 'END' ELSE '' END
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA=@tableSchema AND TABLE_NAME=@tableName
        FOR XML PATH('')
       ),1,1,''
       );

        DECLARE @cmd VARCHAR(MAX)=   
        'SET LANGUAGE ENGLISH;
        WITH ChangedColumns AS
        (
        SELECT   A.PK' +
               ',A.PK.query(''data(/PK/Column/@value)'').value(''text()[1]'',''nvarchar(max)'') AS PKVals' +
               ',Col.*  
        FROM #tmpInserted AS i
        FULL OUTER JOIN #tmpDeleted AS d ON ' + @pkColsCompare +
       ' CROSS APPLY
        (
            SELECT ' + @cols + ' 
            FOR XML PATH(''''),TYPE
        ) AS Col([Column])
        CROSS APPLY(SELECT (SELECT tbl.* FROM (SELECT ' + @pkCols + ') AS tbl FOR XML PATH(''Column''), ROOT(''PK''),TYPE)) AS A(PK)
        )
        INSERT INTO AuditTest(ID,LogDate,TableSchema,TableName,AuditType,Content)
        SELECT  ''' + CAST(@AuditID AS VARCHAR(MAX)) + ''',''' + CONVERT(VARCHAR(MAX),@LogDate,126) + ''',''' + @tableSchema + ''',''' + @tableName + ''',''' + @tp + '''
        ,(
        SELECT ''' + @tableSchema + ''' AS [@TableSchema]
                ,''' + @tableName + ''' AS [@TableName]
                ,''' + @tp + ''' AS [@ActionType]
        ,(
            SELECT ChangedColumns.PK AS [*]
            ,(
            SELECT x.[Column] AS [*],''''
            FROM ChangedColumns AS x 
            WHERE x.PKVals=ChangedColumns.PKVals
            FOR XML PATH(''Values''),TYPE
            )
            FROM ChangedColumns
            FOR XML PATH(''Row''),TYPE
            )
        FOR XML PATH(''Changes'')
        );';

        EXEC (@cmd);

       DROP TABLE #tmpInserted;
       DROP TABLE #tmpDeleted;
    END
    GO

--Now let's test it with some operations:

——现在我们用一些操作来测试一下:

UPDATE dbo.Testx SET Test1='New 1' WHERE ID1={d'2000-01-01'};
UPDATE dbo.Testx SET Test1='New 1',Test2={d'2000-01-01'} ;
DELETE FROM dbo.Testx WHERE ID1={d'2000-02-01'};
DELETE FROM dbo.Testx WHERE ID1=GETDATE(); --no affect
INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES
 ({d'2000-03-01'},NEWID(),'Test3',{d'2001-03-03'})
,({d'2000-04-01'},NEWID(),'Test4',{d'2001-04-04'})
,({d'2000-05-01'},NEWID(),'Test5',{d'2001-05-05'});
UPDATE dbo.Testx SET Test2=NULL; --all rows
DELETE FROM dbo.Testx WHERE ID1 IN ({d'2000-02-01'},{d'2000-03-01'});
GO

--Check the final status

——检查最后的状态

SELECT * FROM dbo.Testx;
SELECT * FROM AuditTest;
GO

--Clean up (carefull with real data!)

——清理(用真实数据小心!)

DROP TABLE dbo.Testx;
GO
DROP TABLE dbo.AuditTest;
GO

The result of the insert

插入的结果

<Changes TableSchema="dbo" TableName="Testx" ActionType="ins">
  <Row>
    <PK>
      <Column name="ID1" value="May  1 2000 12:00AM" />
      <Column name="ID2" value="C2EB4D11-63F8-434E-8470-FB4A422A4ED1" />
    </PK>
    <Values>
      <Column name="ID1" new="May  1 2000 12:00AM" />
      <Column name="ID2" new="C2EB4D11-63F8-434E-8470-FB4A422A4ED1" />
      <Column name="Test1" new="Test5" />
      <Column name="Test2" new="May  5 2001 12:00AM" />
    </Values>
  </Row>
  <Row>
    <PK>
      <Column name="ID1" value="Apr  1 2000 12:00AM" />
      <Column name="ID2" value="28625CE7-9424-4FA6-AEDA-1E4853451655" />
    </PK>
    <Values>
      <Column name="ID1" new="Apr  1 2000 12:00AM" />
      <Column name="ID2" new="28625CE7-9424-4FA6-AEDA-1E4853451655" />
      <Column name="Test1" new="Test4" />
      <Column name="Test2" new="Apr  4 2001 12:00AM" />
    </Values>
  </Row>
  <Row>
    <PK>
      <Column name="ID1" value="Mar  1 2000 12:00AM" />
      <Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
    </PK>
    <Values>
      <Column name="ID1" new="Mar  1 2000 12:00AM" />
      <Column name="ID2" new="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
      <Column name="Test1" new="Test3" />
      <Column name="Test2" new="Mar  3 2001 12:00AM" />
    </Values>
  </Row>
</Changes>

The selective result of an update

更新的选择结果

<Changes TableSchema="dbo" TableName="Testx" ActionType="upd">
  <Row>
    <PK>
      <Column name="ID1" value="Feb  1 2000 12:00AM" />
      <Column name="ID2" value="D7AB263A-EEFC-47DB-A6BB-A559FE8F2119" />
    </PK>
    <Values>
      <Column name="Test1" old="Test2" new="New 1" />
      <Column name="Test2" old="Feb  2 2002 12:00AM" new="Jan  1 2000 12:00AM" />
    </Values>
  </Row>
  <Row>
    <PK>
      <Column name="ID1" value="Jan  1 2000 12:00AM" />
      <Column name="ID2" value="318C0A66-8833-4F03-BCEF-7AB78C91704F" />
    </PK>
    <Values>
      <Column name="Test2" old="##NULL##" new="Jan  1 2000 12:00AM" />
    </Values>
  </Row>
</Changes>

And the result of a delete

以及删除的结果。

<Changes TableSchema="dbo" TableName="Testx" ActionType="del">
  <Row>
    <PK>
      <Column name="ID1" value="Mar  1 2000 12:00AM" />
      <Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
    </PK>
    <Values>
      <Column name="ID1" old="Mar  1 2000 12:00AM" />
      <Column name="ID2" old="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
      <Column name="Test1" old="Test3" />
      <Column name="Test2" old="##NULL##" />
    </Values>
  </Row>
</Changes>

#10

There is a generic way to do it.

有一种通用的方法。

CREATE TABLE [dbo].[Audit](
    [TYPE] [CHAR](1) NULL,
    [TableName] [VARCHAR](128) NULL,
    [PK] [VARCHAR](1000) NULL,
    [FieldName] [VARCHAR](128) NULL,
    [OldValue] [VARCHAR](1000) NULL,
    [NewValue] [VARCHAR](1000) NULL,
    [UpdateDate] [datetime] NULL,
    [UserName] [VARCHAR](128) NULL
) ON [PRIMARY]