Nice example, David. I developed a complimentary set of audit functionality that audits data changes themselves a couple years ago for SQL Server 2000. I ported it to SQL Server 2005, and would like to generalize the approach and clean up the implementation, but I understand that data-audit functionality ("Change Data Capture") is a built-in feature in 2008. So, I suspect that the life of my little custom tool for auditing data changes is near it's end.

However, I am most interested in your follow-up example. The database I wrote data change auditing for has several hundred tables. So, automating the trigger generating code was a key challenge I faced, and the code I came up with to generate the data change audit triggers was somewhat complex and cumbersome. Since you are going to conquer a similar problem in generating these triggers, and you have hinted that you are going to use XML, it sounds like you are going to solve the same problem using a different approach. I am quite curious to say the least!

Simon Taylor