(Almost) Generic Data Audit Trail for SQL Server

I’ve been looking for some time for a product or method that would easily give me an audit trail of changes to parameter-tables that users change themselves, but I weren’t really able to find anything.

The “right” thing to do, would of course be to include some sort of “latest valid record” method directly in the datatables, ie some sort of CUD indicator and datetime with each record – or would it? I will always try to avoid unnecessary complexity as it will always translate into technical debt

So logging with a trigger to a secondary table seems a much simpler option. But on the other hand, building a specific audit table, with a matching schema, for each table in my database is also not a very good idea – it leads to double schema maintenance and unnecessary errors. Brooding over this, I came past the following references: Nigel Rivett’s Generic Audit Trail Trigger and Creating a generic audit trigger with SQL 2005 CLR from SQLjunkies, and it looked a lot like what I wanted: A generic method for storing information about multiple tables in a single audit table – but it still seemed like such an awful lot of code for a very simple problem. Finally there was Richards C# blog, with an interesting article called Super easy SQL Server 2005 Database Schema change auditing. Richard uses built in XML formats to store the audit trail of database schema changes (another interesting issue), and this gave me the idea to simply store the results of the “inserted” and “deleted” data in a trigger as XML.

The answer I have come up with is, in my opinion, both short in code, easy in implementation and rich in flexibility. I have created an Audit table that looks like this:

CREATE TABLE [dbo].[Audit](
[AuditID] [bigint] IDENTITY(1,1) NOT NULL,
[Updatetime] [datetime] NULL,
[UserName] [varchar](50) NULL,
[TableName] [varchar](50) NULL,
[DeleteCount] [tinyint] NULL,
[DeleteXML] [xml] NULL,
[InsertCount] [tinyint] NULL,
[InsertXML] [xml] NULL

Nothing special there, except for the two XML columns that wil hold the changed data from the trigger. The trigger itself is also very short (and hopefully understandable). To create an audit trail for another table, all you need to do is to edit the @TableName parameter, as unfortunately the trigger itself does not know what table it is operating on.

/* (Almost) Generic Data Audit Trail, Per Hejndorf 2007                                                 */

CREATE TRIGGER [dbo].[trigMyTable]
ON [dbo].[MyTable]
/* Change the Tablename here for each individual table trigger                                    */
DECLARE @TableName VARCHAR(50); SET @TableName = ‘MyTable’;
/* Main trigger */
DECLARE @count_inserted int, @count_deleted int;
SELECT @count_inserted = COUNT(*) FROM inserted;
SELECT @count_deleted = COUNT(*) FROM deleted;

DECLARE @xml_inserted xml, @xml_deleted xml;
SELECT @xml_inserted = (SELECT * FROM inserted FOR XML RAW);
SELECT @xml_deleted = (SELECT * FROM deleted FOR XML RAW);

INSERT INTO Audit VALUES (GetDate(), SYSTEM_USER, @TableName, @count_deleted, @xml_deleted, @count_inserted, @xml_inserted);

All there needs to be done to add auditing to another table is to search’n’replace the references to “MyTable”, and if there are any triggers already in play, possibly move this one to the end of the chain. When checking the audit-table for the Create, Update, Delete operations, the inserted (C) lines are identifiably by DeleteCount = 0 and InsertCount > 0, updated (U) lines by DeleteCount > 0 and InsertCount > 0, and the deleted (D) lines by DeleteCount > 0 and InsertCount = 0.

Did I overlook or forget anything? Please post me a comment 🙂

Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s