David Ziffer (10/27/2010)
wbrianwhite (10/27/2010)
As mundane as this might seem, these three routines provide some dazzling capabilities:
Not really. A history trigger is a far better choice. The stored proc can only ensure that the archive table has values in synch if that stored proc is the only means used to alter the data. What if someone does it another way? A trigger will fire no matter what means you use to update the data.
I have long considered using triggers instead of the "output" clause in the stored procs, precisely because of this situation here. Of course if you have developers who don't understand the database structure you're pretty much screwed no matter what you do, because of course such people could go around modifying the archive tables directly (without making corresponding changes to the current data), and then even your triggers won't save you.
I agree with wbrianwhite - triggers are better. If you give noone permission to modify the contents of the archive tables, then your developers won't mess up either. The trigger will still be able to add to the archive table, becuase of ownership chaining.
The one problem that I haven't solved yet with triggers is how to populate the AuditUserId field on a deletion. With insertions and deletions the AuditUserId is being written to the "current" record and so a trigger would have access to it. On a deletion, nothing is being written, and so the trigger would not have access to the ID of the user who is doing the deletion.
USER_NAME() returns the name of the database user; SUSER_SNAME() returns the corresponding login.