CODEWITHSUNDEEP
sql
CodeMonkey1313
CodeMonkey1313

Reputation: 16031

Trigger for updated date column vs explicitly setting it

I'm wondering if using a trigger to set the updated date column of a table (or all tables) is considered a better practice versus having the application explicitly set it. I realize this could devolve into a debate over preferences and design patterns, so in an effort to parameterize the question a bit - I'd like to get a take from a best practices stand point. Taking separation of concerns into consideration (keeping business logic out of the database type of thing) as well as making sure database columns have what is intended within them (actually updating the "last modified date" column), I'm more inclined to let the database handle this via a trigger. That said, I also tend to shy away from triggers since they tend to hide the consequences of an action in a database. I'm hoping that the many smarter people here on SO have a more concrete thought than mine.

Upvotes: 0

Views: 59

Answers (2)

Amir Pelled
Amir Pelled

Reputation: 601

Apart from the tuning, the issue with triggers is that if they are disabled, you will never know this, and transactions will be committed. This could cause a major problem if the update-date is important to your business logic (for instance for sync processes or for finance). I prefer to explicitly handle all business related data within the procedures. And use triggers mainly for auditing.

Upvotes: 0

Robbie Dee
Robbie Dee

Reputation: 1977

It rather depends on what you want to do. If you want to know when any column has changed, then a trigger would be the most suitable. It would be a bit of a drag to have to change code every time you added a new column.

However, if you were only interested in certain columns, you might simply wish to handle this in the update SQL.

There are of course shades in between - you could choose to handle only certain columns in the trigger.

Another consideration is how many bits of code can update a given table. You might have some all singing all dancing update code, but it is perfectly possible this could be spread out.

One word of caution - triggers tend to be the last place you consider when tracking an issue. For this sake (and this is a personal preference), I tend to avoid them unless they're absolutely necessary.

Upvotes: 2

Related Questions