很高兴您已经找到了解决方案…
我只是在想类似的事情…
您的方法将 在任何微小变化下获取完整记录的两份副本 。由于我必须处理具有很多列的表，其中一些是BLOB，所以这不适合我。
好吧，我没有找到一个绝对的“干净”方法，但是通过下面的操作，您将获得一个AuditLog，其中仅包含那些以更易于阅读的方式进行了手动更改的值。
也许您喜欢它：
测试场景
编辑：添加了对INSERT
和DELETe
和NULL
值的支持。
试试看：
CREATE TABLE AuditTest(TableSchema VARCHAr(250), TableName VARCHAr(250), AuditType VARCHAr(250),Content XML, LogDate DATETIME DEFAULT GETDATE());GOCREATE 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,DELETEAS 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;ENDGO-现在让我们通过一些操作对其进行测试：
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 affectINSERT 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 rowsDELETE FROM dbo.Test WHERe ID IN (1,3);GO-检查最终状态
SELECt * FROM dbo.Test;SELECt * FROM AuditTest;GO
- 清理
DROP TABLE dbo.Test;GODROP 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>
