Tidbits on software development, technology, and other geeky stuff.

I Hate Triggers

I understand the initial appeal of database triggers from a developer’s perspective. Among other things, they enable you to tack business logic to existing database structures without having to change applications that sit on top of them.

For example, let’s say you have an e-commerce database that has several applications sitting on top of it. When these applications were developed, a business assumption was made to charge a flat shipping fee of $10.00 for every order of $100.00 or more. This $10.00 value is stored in a configuration table within the database. Times changed and now, we have different flat shipping rates, depending upon which state the customer lives. It might be tempting to simply write an Insert trigger on the Order table to change the $10.00 shipping charge to an amount determined by a State_Flat_Shipping_Rate table. After all, this would be fairly quick to implement and you wouldn’t have to dig through old code of several existing applications. But, as with many things in life,_ the simple path is not always the best path_.

I can’t tell you how many times I have come across an issue that was caused by a trigger. I’ve gotten to the point where, when I see a trigger culprit, I raise my fist in the air and say “damn you, trigger!”.

Here’s why I generally hate them:

The only scenarios I like to see triggers used are:

If you really must use a trigger and can’t find a way to do what you need by another mechanism, a good question to ask yourself is “If this trigger is temporarily disabled or dropped entirely, will it affect my application in any material way?” If the answer is yes, don’t use one!

Discuss on Twitter