Share via

Default Values, Triggers, and Code supplied values ... oh my!

  • Article

In an earlier post, I mentioned that I use a trigger to update a "DateCreated" field in one of my tables... and various people commented on this... asking "why not pass in a value in your Insert?" or "why not use a default value?"

Since I feel that replying to comments in the comments section is generally just a black hole, I thought I'd raise this discussion up to a proper blog entry of its own :)

I tend to have audit information on my tables, and that often includes 4 columns; DateCreated, CreatedBy and DateModified, LastModifiedBy

I use two triggers, an INSERT trigger that sets all of these fields to the current date and current user (as appropriate, and using Windows Authentication), and an UPDATE trigger that sets only the two modified columns.

Why not just pass the value? Well, two reasons...

sometimes data gets entered in through a different code path than mine... or through something like SQL Enterprise Manager (for lookup tables especially)...

  1. I just don't like relying on code external to the database to put the right value in for audit information. This is true for both the modified and the created situations...
  2. Why not just use a default? Well, that is a better solution than passing the value in, except it doesn't prevent the user from passing in (or setting) whatever value they want. If they pass in a value for DateCreated in their INSERT now, it will get overwritten with the 'real value'. And, even if they do an UPDATE later to change the DateCreated (which is a real flaw in this method), at least the 'last modified by' will be accurate.

Of course, this is just my 'simple' auditing method... for any situation where I really care about the audit information (this example was from my little polling system... auditing info is just a 'nice to have') then I would need to actually restrict access to those fields completely so that they can only be set through my triggers or stored procs. There are well documented ways to do this, so I won't go into them here... (restrict all access to the table, only allow INSERTs and UPDATEs through your chosen Stored Procs... Stored Procs set those audit fields exactly as you specify, etc...).

It is worth noting that in a more complex auditing solution tracking only the last modification probably wouldn't be sufficient anyway.

I'm sure there will be people, many of which know more about this problem space than me, that can chime in with dissenting or agreeing opinions... I'm looking forward to the discussion :)

Comments

  • Anonymous
    July 14, 2004
    In the UPDATE trigger, you could just cancel out any update to the DateCreated, right? Replace the value of the field in the insert table that from the delete table?

    And then raise red flags, track down the user, remove their access, (pillage their hometown, take their first-born child, key their Ferrari in the parking lot...)
  • Anonymous
    July 14, 2004
    The comment has been removed
  • Anonymous
    July 14, 2004
    Except for the names, I have the same fields.

    My triggers also log all activity to audit tables, with ModificationType, ModifiedOn, and for each field the old value and the new value.

    The best part? I've tooled adding the fields, creating the audit table and creating the triggers -- so for me, it's a single stored procedure call to set up FULL auditing on a table.

    Ah, bliss :-)
  • Anonymous
    July 14, 2004
    I seem to remember reading that having triggers could be a problem for transactions. Is there any truth to this?

    Also, if you are going to restrict access to only SPROCS, why not simply have the procedure update the auditing info directly?

    Great points and discussion...