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

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.