Creating Audit Triggers in SQL Server

Question

I need to implement change tracking in two tables in my SQL Server 2005 database. I need to check for additions, deletions, updates (in detail about what was updated). I planned to use a trigger to do this, but after I logged in to Google, I found it was incredibly easy to do it wrong, and I wanted to avoid it on the go.

Can someone post an example update trigger that will successfully and elegantly accomplish this? I hope to get an audit table with the following structure:

• ID
• Logdate
• Tablename
• TransactionType (update / insert / delete)
• Recordord
• Fieldname
• Oldvalue
• Newvalue

... but I am open to suggestions.

Thank!

Answer 1

I just want to call a couple of points:

Use code generators You cannot have a single procedure for tracking all tables; you will need to generate similar, but different triggers for each table being tracked. This kind of work is best for creating automated code. If I were you, I would use the XSLT transform to generate code from XML, and XML could be automatically generated from metadata. This makes it easy to maintain triggers by regenerating them every time you make changes to the audit logic / structure, or the target table is added / changed.

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

• each DML operation will require locks in the audit table.
• all administration and maintenance operations will need to take into account the size of the database due to the audit.

Consider circuit changes. A table named “Foo” can be deleted, and later another table called “Foo” can be created. The audit trail should be able to distinguish between two different objects. Better to use a slowly changing size .

Consider the need to effectively delete audit records . When the retention period dictated by the policies relevant to your application is mandatory, you should be able to delete the due diligence records. Now this may not seem so big, but after 5 years, when the first entries will be entered in the audit table, it has grown to 9.5 TB, this may be a problem.

Consider the need to request an audit . The structure of the audit table should be prepared to respond effectively to audit requests. If your audit cannot be requested, then it does not matter. Requests will completely depend on your requirements, and only you know them, but most audit records are requested for time intervals ("what changes have occurred yesterday from 7 pm to 8 pm?"), By object ("what changes have occurred with this entry in this table? ') or by the author ("what changes did Bob make to the database?").

Answer 2

We use ApexSQL Audit , which generates audit triggers and below are the data structures used by this tool. If you do not plan to buy a third-party solution, you can install this tool in trial mode, see how they implemented triggers and storage, and then create something similar for yourself.

I was not worried about getting too much information about how these tables work, but I hope this helps you get started.

Answer 3

There is no general way to do this the way you want. Ultimately, you end up writing code codes for each table. Not to mention that it can be very slow if you need to compare each column for a change.

Also, the fact that you can update multiple lines at the same time implies that you need to open the cursor to scroll through all the records.

The way I do this will use a table with a structure identical to the tables you are tracking, and not open it later to show which columns have really changed. I will also be following the session that actually made the change. This assumes that you have a primary key in the monitored table.

So for 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 would create an audit table like this in an audit sheme.

 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 an update trigger 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 is pretty fast. During reports, you simply join rows based on sessionID and primary key and create a report. Alternatively, you can have a batch job that periodically goes through all the tables in the audit table and prepares a name-value pair that shows the changes.

NTN

Answer 4

Mike, we use the tool www.auditdatabase.com, this free tool generates audit triggers, and it works well with SQL Server 2008 and 2005 and 2000. Its a complex and insane tool that allows you to configure audit triggers for a table.

Another Great Tool - Apex SQL Audit

Answer 5

I will throw my approach and suggestions into the mix.

I have a very similar table with the proposed design that I have used over the past seven years in the SQL 2005 database (now 2008).

I added add, update, and delete triggers to the selected tables, and then checked the changes in the selected fields. At that time, it was simple and working well.

Here are the problems that I find with this approach:

• The fields for the old / new values ​​of the audit table had to be varchar (MAX) types in order to be able to handle all the different values ​​that could be checked: int, bool, decimal, float, varchar, etc. to match

• The verification code for each field is tedious to record support. It is also easy to skip things (for example, change the null field to a value that did not hit, because the value is NULL! = Equals NULL.

• Delete recording: how do you record this? All fields? Selected? It is getting complicated.

My future vision is to use some SQL-CLR code and write a generated trigger that executes, and check the table metadata to see what to do. Secondly, New / Old values ​​will be converted to XML fields and the entire object is written: this leads to more data, but the deletion has an entire record. There are several articles on the web about XML audit triggers.

Answer 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 

Answer 7

A trigger is used. If you modify or insert into a specific table, this will be executed, and you can check the specific column in the trigger. A full explanation is provided on the following website. http://www.allinworld99.blogspot.com/2015/04/triggers-in-sql.html

Answer 8

Finally, I found a universal solution that does not require dynamic changes to sql and logs for all columns.

No need 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 a 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 a single table named AGB. A table named AGB has a primary key column with a name identifier and a column named LastModifiedBy that contains the name of the user who made the last change.

The trigger consists of two parts, first it converts the columns of the inserted and deleted tables into rows. This is described in detail here: stack overflow

Then it concatenates the rows (one row per column) of the inserted and deleted tables using the primary key and the field name and writes a row for each changed column. It does NOT register changes to ID, TimeStamp or LastModifiedByColumn.

You can insert your own names TableName, Columns.

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 

Answer 9

Each table that needs to be tracked will have its own trigger. Obviously, as indicated in the accepted answer, code generation will be a good thing.

If you like this approach, you might get the idea to use this trigger and replace some common steps with the generated code for each table separately.

However, I created a completely general audit trigger . The observed table must have PK , but this PK can even be multiple columns.

Some types of columns (e.g. BLOBs) may not work, but you can easily exclude them.

It will not be the best in performance: -D

Honestly: This is more 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 verify this (fancy PK columns were used for purposes ...)

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

is current content

 SELECT * FROM dbo.Testx; GO 

- trigger for 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 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 final status

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

- Clear (be careful with real data! )

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

Insert result

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

Selective Update Result

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

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

There is a general way to do this.

 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]