mcl (5/18/2009)
A great concept, excellently packed and ready for use out-of-the-box.Reading the article sparked off an idea but, as I'm relatively inexperienced in working with SQL server & XML, I'm not sure if this would be possible. Anyway here is the idea.
In applications that I have worked on there has been a need, especially in the area of master data (codes, descriptions, customers account information etc.), to record who changed what data and when. Something that can take as much effort to do as to maintain the information itself.
Extending the data collected to include the who & the when will allow the rollback, rollforward of data on a user basis and solve the business auditing need in one and the same solution.
I'm assuming that the system tim & the logged on user (not necessarily the db user) are available as either variables or function calls.
To me, this almost seems too good to be true. There must be a catch somewhere or what?
To include the Who and the When is trivial - you can just add columns with default values to the audit table. (e.g. getdate() and suser_sname).
What I provided is the core of the solution - and while it does quite a bit 'out of the box' you'll still have to adapt / interpret and apply in order to get it working for your situation.
Is there a catch? Yes, probably - although in certain circumstances it might be just what you're looking for. The basic 'issues' are principally (1) that the trigger is quite chunky, and so may take as much time / resources as your operation (2) Everything here is auditing to the same table so there may be competition to insert rows.
Essentially you've got to consider the possibility that the auditing may bring your database to a halt, if you're not careful. But either for specific tables or for a database with few updates, there may be no problem at all.
Regards,
David.