Dave Britten

I find myself often needing to have some kind of audit trail for a table in SQL Server, i.e. some way to look at before-and-after data, who changed it, when, etc. I've set up enough of these manually that they were becoming formulaic, so I figured why not put together a procedure that creates them for me and writes the necessary trigger?

Attached is the version I'm currently using. The syntax is pretty simple. Here's an example, a real one that I just set up:

EXEC sp_CreateDataAudit
        'dbo',
        'IV00200',
        'audit',
        'IV00200',
        NULL,
        'tr_IV00200_Audit',
        'SERLNMBR,UNITCOST,RCTSEQNM ...

If you're a DBA, especially one that has to deal with an ERP system that has no concept of silly things like "transactions", then you've probably got a collection of scripts to update and fix data as necessary.

And you probably (hopefully) run a SELECT before actually changing data, to visually confirm what will happen. You might do this by either having both a SELECT and UPDATE query in the script, or by commenting out the SELECT or UPDATE portions of the script as needed, or some other technique.

Here's an approach I've used for a ...

Triggers are an indispensible feature of SQL Server. Essentially stored procedures that are invoked automatically, a trigger can be used to perform logging, data validation and cleaning, synchronization, and notification, and implement referential integrity constraints that are too complicated for foreign keys, among, no doubt, plenty of other things.

But alas, any seasoned SQL Server DBA is probably familiar with the minefield of performance-related and other issues that can very easily stem from misuse or overuse of triggers. I've frequently been victim to (and perpetrator of) such issues myself.

So what does it take to write a good, well-behaved ...