I very rarely post any of my own code online (with the exception of helping folks over at Experts Exchange) because most of my coding is done at work and there’s so little about work I can post. So here’s a rare one inspired by Jon Galloway’s post on simple trigger-based auditing.
Oh, and since my home development computer is dead, no pretty screen shots for you!
My work demands that we have 100% accountability in terms of data creation and modification. With our recent upgrade to SQL2005 I took the opportunity to work with the xml datatype to implement trigger based auditing that stores the data as XML. I get all the benefits of XML in SQL2005 (like xquery support), I can easily display the data in the web application with XSLT, and I still have some structure to the audited data without having to worry about the source table schema changing.
In order to make some standard querying easier, the trigger extracts out some data that we are often requested to query on (primary key ID, created on/created by, updated on/updated by). This allows for answers to simple questions like “when was this record created”, “how often has it been modified”, “when was the last time it was modified”, and “who modified it.”
The trigger itself is for INSERT, UPDATE and DELETE so that I can have a record of the data’s changes from insertion through deletion. The end result is one row in the audit table for each modification to the source row with the full contents of the source row stored as xml in the AuditedData column.
The structure is simple: a table to hold the audit data and one trigger per table being audited.
1. The Audit Table.
CREATE TABLE [tblAudit]
[AuditID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_tblAudit_AuditID] DEFAULT (newid()),
[AuditedOn] [datetime] NOT NULL CONSTRAINT [DF_tblAudit_AuditedOn] DEFAULT (getutcdate()),
[AuditedData] [xml] NOT NULL,
[PrimaryKeyID] [uniqueidentifier] NOT NULL,
[CreatedBy] [uniqueidentifier] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[UpdatedBy] [uniqueidentifier] NULL,
[UpdatedOn] [datetime] NULL,
CONSTRAINT (PK_tblAudit] PRIMARY KEY CLUSTERED
(
[AuditID] ASC
) ON PRIMARY
2. The Trigger
CREATE TRIGGER [trgAudit_tblMyTable]
ON [tblMyTable]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Xml XML
IF EXISTS(SELECT TOP 1 * FROM Inserted) BEGIN
SET @Xml = (SELECT * FROM Inserted AS tblMyTable FOR XML AUTO)
INSERT INTO tblAudit (AuditedData, PrimaryKeyID, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy)
SELECT @Xml.query('tblMyTable[@MyTableID=sql:column("MyTableID")]'), MyTableID, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy FROM Inserted
END
ELSE IF EXISTS(SELECT TOP 1 * FROM Deleted) BEGIN
SET @Xml = (SELECT * FROM Deleted AS tblMyTable FOR XML AUTO)
INSERT INTO tblAudit (AuditedData, PrimaryKeyID, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy)
SELECT @Xml.query('tblMyTable[@MyTableID=sql:column("MyTableID")]'), MyTableID, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy FROM Deleted
END
SET NOCOUNT OFF;
END
Querying values inside of the XML data is done using SQL 2005’s XQuery support.
SELECT * FROM tblAudit
WHERE AuditedData.exist('tblMyTable[@SomeField="SomeValue"]') = 1
Besides data auditing, you can also use XML and triggers to audit database object schema changes.
