the following may help you on your way

/*This provides code to create a trigger on any table to record changes in the data

the changes made are recorded in a single "Audit" table

Only columns that have been updated are recorded IF THE TRIGGER IS SET TO UPDATE ONLY

If the trigger is used for INSERT/DELETE then every column in the table will create a new row in the AUDIT table

YOU HAVE BEEN WARNED !!

Personally I only use this for auditing changes to static/master data tables (eg products/customers/suppliers etc)

and not for any transactional type tables.

Note that there will be an overhead.....but I will leave that for testing in your own environment

The original code was found on the web http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html.

*/

/*start in a safe place !!!!*/

use [tempdb]

GO

IF OBJECT_ID('tempdb..TransData', 'U') IS NOT NULL

DROP TABLE tempdb..TransData;

IF OBJECT_ID('tempdb..Audit', 'U') IS NOT NULL

DROP TABLE tempdb..Audit;

/* create the audit table.

There will only need to be one of these in a database

will be updated from any table with the trigger below*/

CREATE TABLE Audit (

Type CHAR(1),

TableName VARCHAR(128),

PK VARCHAR(1000),

ColumnName VARCHAR(128),

OldValue VARCHAR(1000),

NewValue VARCHAR(1000),

UpdateDate DATETIME,

UserName VARCHAR(128),

AppName VARCHAR(128)

)

GO

/*create some test data*/

SELECT TOP 100000

TranID = IDENTITY(INT, 1, 1),

CustomerID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),

Trans_type = CHAR(Abs(Checksum(Newid())) % 2 + 65)

+ CHAR(Abs(Checksum(Newid())) % 3 + 65)

+ CHAR(Abs(Checksum(Newid())) % 7 + 65),

ProdID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),

Sales_Amount= CAST(Rand(Checksum(Newid())) * 99 + 1 AS DECIMAL(5, 2)),

Trans_Date = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2011', '2012'), '2011')

INTO TransData

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3

/*note that for this audit trigger to work there must be a primary key on the table*/

ALTER TABLE [dbo].[TransData] ADD CONSTRAINT [PK_TransData] PRIMARY KEY CLUSTERED ([TranID] ASC)

GO

/*create the trigger . This has to be created on every table you want to monitor

*/

CREATE TRIGGER [dbo].[transdata_Audit]

ON [dbo].[TransData]

FOR

--INSERT, ---uncomment if required

--DELETE, ---uncomment if required

UPDATE

AS

DECLARE @bit INT

, @field INT

, @maxfield INT

, @char INT

, @ColumnName VARCHAR(128)

, @TableName VARCHAR(128)

, @PKCols VARCHAR(1000)

, @sql VARCHAR(2000)

, @UpdateDate VARCHAR(21)

, @UserName VARCHAR(128)

, @Type CHAR(1)

, @PKSelect VARCHAR(1000)

, @PKField VARCHAR(1000)

, @AppName VARCHAR(128)

/*IMPORTANT You will need to change @TableName to match the table to be audited*/

SELECT @TableName = 'transdata'

/* date - user - application*/

SELECT @UserName = SYSTEM_USER

, @AppName = APP_NAME()

, @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

/* 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 columns ---jls*/

SELECT @PKField = COALESCE(@PKField + '+', '') + ''''

+ '''+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 @ColumnName = COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

AND ORDINAL_POSITION = @field

SELECT @sql = '

insert Audit (

Type,

TableName,

PK,

ColumnName,

OldValue,

NewValue,

UpdateDate,

UserName,

Appname)

select ''' + @Type + ''',''' + @TableName + ''',' + @PKField + ','''

+ @ColumnName + '''' + ',convert(varchar(1000),d.'

+ @ColumnName + ')' + ',convert(varchar(1000),i.'

+ @ColumnName + ')' + ',''' + @UpdateDate + ''''

+ ',''' + @UserName + ''',''' + @Appname + ''''

+ ' from #ins i full outer join #del d' + @PKCols

+ ' where i.' + @ColumnName + ' <> d.' + @ColumnName

+ ' or (i.' + @ColumnName + ' is null and d.'

+ @ColumnName + ' is not null)' + ' or (i.'

+ @ColumnName + ' is not null and d.' + @ColumnName

+ ' is null)'

EXEC ( @sql )

END

END

/*trigger end*/

GO

/*=======================================================*/

/* do some updates*/

/*=======================================================*/

UPDATE TransData

SET Trans_type = 'jls',

Sales_Amount = Sales_Amount * 1.2

WHERE (TranID % 1000 = 0)

SELECT * FROM Audit

order by tableName,cast(PK AS INT),ColumnName;

/*TRUNCATE TABLE Audit;*/