Using a context trigger for SQL Server Data Auditing

SQL Server 2008, Easy data auditing - Using a trigger and context to trace updates to an SQL Table

Aim

Auditing your data can at times be a bit of a nightmare. I recently set up a database for an automated dialler, it needed to be updated from multiple sources, meaning that in the case of errors it would be very hard to see what process had made the change.

I had recently read about using Context as a way of passing data between processes in SQL, and as luck would have it, this was what was needed, along with a trigger.

First of all we will create two tables, one with the data we want, then a another with the same columns and an identity column, date time and process name to store all of our changes.

SQL

CREATE TABLE Audit(AuditID INT IDENTITY(1,1) CONSTRAINT PK_AuditID PRIMARY KEY,AuditData NVARCHAR(100))CREATE TABLE AuditAudit(AuditAuditID INT IDENTITY(1,1) CONSTRAINT PK_AuditAuditID PRIMARY KEY,AuditAuditDateTime DATETIME DEFAULT GETDATE(),AuditAuditProcess NVARCHAR(128),AuditID INT,AuditData NVARCHAR(100))

Aim

Next we will create a trigger on the table. We want to run it after any inserts or updates. This trigger will then add a row to the AuditAudit table recording what the new data is, the date and time of the change and the process (if we have put it in) or connection id. More on triggers can be found on MSDN.

SQL

CREATE TRIGGER AuditUpdated ON Audit AFTER INSERT, UPDATE AS BEGINDECLARE @Cont VARCHAR(128) =(SELECT CAST(CONTEXT_INFO() as varchar(128)))--Retrieve Context InfoINSERT INTO AuditAudit(AuditAuditProcess,AuditID,AuditData)SELECT ISNULL(@Cont,CAST(@@Spid AS VARCHAR(20))),AuditID,AuditData FROM Inserted--Insert process id if there is not context addedENDGO

Aim

Now we can a few statements to update the table. First we perform a normal insert, then an insert and an update with context populated.

SQL

--insert with no contextINSERT INTO Audit(AuditData)SELECT '1'--insert with contextGODECLARE @Context_Info varbinary(128)SELECT @Context_Info = CAST('Insert'+SPACE(128) AS VARBINARY(128))--Declare the name for the contextSET CONTEXT_INFO @Context_Info--Update Context info to aboveINSERT INTO Audit(AuditData)--Run your codeSELECT '2'--update with contextGODECLARE @Context_Info varbinary(128)SELECT @Context_Info = CAST('Update'+SPACE(128) AS VARBINARY(128))--Declare the name for the contextSET CONTEXT_INFO @Context_Info--Update Context info to aboveUPDATE Audit SET AuditData='3' WHERE AuditID=1--Run your code--Select Records from the audit tableGOSELECT * FROM AuditAudit

Results

Your select statement should have three rows as per below.

AuditID	AuditDateTime	AuditProcess	AuditId	AuditData
1	2013-01-29 18:21:23.097	51	1	1
2	2013-01-29 18:21:27.433	Insert	2	2
3	2013-01-29 18:21:30.710	Update	1	3

I found the easiest way to use this was to attach unique process names to stored procedures that update the tables, that way you could track where the data changes came from.