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:

  • They can introduce “data bugs” that are extremely hard to trace.
  • They can (and usually do) slow down CRUD operations.
  • Since they work in a transparent manner, it is hard to performance trace them with tools such as SQL Profiler.
  • They hide business logic.  Things that seem to happen in an application magically could be attributed to an unknown trigger.  Not having visibility into the all the business logic for a particular domain creates “blind spots” for developers and therefore increases the chances of defects.

The only scenarios I like to see triggers used are:

  • For database audit purposes – if you need fine grain tracking of what is going on in a table, like who touched it or filling a corresponding history table.
  • For use with database refactoring, but only on a temporary basis.

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!

  • http://mudpoet.org Travis

    I have to disagree. I love triggers.

    Data bugs can be introduced anywhere. The business logic should be documented (your example is exactly why)…don’t blame the trigger for that one. If there are blind spots in your solution, you’ve got a LOT more to worry about than poorly written triggers. You do have a point in regards to performance in CRUD operations, but that’s where good coding comes into play. When designing a system, a good software developer will always come up with multiple solutions and choose the best approach.

    I’m not saying they are the end-all be-all, but triggers are damn useful when used properly.

  • Matt

    From a code developer standpoint, my problems with triggers stem from: 1) Based on my (albeit limited) knowledge, it is very difficult to version control triggers. In fact, it is very difficult to version control MS SQL in general. 2) In code, the typical pattern for reacting to a change in data is to implement IObservable/IObserver. Implemented as such, you can read through the target code, and actually *see* that, hey, there is an external component that may be activated on this change event. Triggers, on the other hand, are transparent to the actual table.

  • Wes Thomas

    I guess the biggest reason to hate triggers is their transparency. And therefore their lack of traceability. If you have a bug in your code, you can spend lots of time tracing and never know that a trigger is doing something to your data without revealing itself. The worst is when the trigger triggers another trigger which triggers a trigger.

    Just.. don’t. DON’T.

  • Trigger-UnHappy DBA

    Nice write up on the subject. Now as a DBA, I have to chime in. In addition to those you mentioned, database triggers can slow a OLTP database when it becomes a batch process (more on a gotcha with that later) since its data update timings are at the mercy on the volume of the database activity rather than a more predictable scheduled batch job. Now the gotcha part, the simple database trigger is only fire ONCE for bulk updates (usual against the last row), but in order for the trigger to fire on ALL the rows in a batch, the developer has to enhance the trigger by using the special “inserted” table along with a cursor to get all the batch rows. For example, let says you have a trigger that fires when a sales record is updated on the trans_status column to send an email the buyer. All is good if the application is doing one row at a time as it normally should. Now, your Customer Dept. discovered that several sales transactions needed to flip its trans_status from SOLD to REFUND and asked the developer/DBA to do a batch update on them since it would take 1 hour or so for her to do it manually via the application. It would be a quick 2 mins task for the developer/DBA to write a mass update statement and the affected customers would get an notification email right?

    Nope, not with a standard SQL server triggers, only a cursor or equivalent coded inside the trigger would do that as a standard trigger just only send 1 email since the trigger only fired once even on a mass update statement.

  • Piep

    You are right about all points, OP