问题描述
我有两个表 Customers 和 AuditTable.当我修改Customers表时,需要在AuditTable中插入一条新记录:
I have two tables Customers and AuditTable. When I modify the Customers table, I need to insert a new record into the AuditTable:
CREATE TABLE [dbo].[AuditTable] ( [Id] [int] IDENTITY(1,1) NOT NULL, [StateBefore] [nvarchar](max) NULL, [StateAfter] [nvarchar](max) NULL )
我需要在更新前后将 Customer 状态的 XML 表示放入 StateBefore 和 StateAfter 中.
I need to place an XML representation of Customer state into StateBefore and StateAfter, before and after update.
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 表示的方法:
I found the way how to get an xml representation of rows:
SELECT [State] = (SELECT * FROM dbo.Customer [Customer] WHERE [Customer].Id = cust.Id FOR XML AUTO ) FROM dbo.Customer cust
这只是举例.所以在我的触发器中,我需要从 deleted 和 inserted 表中创建行的 xml 表示.
It just for example. So in my trigger I need to create the xml representation of rows from deleted and inserted tables.
这是触发器:
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 中?谢谢.
So, how can I join two representations of deleted and inserted tables to correctly insert them into the AuditTable? Thanks.
推荐答案
很高兴您已经找到了解决方案...
Great that you've found a solution already...
我只是在考虑类似的事情...
I was just thinking about something similar...
您的方法将在任何微小的变化中都需要两份完整记录.由于我必须处理包含很多列的表,其中一些是 BLOB,这不适合我.
Your approach would take two copies of the full record at any tiny change. As I have to deal with tables with a lot of columns, some of them are BLOBs, this would not fit for me.
好吧,我没有找到绝对干净"的方法,但是通过以下操作,您将获得一个 AuditLog,其中仅包含以更易于阅读的方式实际更改的值.
Well, I did not find an absolutely "clean" approach, but with the following you'll get an AuditLog with only the values which have changed acutally in a better to read style.
也许你喜欢它:
试试看:
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
--现在让我们用一些操作来测试一下:
--Now let's test it with some operations:
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 操作的结果:三个新行
Result of INSERT action: three new rows
<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 操作的结果
Result of last DELETE action
<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>