I came across a really interesting audit trigger for SQL Server 2005 a little while back, and thought it was worth re-posting it here.
The trigger basically logs changes for only columns that are affected into a generic audit table. It's got some fancy bit manipulation coding (that I still don't fully understand) and uses dynamic SQL, but hey, since I normally stay away from triggers anyway, I can accept a little dynamic SQL :-)
The alternative is to have an audit table that closely resembles my real table and log changes to that. And then run queries to find what was changed, and when. And then do this for every table I want to audit. And then...you get the picture.
You can find this amazing trigger at Pop Rivett's SQL Server FAQ No.5: Pop on the Audit Trail. It met my needs perfectly, but it may not meet yours: it's worth testing the impact of dynamic SQL and the effect if you use @@IDENTITY after inserts.
Links: http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/
Tags: sql server, audit, log, trigger
posted @ Tuesday, July 22, 2008 9:20 PM