This script is fantastic! But I have one question. If I wanted a Primary Key on the Audit table, how would I need to amend your script to include this? I would like it called AUDIT_##YOUR_TABLE##ID. I'm pretty new at all this and would appreciate your assistance.

just so I'm clear on this, will this record old value and new value? I need to setup an audit table to record when a row is updated, and need it to record what column or columns were changed, and what the old and new values were, and who updated it. does this trigger accomplish that?

I would add just two other variables that can be quite helpful for auditing - user name and machine name. I updated the SQL to do this:

-- ADD THE AUDIT FIELDS SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE,CURRENT_USER AS TRG_USER, HOST_NAME() AS TRG_PC' -- SET UP THE SELECT FOR CREATING THE AUDIT TABLE SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_PAYMENT] FROM [DBO].[PAYMENT]' EXEC(@SQLSTR)END

Hi, yes this is a real conundrum. There are several limitations in SQL Server that make this impossible:

1. You can't refer in any way to text, ntext or image fields in the inserted or deleted tables.2. You can't refer to the inserted or deleted tables in dynamic sql which means you can't build a select statement excluding the forbidden columns.

Can anyone see a way around this without having to explicitly name columns (which would render the solution non-generic)?