Add a LastModified column to SQL Server table

I had a job recently where I needed to find the delta from a number of Microsoft Access database tables. The db designer had made a lot of interesting architectural decisions, but the biggest problem I had was finding which records had been touched in the past few months. It is easy enough to detect the changed tables, but at a field level I had to run a diff of the old and new copies and extract the changes using DiffMerge.

This project served as a reminder that it always helps to include audit trails, so when I was building my latest staging area database in SQL Server, I made sure to add CreatedBy, CreatedDate, LastModifiedBy and LastModifiedDate fields to every table.

First, create the table then use ALTER TABLE to add the audit fields:

ALTER TABLE Database1.dbo.Table1
ADD DateCreated DATETIME DEFAULT GETDATE()
,CreatedBy nvarchar(255)
,LastModified DATETIME
,LastModifiedBy nvarchar(255)

Then create three triggers to INSERT the creating user, UPDATE the editing user, and UPDATE the edited date. I have included the LastModified update trigger as an example:

USE Database1;
GO
CREATE TRIGGER Edited
ON dbo.Table1
FOR UPDATE
AS
BEGIN
IF NOT UPDATE(LastModified)
UPDATE dbo.Table1 SET LastModified=GETDATE()
WHERE UniquePK IN (SELECT UniquePK FROM inserted)
END
GO

To UPDATE or INSERT the creator or editor use:
SET LastModifiedBy=USER_NAME(USER_ID())