What is the best way to maintain a LastUpdatedDate column in SQL?

Question

Suppose I have a database table that has a timedate column of the last time it was updated or inserted. Which would be preferable:

1. Have a trigger update the field.
2. Have the program that's doing the insertion/update set the field.

The first option seems to be the easiest since I don't even have to recompile to do it, but that's not really a huge deal. Other than that, I'm having trouble thinking of any reasons to do one over the other. Any suggestions?

Answer 1

The first option can be more robust because the database will be maintaining the field. This comes with the possible overhead of using triggers.

If you could have other apps writing to this table in the future, via their own interfaces, I'd go with a trigger so you're not repeating that logic anywhere else.

If your app is pretty much it, or any other apps would access the database through the same datalayer, then I'd avoid that nightmare that triggers can induce and put the logic directly in your datalayer (SQL, ORM, stored procs, etc.).

Of course you'd have to make sure your time-source (your app, your users' pcs, your SQL server) is accurate in either case.

---

Regarding why I don't like triggers:

Perhaps I was rash by calling them a nightmare. Like everything else, they are appropriate in moderation. If you use them for very simple things like this, I could get on board.

It's when the trigger code gets complex (and expensive) that triggers start to cause lots of problems. They are a hidden tax on every insert/update/delete query you execute (depending on the type of trigger). If that tax is acceptable then they can be the right tool for the job.