Change Trigger - Log all data changes in a table

I needed a MSSQL trigger to store alterations to data in a table called tblContacts. All changes (i.e. the before & after values) needed to be stored in a table that logs all changes from various tables.

tblAudit Structure

irpEditRecordID (int)
dwoEditDate (datetime)
swoUser (nvarchar)
cwoAction (char)
swoRecordID (nvarchar)
swoSourceTable (nvarchar)
swoSourceField (nvarchar)
swoBeforeValue (sql_variant)
swoAfterValue (sql_variant)

This is a bit quick and nasty, and probably a bit slow if you've got a table that really gets hammered but it suits my needs. The trigger below will spin through all available fields looking for changes and log them in tblAudit. The payoff is I don't need to remember to update the trigger if I add new fields!

tblContacts Update Trigger

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- Change the trigger name below to reflect your table name
CREATE TRIGGER trg_tblContacts_Update

-- Change the table name below to reflect the table you want to audit
ON dbo.tblContacts

FOR UPDATE

AS

DECLARE @sField    VARCHAR(255)
DECLARE @sTable    VARCHAR(255)
DECLARE @sPrimaryKey  VARCHAR(255)
DECLARE @sSQL    VARCHAR(3000)

-- SETTINGS --
-- Change the table name below to reflect the table you want to audit
SET @sTable = 'tblContacts'
-- Change the primary key below to reflect the primary key field for table you want to audit
SET @sPrimaryKey = 'irpContact'

-- INIT --
SET @sSQL = ''

SELECT  * INTO #temp_inserted FROM inserted;
SELECT  * INTO #temp_deleted FROM deleted;


DECLARE fields_cursor CURSOR FOR 
 SELECT column_name FROM information_schema.columns
  WHERE table_name = @sTable
 ORDER BY ordinal_position
OPEN fields_cursor

-- CHECK CHANGES TO DATA IN EACH FIELD --

FETCH NEXT FROM fields_cursor INTO @sField

WHILE @@FETCH_STATUS = 0
BEGIN

 -- Find and log changes in tblAudit
 SET @sSQL = ' INSERT INTO tblAudit  (dwoEditDate,swouser,cwoAction,swoRecordID,swoSourceTable,swoSourceField, swoBeforeValue,swoAfterValue) ( select GETUTCDATE() as calc_datestamp, system_user as calc_username, ''A'' as calc_status, #temp_inserted.' + @sPrimaryKey + ', ''' + @sPrimaryKey + ''' as calc_TableName, ''' + @sField + ''' as calc_fieldname,  #temp_deleted.' + @sField + ', #temp_inserted.' + @sField + ' from #temp_inserted , #temp_deleted  where (#temp_deleted.' + @sPrimaryKey + ' = #temp_inserted.' + @sPrimaryKey + ') AND (#temp_inserted.' + @sField + ' != #temp_deleted.' + @sField + ' ) ) '
 
--PRINT @sSQL
 EXEC(@sSQL)

 FETCH NEXT FROM fields_cursor INTO @sField
END

CLOSE fields_cursor
DEALLOCATE fields_cursor



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO