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)...
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 :)