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

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)
)
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
DECLARE @Cont VARCHAR(128) =(SELECT CAST(CONTEXT_INFO() as varchar(128)))--Retrieve Context Info
INSERT INTO AuditAudit(AuditAuditProcess,AuditID,AuditData)
SELECT ISNULL(@Cont,CAST(@@Spid AS VARCHAR(20))),AuditID,AuditData FROMInserted--Insert process id if there is not context added
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 context
INSERT INTO Audit(AuditData)
SELECT '1'
-- insert with context
GO
DECLARE @Context_Info varbinary(128)
SELECT @Context_Info = CAST('Insert'+SPACE(128) AS VARBINARY(128))--Declare the name for the context
SET CONTEXT_INFO @Context_Info--Update Context info to above
INSERT INTO Audit(AuditData)--Run your code
SELECT '2'
-- update with context
GO
DECLARE @Context_Info varbinary(128)
SELECT @Context_Info = CAST('Update'+SPACE(128) AS VARBINARY(128))--Declare the name for the context
SET CONTEXT_INFO @Context_Info--Update Context info to above
UPDATE Audit SET AuditData='3' WHERE AuditID=1--Run your code
--Select Records from the audit table
GO
SELECT * FROM AuditAudit
Your select statement should have three rows as per below.
AuditIDAuditDateTimeAuditProcessAuditIdAuditData
12013-01-29 18:21:23.0975111
22013-01-29 18:21:27.433Insert22
32013-01-29 18:21:30.710Update13
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.
Ousia Logo