Posted by Erland Sommarskog on 01/15/07 22:57

Rico (you@me.com) writes:
> I'm creating an audit table and associated triggers to be able to
> capture any updates and deletes from various tables in the database. I
> know how to capture the records that have been updated or deleted, but
> is there any way that I can cycle through a changed record, look at the
> old vs new values and capture only the values that have changed?

In a word: don't do it.

Don't implment your own audit solution, when there are third-party solutions
around. For instance have a look at
http://www.apexsql.com/sql_tools_audit.asp-

No, I have not used that tool, so I cannot vouch for whether it is good
or not. But to be frank: if you have to ask how to write such an audit
trigger, what are the odds that you would do it better?

In order to "cycle through the columns" at run-time you would have to use
dynamic SQL, and dynamic SQL comes with permissions problems. And it would
be grossly ineffective. The correct way to go would be to write a program
that generates a trigger that checks all columns individually. For tables
with many columns this could still be less effecient that just saving the
entire row to the audit table.

Finally, I should say that an alternative to using triggers for auditing
is to use the transaction log. There are several log readers out there. I
see that ApexSQL has one. Red Gate has another. And, of course, Lumigent
who implemented the first log reader are still in business. They also
have a more versatile audit tool biuld on top of their log reader.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]