Feeling brave? Enjoy danger? Well, I dare you to walk into a room full of Oracle experts wearing an I ♥ DATABASE TRIGGERS t-shirt. Let’s see if you walk out of that room alive.
Database triggers are the Oracle feature that developers love to hate, perhaps above every other. And it’s pretty unanimous – from luminaries like Tom “Triggers are evil” Kyte to people like this dude (“Triggers are powerful – and much despised”).
The Problem with Triggers
But, you may be wondering, why are triggers so hated? What’s the big deal? You understand them; you know what you’re doing.
To answer your question, let me start by quoting myself again:
Simple triggers to populate primary key columns from sequences are pretty uncontroversial; however, there is no upper limit to their potential complexity. They can be hundreds of lines long and contain convoluted conditions. Many developers dislike them because they fire silently in the background, seeming to live just beyond the fingertips of their control.
The issue I was highlighting is that because triggers fire automatically, they can sometimes seem like hidden anarchists lobbing Molotov cocktails into your code from the shadows. Imagine you have a huge table – 1 million rows – and you need to update a column. It’s a huge task, but your table is properly indexed so you’re not too worried. Maybe it’ll take a few minutes.
Two hours later you’re sitting there scratching your head. And that’s when you remember that your table has a for each row after-update trigger that inserts into an audit table, synchronises a mirror table, performs some calculations, updates some other columns, and, probably, pauses to smoke a cigar too. Triggers are evil.
Triggers lie too. Imagine you write a simple statement to update 10 records and run it. 10 rows processed, the message says. Oh really? If you have triggers on the table, not even Nostradamus knows how many records across the database were really updated. Triggers are evil.
Triggers are also a convenient hiding place for bugs. When your code errors, it is natural to go over the code you have just written looking for fractures in the logic; no one ever remembers to look in the triggers that may be firing in silence. Triggers are evil.
In fact, in researching this article, I learned that I was wrong when I wrote that simple triggers to populate primary key columns from sequences are pretty uncontroversial. Many developers refuse to tolerate even that. They recommend that you build APIs to interact with your tables, and populate your primary key columns that way. No need for a trigger. I can’t argue with that.
Tom Kyte points out another reason to be wary of triggers. Imagine you maintain a database for a business that sends a £5 discount code to every customer who makes a purchase. To implement it you write an after-insert trigger on the SALES table. For each row inserted into SALES, the trigger makes a call to utl_mail.send to automatically send an email containing the discount code to the customer.
Whatever could go wrong?
If, for whatever reason, your transaction is rolled back after you’ve inserted 100 rows into SALES, those rows will be erased – but what about the emails? Sent. Gone. The customers are probably blowing your cash right now. Ouch. Triggers are evil.
And it’s not just utl_mail; the same thing would happen with utl_file, utl_http, and many other utl_ functions.
And it is a popular practice to write audit records using autonomous transactions fired from within triggers. Same issue. If your main process is rolled back, you’ll be left with your audit records. Like the footprints of a ghost.
What Are Triggers Good For?
At this point you’re probably asking the sensible question: if triggers are evil, why do they exist? It’s almost as if they’re the apple tree in the middle of Eden, a trap placed there to tempt us to sin.
I use them to maintain modified by and modified date columns. And yes, I still use them to populate primary key columns that depend on sequences. Sue me.
And, despite highlighting the danger of using them for auditing, we do still maintain some legacy code that does that. If I was starting today, I would definitely use fine grained auditing instead, but we can’t rewrite the past, at least not cheaply.
Some developers use triggers to implement complex check constraints, since ordinary check constraints cannot reference other tables or include things like sub-queries. You can build a trigger that will carry out your checks before an update or insert and rebuff the transaction if it fails.
Fair enough. But if you build transactional APIs and use them strictly, you can include your complex check in your API and not need a trigger.
When Should You Use Triggers?
Never. THE END.
Oh, is that answer too short for you? Need something longer? Yes, I confess that the main database I work with still has a number of triggers in it, and I know that that makes me sound hypocritical. But it is 15 years old, and sometimes it’s too much trouble to erase the fingerprints of past developers.
But if you do not have that problem, do not use triggers – unless they are the only solution to your particular problem.
And if you think they are the only solution to your particular problem, think again. You’re probably wrong. Believe me.
So that’s it, triggers. Now, we’re all on the same page. We all agree: trigger-happy is a dirty word. So take off that I ♥ DATABASE TRIGGERS t-shirt; let’s set fire to it.