After cooresponding with mcerkez88 I took his idea and implemented it using triggers. Attached is a text file that contains the DDL to create the "Audit" table, a trigger to prevent editing the audit table by most users/process, along with the definition of a simple table from my database and the trigger attached to it that logs all changes to the table in the audit table.

Prior to this I had to have a seperate audit table for each table I was auditing and keeping all the changes in sync between the "audited" and "audit" tables was a bit of a pain, but it did work. Based on the new design I need only a single audit table with views into the xml to retrieve the information I'm interested in. Performance seems to be excellent, though I have not stressed the system as we only have about 50 users at any one time on the system where I'm using this, so I make no warranties in that respect 🙂

I hope someone finds the attached file usefull/interesting and I'm interested in all constructive comments/suggestions. I don't consider myself an expert in any of this and am always willing to learn a new technique or be corrected if I've done something stupid.

One comment on maintaining my triggers, I use Powerdesigner from Sybase to maintain my DB Model and with it's built in scripting it does an excellent job of keeping my triggers up-to-date if I change a table name or make some other modification that would result in breaking the trigger.