How to log only updated columns for audit table in SQL Server (using Update Trigger)?

Keeping track of all updates for audit log table is common practice. But the question is how we will keep track only those values which have been updated. It is wise decision to capture only updated columns instead of capturing all the columns unnecessary.

We can do so by creating “Update Trigger” in SQL Server. SQL Server provides “COLUMNS_UPDATED()” function to get those. You can learn more about this function from msdn : http://msdn.microsoft.com/en-us/library/ms186329.aspx

I have used temporary table to hold deleted and inserted values for old and new changes. Use of temporary table will not be a problem of overridden values by running simultaneous firing of trigger because they are scope sensitive to the procedure that created them and will remove on their own.

I have created “AuditLog_TestTable” just with one column which is “ModifiedFields” column to represent how it works. So here is the complete Trigger which will log only updated columns in audit table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TestTable_Upon_Update]
ON [dbo].[TestTable]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql as NVARCHAR(max);

-- Temp table for Inserted/Deleted
SELECT * INTO #tempInserted FROM Inserted;
SELECT * INTO #tempDeleted FROM Deleted;

SELECT @sql = COALESCE(@sql + ',' + QUOTENAME(COLUMN_NAME), QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE ( SUBSTRING(COLUMNS_UPDATED(), 
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME, 'U'),
COLUMN_NAME, 'columnId') / 8 + 1 , 1) & 
POWER(2, -1 + COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME, 'U'),
COLUMN_NAME, 'columnId') % 8 ) > 0 
OR ( (COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME, 'U'),
COLUMN_NAME, 'columnId') % 8 = 0) AND (SUBSTRING(COLUMNS_UPDATED(), 
(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME, 'U'),
COLUMN_NAME, 'columnId') / 8) , 1) & POWER(2,(8-1))) > 0 ) )
AND TABLE_NAME = 'TestTable' 

-- and column_name in ('c1', 'c2') 
-- limit to specific columns 
-- and column_name not in ('c3', 'c4') -- or exclude specific columns 

SET @sql = ' DECLARE @x as NVARCHAR(max); 
SET @x = ''OLD:'' + 
(SELECT ' + @sql + ' FROM #tempDeleted FOR XML RAW); 
SET @x = @x + ''NEW:'' + 
(SELECT ' + @sql + ' FROM #tempInserted FOR XML RAW); 
INSERT INTO [dbo].[AuditLog_TestTable] 
([ModifiedFields]) VALUES (@x);'; 

EXECUTE (@sql); 
EXECUTE ('DROP TABLE #tempInserted; DROP TABLE #tempDeleted;'); 

END 

Above trigger steps are following:
1. I am storing “Inserted/Deleted” record into temp table.
2. Then, setting @sql variable with only updated columns.
3. Then, getting XML formatted updated columns and it's value to insert into auditLog table
4. Finally, Inserting into auditLog table and dropping #temp tables.

Your comments are most welcome, I hope this can help you.