I need to implement change tracking on two tables in my SQL Server 2005 database. I need to audit additions, deletions, updates (with detail on what was updated). I was planning on using a trigger to do this, but after poking around on Google I found that it was incredibly easy to do this incorrectly, and I wanted to avoid that on the get-go.

Can anybody post an example of an update trigger that accomplishes this successfully and in an elegant manner? I am hoping to end up with an audit table with the following structure:

• ID
• LogDate
• TableName
• TransactionType (update/insert/delete)
• RecordID
• FieldName
• OldValue
• NewValue

... but I am open for suggestions.

Thanks!