Really helpful. THANKS for posting this.

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

IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_PAYMENT] SELECT *,'INSERT' ,GETDATE(),CURRENT_USER,HOST_NAME() FROM INSERTED

IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_PAYMENT] SELECT *,'DELETE' ,GETDATE(),CURRENT_USER,HOST_NAME() FROM DELETED

IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_PAYMENT] SELECT *,'UPDATE' ,GETDATE(),CURRENT_USER,HOST_NAME() FROM INSERTED