Help, I a bit stuck.
I'm trying to create a trigger that will launch on changes to the data in a table called tblContacts.

It should record the changes in a table called tblAudit

I've tried to make the code so it checks all fields (columns) in the tblContacts table.


The problem I'm having is I can't work out how to temporarily store the data in a variable (see "-- Get the old and new values" in the code below).

Any ideas?

ALTER TRIGGER trgContactsUpdate

ON tblContacts

FOR UPDATE

AS


DECLARE @tmp_Col_Name VARCHAR(255)
DECLARE @tmp_OldVal sql_variant
DECLARE @tmp_NewVal sql_variant
DECLARE @tmp_RecordID int

DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblContacts'
ORDER BY ORDINAL_POSITION

OPEN column_cursor


FETCH NEXT FROM column_cursor into @tmp_Col_name

-- Check each column for changes
WHILE @@FETCH_STATUS = 0
BEGIN

-- Get the old and new values
select @tmp_OldVal = exec('select top 1 ' + @tmp_Col_name + ' from Deleted ')
set @tmp_NewVal = exec('select top 1 ' + @tmp_Col_name + ' from Inserted ')

-- Get the record ID
select @tmp_RecordID = ( select irpContact from Inserted )

-- If there have been changes in the data...
IF @tmp_OldVal != @tmp_NewVal

BEGIN

-- Record the change in the Audit Table
INSERT INTO tblAudit
(dwoEditDate,swoUser,swoRecordID,swoSourceTable,sw oSourceField,swoBeforeValue,swoAfterValue)
VALUES
(GETUTCDATE(),'user',@tmp_RecordID ,'tblCOntacts',@tmp_Col_name,@tmp_OldVal,@tmp_NewV al)
END

FETCH NEXT FROM column_cursor into @tmp_Col_name
END

CLOSE column_cursor
DEALLOCATE column_cursor

Here is the code I use for Table Audits. It works pretty flawlessly:
CREATE TRIGGER trg_tblName_Audit -- Change this name accordingly reflective of your trigger
------------------------------------------------------------------------------------------------------------
--&
--& Name: trg_Pool_ID_Audit
--&
--& Purpose: This trigger audit trails all changes made to a table.
--& It will place in Tbl_Audit_Trans_Log all inserted, deleted, changed columns in the table on
--& which it is placed. It will put out an error message if there is no primary key on the
--& table, so if your table does not contain a primary key, either put one on, create an
--& identity field and join it with a meaningful field to create a PK, or don't put this audit
--& trigger on the table.
--&
--& This will track:
--& * Type of change,(I)nsert, (U)pdate or (D)elete
--& * Table affected
--& * Primary Key values for the row affected
--& * Field name being affected
--& * Previous value
--& * New Value
--& * Date/Time change was done
--& * User name of the account making the change
--& * Name of the program executing the change
--& * MacAddress of the machine the change was made from
--&
--& Author: Davis C Henely (... and samples from the web)
--&
--& Created: 02/08/2006
--&
--& Usage: This is a general trigger that can be used on any table. The tracking table, Tbl_Audit_Trans_Log
--& will need to be recreated in any database where tables need to be tracked. You cannot have
--& changes in a table on DB XYZ tracked in the Audit table in DB ABC. You must create a
--& Tbl_Audit_Trans_Log table in DB XYZ as well.
--&
--& *****!!!!! You will need to change @TableName to match the table to be audit trailed
--&
--& Updates:
--&
------------------------------------------------------------------------------------------------------------

ON Tbl_Pool_ID -- Make sure this table name matches ...
FOR UPDATE, DELETE
AS

SET NOCOUNT ON

DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@UpdateDate VARCHAR(50) ,
@UserName VARCHAR(128) ,
@Type CHAR(1) ,
@PKSELECT VARCHAR(1000),
@macaddr VARCHAR(50),
@ProgName VARCHAR(100)

SELECT @TableName = 'tblName' -- ... this table name from above and is the name of your table

-- date AND user
SELECT @UserName = system_user ,
@UpdateDate = CONVERT(VARCHAR(50), GETDATE(), 109)

-- Get the user's network address from where the changes came from and the name of the app they were using
SELECT @macaddr = LEFT(LTRIM(RTRIM(CONVERT(VARCHAR(100), sys.net_address))),25) ,
@ProgName = LEFT(LTRIM(RTRIM(CONVERT(VARCHAR(100), sys.program_name))),25)
FROM master.dbo.sysprocesses sys (nolock)
WHERE sys.spid = @@spid
-- AND hostname NOT IN ('corp-db-01', ' ')

-- Action
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'

-- get list of columns
SELECT * into #ins FROM inserted
SELECT * into #del FROM deleted

-- Get primary key columns for FULL OUTER JOIN
SELECT @PKCols = COALESCE(@PKCols + ' AND', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key SELECT for insert
SELECT @PKSELECT = COALESCE(@PKSELECT+'+','') + '''[' + COLUMN_NAME + '=''+convert(VARCHAR(100),COALESCE(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+'']'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

IF @PKCols IS NULL
BEGIN
RAISERROR('No PK on table %s', 16, -1, @TableName)
RETURN
END

SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
while @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type in ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @field
SELECT @fieldname = '[' + @fieldname + ']'
SELECT @sql = 'INSERT Tbl_Audit_Trans_Log (TransType, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName, ProgramName, MacAddress)'
SELECT @sql = @sql + ' SELECT ''' + @Type + ''''
SELECT @sql = @sql + ',''' + @TableName + ''''
SELECT @sql = @sql + ',' + @PKSELECT
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(VARCHAR(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(VARCHAR(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ',''' + @ProgName + ''''
SELECT @sql = @sql + ',''' + @macaddr + ''''
SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @sql = @sql + @PKCols
SELECT @sql = @sql + ' WHERE i.' + @fieldname + ' <> d.' + @fieldname
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' IS NOT NULL)'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' IS NULL)'
EXEC (@sql)
END
END

SET NOCOUNT OFF

Here is the structure of the Audit table:
CREATE TABLE [tbl_Audit_Trans_Log] (
[TransType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TableName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PK] [varchar] (600) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FieldName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldValue] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdateDate] [datetime] NOT NULL ,
[UserName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProgramName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MacAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Tbl_Audit_Trans_Log] PRIMARY KEY CLUSTERED
(
[TableName],
[PK],
[FieldName],
[UpdateDate]
) ON [PRIMARY]
) ON [PRIMARY]
GO