SQL Server 表审计触发器

Question

我有两个表 Customers 和 AuditTable。当我修改 Customers 表时，我需要在 AuditTable 中插入一条新记录:

CREATE TABLE [dbo].[AuditTable]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [StateBefore] [nvarchar](max) NULL,
    [StateAfter] [nvarchar](max) NULL
) 

我需要将 Customer 状态的 XML 表示放入 StateBefore 和 StateAfter 中，更新前后。

Customer 表是:

CREATE TABLE [dbo].[Customer]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [Email] [nvarchar](max) NOT NULL,
    [IsDeleted] [bit] NULL,
    [CreatedUtc] [datetime] NOT NULL,
    [UpdatedUtc] [datetime] NULL,
    [Version] [timestamp] NOT NULL,

    CONSTRAINT [PK_dbo.Customer] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
           WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,  
                 ALLOW_PAGE_LOCKS = ON)
)
GO

ALTER TABLE [dbo].[Customer] 
   ADD DEFAULT (getutcdate()) FOR [CreatedUtc]
GO

我找到了获取行的 xml 表示的方法:

SELECT
    [State] = (SELECT *
               FROM dbo.Customer [Customer]
               WHERE [Customer].Id = cust.Id
               FOR XML AUTO
              )
FROM 
    dbo.Customer cust

仅举个例子。因此，在我的触发器中，我需要为 deleted 和 inserted 表中的行创建 xml 表示。

这是触发器:

ALTER TRIGGER [dbo].[UpdateCustomerTrigger]
ON [dbo].[Customer]
FOR UPDATE
AS 
BEGIN
    UPDATE Customer 
    SET UpdatedUtc = GETDATE()
    FROM INSERTED
    WHERE inserted.id = Customer.Id

    -- here I need to insert new records into AuditTable
END

那么，我如何连接 deleted 和 inserted 表的两个表示，以将它们正确地插入到 AuditTable 中？谢谢。

Answer 1

很高兴您已经找到了解决方案...

我只是在想类似的事情......

您的方法将在任何微小更改处获取完整记录的两份副本。由于我必须处理包含很多列的表，其中一些是 BLOB，这不适合我。

好吧，我没有找到绝对“干净”的方法，但通过以下方法，您将获得一个 AuditLog，其中仅包含以更易读的方式进行了实际更改的值。

也许你喜欢它:

测试场景

编辑:添加了对INSERT 和DELETE 以及NULL 值的支持。

尝试一下:

CREATE TABLE AuditTest(TableSchema VARCHAR(250), TableName VARCHAR(250), AuditType VARCHAR(250),Content XML, LogDate DATETIME DEFAULT GETDATE());
GO

CREATE TABLE dbo.Test(ID INT,Test1 VARCHAR(100),Test2 DATETIME,ModifyCounter INT DEFAULT 0,LastModified DATETIME DEFAULT GETDATE());
INSERT INTO dbo.Test(ID,Test1,Test2) VALUES
 (1,'Test1',{d'2001-01-01'})
,(2,'Test2',{d'2002-02-02'});

--当前内容

SELECT * FROM dbo.Test;
GO

--审计的触发器

