David McKinney (5/18/2009)
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.
I've just noted that I didn't sign my comment, please accept my apologies.
As with the catches, I'd already got my solution orientated blinkers on. You're absolutely right it would potentially require lots of hardware to fully audit an OLTP system database, I was looking at the solution for low change Master Data tables, which should keep database resource use and contention within the bounds of acceptability.
Best regards
Mik