在 SQL Server 中创建审核触发器

Question

我需要对 SQL Server 2005 数据库中的两个表实施更改跟踪。我需要审核添加、删除、更新（详细说明更新内容）。我本来计划使用触发器来执行此操作，但似乎很容易错误地执行此操作。

任何人都可以发布一个更新触发器的示例，以优雅的方式成功地完成此任务吗？我希望最终得到一个具有以下结构的审计表：

• ID
• LogDate
• TableName
• TransactionType (update/insert/delete)
• RecordID
• FieldName
• OldValue
• NewValue

...想法？

Answer 1

我只想指出几点：

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

考虑审核容量规划。到目前为止，跟踪所有值更改的审计表将是数据库中最大的表：它将包含所有当前数据和
当前数据的所有历史记录。这样的表将使数据库大小增加 2-3 个数量级（x10、x100）。并且审计表将很快成为一切的瓶颈：

• 每个 DML 操作都需要在审计表中锁定
• 所有管理和维护操作都必须适应数据库的大小，由于审计

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

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

考虑查询审核的需要。必须准备审计表结构以有效地响应审计查询。如果您的审计无法被查询，那么它就没有价值。查询将完全由您的需求驱动，并且只有您知道这些，但大多数审计记录都是按时间间隔（“昨天晚上 7 点到 8 点之间发生了什么变化？”）、按对象（“此记录在此记录中发生了什么变化”）进行查询。表？”）或按作者（“Bob 在数据库中做了哪些更改？”）。

Answer 2

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

我没有费心去了解这些表如何工作的太多细节，但希望这能让您开始。

Answer 3

没有通用的方法可以按照您想要的方式进行操作。最终您最终会为每个表编写大量代码。更不用说如果您需要比较每一列的变化，它可能会非常慢。

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

我的方法是使用与您正在跟踪的表结构相同的表，并稍后将其逆透视以显示哪些列实际发生了更改。我还会跟踪实际进行更改的会话。这假设您在被跟踪的表中有主键。

因此，给定一个这样的表，

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

我将在审计 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())

和这样的更新触发器

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

这运行得相当快。在报告期间，您只需根据 sessionID 和主键连接行并生成报告。或者，您可以使用批处理作业定期检查审核表中的所有表并准备显示更改的名称-值对。

华泰

Answer 4

它看起来很简单，并且应该可以很好地工作，直到表中有 image/varbinary 等元素
您拥有完整的旧记录和全新的 xml 记录。
还应该可以正常工作以批量插入多列。

CREATE TABLE _AuditTable
(Aud_Id int identity(1,1) primary key,
Aud_TableName varchar(100), 
Aud_ActionType char(1),
Aud_Username varchar(100),
Aud_OLDValues xml, 
Aud_NEWValues xml,
Aud_OperationDate datetime DEFAULT GETDATE()
)

并触发代码

CREATE TRIGGER _test2_InsertUpdate on _test2
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
 IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) 
    RETURN;

declare @tablename varchar(100)
SELECT @tablename = OBJECT_NAME(parent_object_id) 
             FROM sys.objects 
             WHERE sys.objects.name = OBJECT_NAME(@@PROCID)

/*Action*/
DECLARE @ActionType char(1)
IF EXISTS (SELECT * FROM inserted)
       IF EXISTS (SELECT * FROM deleted)
               SELECT @ActionType = 'U'
       ELSE
               SELECT @ActionType = 'I'
ELSE
       SELECT @ActionType = 'D'

declare @inserted xml, @deleted xml 
SET @inserted = (SELECT * FROM inserted FOR XML PATH)
SET @deleted = (SELECT * FROM deleted FOR XML PATH)

             INSERT INTO _AuditTable(Aud_TableName, Aud_ActionType, Aud_Username, Aud_OLDValues, Aud_NEWValues)
             SELECT @tablename, @ActionType, SUSER_SNAME(), @deleted, @inserted
END

OUTPUT

Aud_Id | Aud_TableName  | Aud_ActionType | Aud_Username | Aud_OLDValues | Aud_NEWValues |   Aud_OperationDate
1      |_test2          |   I            |abc\mR        |   NULL        |<row><name>abc</name></row> |  2018-11-07 12:38:34.937

Answer 5

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

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

这是审核日志：

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]

这是一个表的触发器：

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))

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

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

然后它连接行 (按主键和字段名称插入和删除的表的每列一行），并为每个更改的列记录一行。它不记录 ID、TimeStamp 或 LastModifiedByColumn 的更改。

您可以插入自己的表名、列名。

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

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

Answer 6

每个想要监控的表都需要它自己的触发器。很明显，正如已接受的答案中所指出的那样，代码生成将是一件好事。

如果您喜欢这种方法，那么使用此触发器并用为每个表单独生成的代码替换一些通用步骤可能是一个主意。

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

某些列类型（例如 BLOB）可能不起作用，但您可以轻松排除它们。

这不会是最好的性能：-D

说实话：这更像是一种练习...--

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

一些表来测试它（故意使用古怪的 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);

--一些测试数据

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'});

--这是当前的内容

SELECT * FROM dbo.Testx;
GO

--审计的触发器

    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

--现在我们用一些操作来测试一下：

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

--检查最终状态

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

--清理（小心真实数据！)

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

插入的结果

<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>

更新的选择性结果

<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>

以及删除的结果

<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>

Answer 7

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

Answer 8

我有一个与我所使用的您提议的设计非常相似的表格。

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

以下是我使用这种方法发现的问题：

1. 审计表旧/新值字段必须是 varchar(MAX) 类型才能处理可以审计的所有不同值：int、bool、decimal、float 、varchar 等都必须适合

2. 检查每个字段的代码编写维护起来很乏味。也很容易错过一些事情（例如将 null 字段更改为未捕获的值，因为 NULL != value 是 NULL。

3. 删除记录：如何记录这个？所有字段？选定的字段？它变得复杂

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

Answer 9

触发器用于如果您修改或插入特定表，它将执行，并且您可以检查触发器中的特定列。带有解释的完整示例位于以下网站中。
http://www.allinworld99.blogspot.com/2015/04 /triggers-in-sql.html