Trigger to maintain a basic audit trail of changes to our Users table using PIVOTs instead of yucky, slow dynamic SQL.

change_log.sql

-- Trigger to maintain a log of changes to the Users table (T-SQL, SQL Server 2005)
-- Presently only tracking UPDATEs
-- Inspiration from: http://goo.gl/Geds
IF OBJECT_ID('tr_ChangeLogOnUsers', 'TR') IS NOT NULL
	DROP TRIGGER tr_ChangeLogOnUsers;
GO

CREATE TRIGGER tr_ChangeLogOnUsers ON Users FOR UPDATE
AS
-- only when LastModifiedBy is changed (someone made a change vs. something)
IF( UPDATE(LastModifiedBy) )
BEGIN
	DECLARE @ChangeSet VARCHAR(36),	-- group changes when bulk updates happen
		@ChangeBy VARCHAR(255),	-- whodunit
		@ChangeAt DATETIME,	-- when at
		@ChangeType CHAR(1); 	-- (U)PDATE
	
	SELECT @ChangeAt = GETDATE(), @ChangeSet = NEWID(), @ChangeType = 'U'

	-- LastModifiedBy is how the trigger knows who did this change (since our SYSTEM_USER never changes)
	SELECT TOP 1 @ChangeBy = LastModifiedBy FROM inserted

	DECLARE @Ins TABLE (PrimaryKeyValue INT, ColumnName VARCHAR(MAX), ColumnValue sql_variant NULL);
	DECLARE @Del TABLE (PrimaryKeyValue INT, ColumnName VARCHAR(MAX), ColumnValue sql_variant NULL);
	
	-- unpivot `inserted` rows to: (PrimaryKeyValue, ColumnName, ColumnValue)
	-- column values must all be of the same type, and we are only monitoring some columns
	INSERT INTO @Ins (PrimaryKeyValue, ColumnName, ColumnValue)
	SELECT PrimaryKeyValue, ColumnName, ColumnValue
	FROM
		(SELECT UserID as PrimaryKeyValue,
		CAST(FirstName as sql_variant) as FirstName,
		CAST(MiddleName as sql_variant) as MiddleName,
		CAST(LastName as sql_variant) as LastName,
		CAST(City as sql_variant) as City,
		CAST(ProvinceState as sql_variant) as ProvinceState,
		CAST(Country as sql_variant) as Country,
		CAST(Active as sql_variant) as Active
		FROM inserted) p
	UNPIVOT
		(ColumnValue FOR ColumnName 
		IN (FirstName, MiddleName, LastName, 
		    City, ProvinceState, Country, Active)
	) as unpvt;

	-- do the exact same thing for the `deleted` table
	INSERT INTO @Del (PrimaryKeyValue, ColumnName, ColumnValue)
	SELECT PrimaryKeyValue, ColumnName, ColumnValue
	FROM
		(SELECT UserID as PrimaryKeyValue,
		CAST(FirstName as sql_variant) as FirstName,
		CAST(MiddleName as sql_variant) as MiddleName,
		CAST(LastName as sql_variant) as LastName,
		CAST(City as sql_variant) as City,
		CAST(ProvinceState as sql_variant) as ProvinceState,
		CAST(Country as sql_variant) as Country,
		CAST(Active as sql_variant) as Active
		FROM deleted) p
	UNPIVOT
		(ColumnValue FOR ColumnName 
		IN (FirstName, MiddleName, LastName, 
		    City, ProvinceState, Country, Active)
	) as unpvt;
	
	-- now join inserted and deleted to get the Before and After Values, and dump them to our Log
	INSERT INTO ChangeLog
		(ChangeSet, ChangeAt, ChangeBy, ChangeType, 
		TableName, PrimaryKeyName, PrimaryKeyValue, FieldName, BeforeValue, AfterValue)
	SELECT 	@ChangeSet, @ChangeAt, @ChangeBy, @ChangeType,
		'Users', 'UserID', 
		COALESCE(i.PrimaryKeyValue, d.PrimaryKeyValue) as PrimaryKeyValue,
		COALESCE(i.ColumnName, d.ColumnName) as FieldName,
		CONVERT(VARCHAR(255), d.ColumnValue) as BeforeValue,
		CONVERT(VARCHAR(255), i.ColumnValue) as AfterValue
	FROM @Ins i FULL OUTER JOIN @Del d
	    ON  i.ColumnName = d.ColumnName and i.PrimaryKeyValue = d.PrimaryKeyValue
	WHERE 	COALESCE(i.ColumnValue, '@NULLVALUE@') <> COALESCE(d.ColumnValue, '@NULLVALUE@')
END;
GO