SQL-Server Trigger on update for Audit

Question

I can't find an easy/generic way to register to an audit table the columns changed on some tables.

I tried to do it using a Trigger on after update in this way:

First of all the Audit Table definition:

CREATE TABLE [Audit](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NOT NULL default GETDATE(),
[IdTypeAudit] [int] NOT NULL, --2 for Modify
[UserName] [varchar](50) NULL,
[TableName] [varchar](50) NOT NULL,
[ColumnName] [varchar](50) NULL,
[OldData] [varchar](50) NULL,
[NewData] [varchar](50) NULL )

Next a trigger on AFTER UPDATE in any table:

DECLARE 
    @sql varchar(8000),
    @col int,
    @colcount int

select @colcount = count(*) from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable'
set @col = 1

while(@col < @colcount )
begin

    set @sql=
    'INSERT INTO Audit
    SELECT 2, UserNameLastModif, ''MyTable'', COL_NAME(Object_id(''MyTable''), '+ convert(varchar,@col) +'), Deleted.' 
    + COL_NAME(Object_id('MyTable'), @col) + ', Inserted.' + COL_NAME(Object_id('MyTable'), @col) + '
    FROM Inserted LEFT JOIN Deleted ON Inserted.[MyTableId] = Deleted.[MyTableId]
    WHERE COALESCE(Deleted.' + COL_NAME(Object_id('MyTable'), @col) + ', '''') <> COALESCE(Inserted.' + COL_NAME(Object_id('MyTable'), @col) + ', '''')'

    --UserNameLastModif is an optional column on MyTable
    exec(@sql)
    set @col = @col + 1

end

The problems

1. Inserted and Deleted lost the context when I use the exec function
2. Seems that colnumber it isn't always a correlative number, seems if you create a table with 20 columns and you delete one and create another, the last one have a number > @colcount

I was looking for a solution for all over the net but I couln't figure out

Any Idea?

Thanks!

Answer 1

@Santiago : If you still want to write it in dynamic SQL, you should prepare all of the statements first then execute them. 8000 characters may not be enough for all the statements. A good solution is to use a table to store them.

IF NOT OBJECT_ID('tempdb..#stmt') IS NULL
    DROP TABLE #stmt; 
CREATE TABLE #stmt (ID int NOT NULL IDENTITY(1,1), SQL varchar(8000) NOT NULL); 

Then replace the line exec(@sql) with INSERT INTO #stmt (SQL) VALUES (@sql);

Then exec each row.

WHILE EXISTS (SELECT TOP 1 * FROM #stmt)
BEGIN
    BEGIN TRANSACTION; 
        EXEC (SELECT TOP 1 SQL FROM #stmt ORDER BY ID); 
        DELETE FROM #stmt WHERE ID = (SELECT MIN(ID) FROM #stmt); 
    COMMIT TRANSACTION; 
END

Remember to use sys.columns for the column loop (I shall assume you use SQL 2005/2008).

SET @col = 0; 
WHILE EXISTS (SELECT TOP 1 * FROM sys.columns WHERE object_id = OBJECT_ID('MyTable') AND column_id > @col) 
BEGIN
    SELECT TOP 1 @col = column_id FROM sys.columns 
    WHERE object_id = OBJECT_ID('MyTable') AND column_id > @col ORDER BY column_id ASC; 
    SET @sql ....
    INSERT INTO #stmt ....
END

Remove line 4 @colcount int and the proceeding comma. Remove Information schema select.

Answer 2

DO not ever use any kind of looping a trigger. Do not use dynamic SQl or call a stored proc or send an email.All of these things are exretemly inappropriate in a trigger.

If tyou want to use dynamic sql use it to create the script to create the trigger. And create an audit table for every table you want audited (we actually have two for every table) or you will have performance problems due to locking on the "one table to rule them all".

Answer 3

This highlights a greater problem with structural choice. Try to write a set-based solution. Remove the loop and dynamic SQL and write a single statement that inserts the Audit rows. It is possible but to make it easier consider a different table layout, like keeping all columns on 1 row instead of splitting them.

In SQL 2000 use syscolumns. In SQL 2005+ use sys.columns. i.e.

SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID(DB_NAME()+'.dbo.Table');