CREATE TRIGGER [dbo].[UpdateTestTrigger]
ON [dbo].[Test]
FOR UPDATE,INSERT,DELETE
AS 
BEGIN
   IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) RETURN;

   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;
   WITH UpdateableCTE AS
   (
    SELECT t.LastModified,t.ModifyCounter 
    FROM dbo.Test AS t
    INNER JOIN inserted AS i ON t.ID=i.ID
   )
   UPDATE UpdateableCTE SET LastModified=GETDATE()
                           ,ModifyCounter=ModifyCounter+1;

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

   DECLARE @tableSchema VARCHAR(250)='dbo';
   DECLARE @tableName   VARCHAR(250)='Test';

   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 COALESCE(i.ID,d.ID) AS ID
            ,Col.*  
    FROM #tmpInserted AS i
    FULL OUTER JOIN #tmpDeleted AS d ON i.ID=d.ID
    CROSS APPLY
    (
        SELECT ' + @cols + ' 
        FOR XML PATH(''''),TYPE
    ) AS Col([Column])
    )
    INSERT INTO AuditTest(TableSchema,TableName,AuditType,Content)
    SELECT ''' + @tableSchema + ''',''' + @tableName + ''',''' + @tp + '''
    ,(
    SELECT ''' + @tableSchema + ''' AS [@TableSchema]
            ,''' + @tableName + ''' AS [@TableName]
            ,''' + @tp + ''' AS [@ActionType]
    ,(
        SELECT ChangedColumns.ID AS [@ID]
        ,(
        SELECT x.[Column] AS [*],''''
        FROM ChangedColumns AS x WHERE x.ID=ChangedColumns.ID
        FOR XML PATH(''''),TYPE
        )
        FROM ChangedColumns
        FOR XML PATH(''Row''),TYPE
        )
    FOR XML PATH(''Changes'')
    );';

    EXEC (@cmd);

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

--现在让我们用一些操作来测试它:

UPDATE dbo.Test SET Test1='New 1' WHERE ID=1;
UPDATE dbo.Test SET Test1='New 1',Test2={d'2000-01-01'} ;
DELETE FROM dbo.Test WHERE ID=2;
DELETE FROM dbo.Test WHERE ID=99; --no affect
INSERT INTO dbo.Test(ID,Test1,Test2) VALUES
 (3,'Test3',{d'2001-03-03'})
,(4,'Test4',{d'2001-04-04'})
,(5,'Test5',{d'2001-05-05'});
UPDATE dbo.Test SET Test2=NULL; --all rows
DELETE FROM dbo.Test WHERE ID IN (1,3);
GO

--查看最终状态

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

--清理

DROP TABLE dbo.Test;
GO
DROP TABLE dbo.AuditTest;
GO

第二个操作的结果:更新两行

<Changes TableSchema="dbo" TableName="Test" ActionType="upd">
  <Row ID="2">
    <Column name="Test1" old="Test2" new="New 1" />
    <Column name="Test2" old="Feb  2 2002 12:00AM" new="Jan  1 2000 12:00AM" />
  </Row>
  <Row ID="1">
    <Column name="Test2" old="Jan  1 2001 12:00AM" new="Jan  1 2000 12:00AM" />
  </Row>
</Changes>

INSERT 操作的结果:三个新行

<Changes TableSchema="dbo" TableName="Test" ActionType="ins">
  <Row ID="5">
    <Column name="ID" new="5" />
    <Column name="Test1" new="Test5" />
    <Column name="Test2" new="May  5 2001 12:00AM" />
    <Column name="ModifyCounter" new="0" />
    <Column name="LastModified" new="Aug 18 2017  5:48PM" />
  </Row>
  <Row ID="4">
    <Column name="ID" new="4" />
    <Column name="Test1" new="Test4" />
    <Column name="Test2" new="Apr  4 2001 12:00AM" />
    <Column name="ModifyCounter" new="0" />
    <Column name="LastModified" new="Aug 18 2017  5:48PM" />
  </Row>
  <Row ID="3">
    <Column name="ID" new="3" />
    <Column name="Test1" new="Test3" />
    <Column name="Test2" new="Mar  3 2001 12:00AM" />
    <Column name="ModifyCounter" new="0" />
    <Column name="LastModified" new="Aug 18 2017  5:48PM" />
  </Row>
</Changes>

最后一个 DELETE 操作的结果

<Changes TableSchema="dbo" TableName="Test" ActionType="del">
  <Row ID="3">
    <Column name="ID" old="3" />
    <Column name="Test1" old="Test3" />
    <Column name="Test2" old="##NULL##" />
    <Column name="ModifyCounter" old="2" />
    <Column name="LastModified" old="Aug 18 2017  5:48PM" />
  </Row>
  <Row ID="1">
    <Column name="ID" old="1" />
    <Column name="Test1" old="New 1" />
    <Column name="Test2" old="##NULL##" />
    <Column name="ModifyCounter" old="3" />
    <Column name="LastModified" old="Aug 18 2017  5:48PM" />
  </Row>
</Changes>