The problem
Creating an audit log is often a tricky proposal. Whether it’s for legal purposes or you just want to give a history of modifications made to your data, Ensuring that your business objects properly keep track of what’s going on often creates a lot of extra work, and if your application isn’t designed properly (or you’re handed legacy code) it can be nearly impossible to track down every instance of CRUD statements.
The proposal
The easiest way to consistently audit every action is via a trigger in the database. Even in poorly written legacy code, developers are very unlikely to have found a reason to disable triggers before running insert/update/delete statements. Using an “INSTEAD OF“ trigger to capture modifications and log it to a single xml column gives you the consistency of a trigger and the ease of data transformation that xml provides.
Limitations
- Developers need to be cognizant of the fact that the magic is being performed by a database trigger.
- T-SQL is not a normal language for developers to be fluent in.
The solution
The XML Schema. This is simply here to ensure that the format of the log stays exactly what we expected.
CREATE XML SCHEMA COLLECTION LogSchema AS N'<xs:schema
attributeFormDefault="unqualified"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns="http://djeebus.net/schemas/xml-log"
targetNamespace="http://djeebus.net/schemas/xml-log">
<xs:simpleType name="entryType">
<xs:restriction base="xs:string">
<xs:enumeration value="insert" />
<xs:enumeration value="update" />
<xs:enumeration value="delete" />
<xs:enumeration value="comment" />
</xs:restriction>
</xs:simpleType>
<xs:element name="log">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="entry">
<xs:complexType>
<xs:sequence>
<xs:element name="column" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="name" type="xs:string" use="required" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="type" type="entryType" use="required" />
<xs:attribute name="date" type="xs:dateTime" use="required" />
<xs:attribute name="user" type="xs:string" use="required" />
<xs:attribute name="comment" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>';
GO
The test table. Just a simple table that describes users.
CREATE TABLE Users
(
UserId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT (NEWID()),
Username NVARCHAR(MAX) NOT NULL,
Email NVARCHAR(MAX) NOT NULL,
PasswordHash VARBINARY(128) NOT NULL,
IsDisabled BIT NOT NULL DEFAULT (0),
IsActivated BIT NOT NULL DEFAULT (0),
Address1 NVARCHAR(MAX) NULL,
Address2 NVARCHAR(MAX) NULL,
City NVARCHAR(MAX) NULL,
State NVARCHAR(MAX) NULL,
Country NVARCHAR(MAX) NULL,
-- columns necessary for logging
ModifiedBy UNIQUEIDENTIFIER NULL,
Comment NVARCHAR(MAX) NULL,
Log XML(LogSchema) NOT NULL DEFAULT (''),
UNIQUE (Username)
)
GO
The trigger. Here’s where the magic happens.
CREATE TRIGGER Users_Log
ON Users
INSTEAD OF INSERT,UPDATE
AS
-- avoid screwing up counts on queries
SET NOCOUNT ON
-- if nothing is updated or inserted, bail
IF (SELECT COUNT(*) FROM Inserted) = 0
BEGIN
RETURN -- nothing to do here
END
-- determine whether this is an update or delete query
DECLARE @is_update BIT
SET @is_update = CASE WHEN (SELECT COUNT(*) FROM Deleted) = 0 THEN 0 ELSE 1 END
IF @is_update = 1 -- all columns show as updated on inserts
BEGIN
IF UPDATE(Log) -- if log column is updated, assume a comment was added
BEGIN
-- update only the log column, throw out other updates and return immediately
UPDATE attendee.Attendees
SET Log = i.Log
FROM attendee.Attendees a
JOIN Inserted i ON i.Id = a.Id
RETURN
END
END
-- ensure that every row has a user assigned for logging purposes
IF (SELECT COUNT(*) - COUNT(ModifiedBy) FROM Inserted) != 0
BEGIN
RAISERROR (N'Must specify a ModifiedBy for every modification of a record.', 10, 1)
RETURN
END
-- perform the actual data operations
IF (SELECT COUNT(*) FROM Deleted) = 0
BEGIN
-- insert new records
INSERT INTO Users (UserId, Username, Email, PasswordHash, IsDisabled, IsActivated, Address1, Address2, City, State, Country)
SELECT UserId, Username, Email, PasswordHash, IsDisabled, IsActivated, Address1, Address2, City, State, Country
FROM Inserted
END
ELSE
BEGIN
-- update existing records
UPDATE Users
SET
UserId = i.UserId,
Username = i.UserId,
Email = i.Email,
PasswordHash = i.PasswordHash,
IsDisabled = i.IsDisabled,
IsActivated = i.IsActivated,
Address1 = i.Address1,
Address2 = i.Address2,
City = i.City,
State = i.State,
Country = i.Country
FROM Users u
JOIN Inserted i ON i.Id = u.Id
END
-- create xml-compatible date string
DECLARE @date NVARCHAR(50)
SET @date = CONVERT(NVARCHAR(50), GETDATE(), 126) + '-07:00'
UPDATE Users
SET Log.modify('
declare namespace a="http://djeebus.net/schemas/xml-log";
insert
element a:entry
{
attribute type { if (empty(sql:column("d.UserId"))) then "insert" else "update" },
attribute date { xs:dateTime(sql:variable("@date")) },
attribute user { sql:column("i.ModifiedBy") }
attribute usertype { sql:column("i.ModifiedByType") },
if (not(empty(sql:column("i.Comment")))) then
attribute comment { sql:column("i.Comment") } else (),
if (not((empty(sql:column("d.Username")) and empty(sql:column("i.Username"))) or (sql:column("d.Username") eq sql:column("i.Username")))) then
element a:column { attribute name { "Username" }, text { xs:string(sql:column("i.Username")) } } else (),
if (not((empty(sql:column("d.PasswordHash")) and empty(sql:column("i.PasswordHash"))) or (sql:column("d.PasswordHash") eq sql:column("i.PasswordHash")))) then
element a:column { attribute name { "PasswordHash" }, text { xs:string(sql:column("i.PasswordHash")) } } else (),
if (not((empty(sql:column("d.Email")) and empty(sql:column("i.Email"))) or (sql:column("d.Email") eq sql:column("i.Email")))) then
element a:column { attribute name { "Email" }, text { xs:string(sql:column("i.Email")) } } else (),
if (not((empty(sql:column("d.IsDisabled")) and empty(sql:column("i.IsDisabled"))) or (sql:column("d.IsDisabled") eq sql:column("i.IsDisabled")))) then
element a:column { attribute name { "IsDisabled" }, text { xs:string(sql:column("i.IsDisabled")) } } else (),
if (not((empty(sql:column("d.IsActivated")) and empty(sql:column("i.IsActivated"))) or (sql:column("d.IsActivated") eq sql:column("i.IsActivated")))) then
element a:column { attribute name { "IsActivated" }, text { xs:string(sql:column("i.IsActivated")) } } else (),
if (not((empty(sql:column("d.Address1")) and empty(sql:column("i.Address1"))) or (sql:column("d.Address1") eq sql:column("i.Address1")))) then
element a:column { attribute name { "Address1" }, text { xs:string(sql:column("i.Address1")) } } else (),
if (not((empty(sql:column("d.Address2")) and empty(sql:column("i.Address2"))) or (sql:column("d.Address2") eq sql:column("i.Address2")))) then
element a:column { attribute name { "Address2" }, text { xs:string(sql:column("i.Address2")) } } else (),
if (not((empty(sql:column("d.City")) and empty(sql:column("i.City"))) or (sql:column("d.City") eq sql:column("i.City")))) then
element a:column { attribute name { "City" }, text { xs:string(sql:column("i.City")) } } else (),
if (not((empty(sql:column("d.State")) and empty(sql:column("i.State"))) or (sql:column("d.State") eq sql:column("i.State")))) then
element a:column { attribute name { "State" }, text { xs:string(sql:column("i.State")) } } else (),
if (not((empty(sql:column("d.Country")) and empty(sql:column("i.Country"))) or (sql:column("d.Country") eq sql:column("i.Country")))) then
element a:column { attribute name { "Country" }, text { xs:string(sql:column("i.Country")) } } else ()
}
as last into (/a:log)[1]')
FROM Users u
JOIN Inserted i ON a.Id = i.Id
LEFT JOIN Deleted d ON i.Id = d.Id
-- go back to counting the number of records affected
SET NOCOUNT OFF
GO
The only maintenance-intensive part is that every time you add a new column you’ll have to add it to the update statement and the list of new elements at the bottom of the query, but I think the trade off is well worth getting fool-proof logging done properly.
For more information on the .modify() method (and on generaly using xml in sql server 2008) see http://msdn.microsoft.com/en-us/library/ms187093.aspx.