Best way to implement a last-modified column in Sql Server 2005?

How do you implement a last-modified column in SQL?

I know for a date-created column you can just set the default value to getdate(). For last-modified I have always used triggers, but it seems like there must be a better way.

Thanks.

From stackoverflow

• Triggers are the best way, because this logic is intimately associated with the table, and not the application. This is just about the only obvious proper use of a trigger that I can think of, apart from more granular referential integrity.

  But I think "last-modified" dates are a flawed concept anyway. What makes the "last-changed" timestamp any more valuable than the ones that came before it? You generally need a more complete audit trail.

  Shawn Simon : im using it in a grid of patients so the doctors can see the last one they modified, although i agree the date of their last visit seems more useful...

  le dorfier : If it's the last patient they modified, rather than the last time they modified each patient, then it even more needs to be in a separate table - or persisted somewhere else globally.

  Tomalak : I think in the old days they said "treat" or "heal", not "modify". SCNR ;-) More seriously, I agree with @doofledorfer in that a LastModified date is not very useful because it carries next to no information and may lead to false conclusions.

  Shawn Simon : hey man i just follow the spec : >

• You can use the keyword DEFAULT, assuming you have a default constraint. On insert, there is no need to specify a value, You could use the keyword here too.

  No trigger and is done in the same write as the "real" data

  UPDATE
     table
  SET
     blah,
     LastUpdatedDateTime = DEFAULT
  WHERE
     foo = bar
  

  le dorfier : Agreed. Except you should declare it as DEFAULT for the column in the table schema.

  le dorfier : Mp = wait - that would only work for INSERTs.

  gbn : No, works for UPDATE http://msdn.microsoft.com/en-us/library/ms177523(SQL.90).aspx ... SET { column_name = { expression | DEFAULT | NULL } ...

  le dorfier : Agreed and raise you one. But this is using it for an evil purpose. :)

  le dorfier : gbn, you're right - new fact. But that's even more evil than a trigger. :)

  gbn : It's be a shame not to use it and enjoy the evil though...

• Using a trigger to update the last modified column is the way to go. Almost every record in the system at work is stamped with an add and change timestamp and this has helped me quite a bit. Implementing it as a trigger will let you stamp it whenever there is any change, no matter how it was initiated.

  Another nice thing about a trigger is that you can easily expand it to store an audit trail as well without too much trouble.

• And as long as you are doing so, add a field for last_updated_by and update the user everytime the record is updated. Not as good as a real audit table but much better than the date last updated.

  A trigger is the only method you can use to do this. And to those who said triggers are evil, no they aren't. They are the best way - by far - to maintain data integrity that is more complex than a simple default or constraint. They do need to written by people who actually know what they are doing though. But then that is true of most things that affect database design and integrity.

  le dorfier : Agreed. Note my statement about "more granular referential integrity".

• The only other way to perform this without using triggers is to disallow any inserts/updates/deletes on the table directly via permissions, and insist all these actions are performed via stored procedures that will take care of setting the modified date.

  An administrator might still be able to modify data without using the stored procedures, but an administrator can also disable triggers.

  If there are a lot of tables that require this sort of functionality, I would favour triggers as it simplifies the code. Simple, well written and well-indexed auditing triggers are generally not too evil - they only get bad when you try to put too much logic in the trigger.