Update Audit Table

Question

Update Audit Table

scotsditch

Ten Centuries

Points: 1020
More actions
October 13, 2014 at 9:21 am

#294116

Hi, I have some stored procedures that delete records, or update column values for records based on input parameters.
I'd like to set something up so that when I run my stored proc it updates an audit table.
Any tips very much appreciated.
October 14, 2014 at 4:50 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

You can use triggers on your tables. However I'd refactor my procs to add a second update (in the same transaction) that updates the audit table with values.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

Quick thought, use the OUTPUT clause in the stored procedure to insert into the audit table.

😎

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

The problem with adding to procs to "update" the audit table is that if the audit table changes, you must find and update all those procs or, perhaps, managed code. You also won't capture ad hoc changes to the audited tables made by folks that either don't remember to audit the changes or don't want the changes to be audited, which is against the nature of doing auditing to begin with.

This IS what triggers do best. Use triggers to do the auditing. Learn to write good, high performance, set-based triggers and they'll be just as effective as if you remembered to add audit code to every proc that affects the tables being audited.

If you think that some form of "generic" or "self-healing" trigger, especially CLR triggers of such a nature, will provide any reasonable performance, you're dead wrong because, since the INSERTED and DELETED tables go out of scope so easily, copies of both will need to be made by such triggers and that's going to make life real difficult for performance. Write good, hard coded triggers and maintain them. Or, do what I did... write a proc that writes the hardcoded trigger for a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

Jeff Moden (10/14/2014)
The problem with adding to procs to "update" the audit table is that if the audit table changes, you must find and update all those procs or, perhaps, managed code. You also won't capture ad hoc changes to the audited tables made by folks that either don't remember to audit the changes or don't want the changes to be audited, which is against the nature of doing auditing to begin with.
This IS what triggers do best. Use triggers to do the auditing. Learn to write good, high performance, set-based triggers and they'll be just as effective as if you remembered to add audit code to every proc that affects the tables being audited.
If you think that some form of "generic" or "self-healing" trigger, especially CLR triggers of such a nature, will provide any reasonable performance, you're dead wrong because, since the INSERTED and DELETED tables go out of scope so easily, copies of both will need to be made by such triggers and that's going to make life real difficult for performance. Write good, hard coded triggers and maintain them. Or, do what I did... write a proc that writes the hardcoded trigger for a table.

Yes and no here. You can certainly standarize what is audited (who, what, when, data) and use a generic proc to do this, and store it in all your update/insert/delete procs.

Note that if audit data changes, you're changing a lot of triggers as well, so I'm not sure I agree with this.