Feeling brave?\u00a0 Enjoy danger? Well, I dare you to walk into a room full of Oracle experts wearing an I <\/i><\/b>\u2665 DATABASE TRIGGERS<\/b> <\/i>t-shirt. Let\u2019s see if you walk out of that room alive.<\/p>\n
Database triggers are the Oracle feature that developers love to hate, perhaps above every other.\u00a0 And it\u2019s pretty unanimous \u2013 from luminaries like Tom \u201cTriggers are evil\u201d Kyte \u00a0to people like this dude<\/a> (\u201cTriggers are powerful \u2013 and much despised\u201d).<\/p>\n But, you may be wondering, why are triggers so hated? What\u2019s the big deal? You understand them; you know what you\u2019re doing.<\/p>\n To answer your question, let me start by quoting myself again:<\/p>\n Simple triggers to populate primary key columns from sequences are pretty uncontroversial; however, there is no upper limit to their potential complexity.\u00a0 They can be hundreds of lines long and contain convoluted conditions.\u00a0 Many developers dislike them because they fire silently in the background, seeming to live just beyond the fingertips of their control.<\/i><\/p>\n<\/blockquote>\n 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 \u2013 1 million rows \u2013 and you need to update a column. It\u2019s a huge task, but your table is properly indexed so you\u2019re not too worried. Maybe it\u2019ll take a few minutes.<\/p>\n Two hours later you\u2019re sitting there scratching your head. And that\u2019s when you remember that your table has a for each row<\/i> 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.<\/p>\n Triggers lie too. Imagine you write a simple statement to update 10 records and run it. 10 rows processed<\/i>, 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.<\/p>\n 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.<\/p>\n 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.<\/i> 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.\u00a0 No need for a trigger. I can\u2019t argue with that.<\/p>\n Tom Kyte points out another reason to be wary of triggers. Imagine you maintain a database for a business that sends a \u00a35 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.<\/p>\n Whatever could go wrong?<\/p>\n Rollback.<\/p>\n If, for whatever reason, your transaction is rolled back after you\u2019ve inserted 100 rows into SALES, those rows will be erased \u2013 but what about the emails? Sent. Gone. The customers are probably blowing your cash right now. Ouch. Triggers are evil.<\/p>\n And it\u2019s not just utl_mail; the same thing would happen with utl_file, utl_http, and many other utl_ functions.<\/p>\n And it is a popular practice to write audit records using autonomous transactions fired from within triggers.\u00a0 Same issue. If your main process is rolled back, you\u2019ll be left with your audit records. Like the footprints of a ghost.<\/p>\n At this point you\u2019re probably asking the sensible question: if triggers are evil, why do they exist? It\u2019s almost as if they\u2019re the apple tree in the middle of Eden, a trap placed there to tempt us to sin.<\/p>\n Not exactly.<\/p>\n I use them to maintain modified by and modified date columns.\u00a0 And yes, I still use them to populate primary key columns that depend on sequences. Sue me.<\/p>\n 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\u2019t rewrite the past, at least not cheaply.<\/p>\n 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.<\/p>\n 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.<\/p>\n Never. THE END.<\/p>\n 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\u2019s too much trouble to erase the fingerprints of past developers.<\/p>\n But if you do not have that problem, do not use triggers \u2013 unless they are the only solution to your particular problem.<\/p>\n And if you think they are the only solution to your particular problem, think again. You\u2019re probably wrong. Believe me.<\/p>\n So that\u2019s it, triggers.\u00a0 Now, we\u2019re all on the same page. We all agree: trigger-happy is a dirty word. So take off that I <\/i><\/b>\u2665 DATABASE TRIGGERS<\/b> <\/i>t-shirt; let\u2019s set fire to it.<\/p>\n References:<\/em><\/p>\n https:\/\/allthingsoracle.com\/oracle-for-absolute-beginners-part-7-creating-tables-constraints-and-triggers\/<\/a><\/p>\n http:\/\/www.oracle.com\/technetwork\/testcontent\/o58asktom-101055.html<\/a><\/p>\n http:\/\/tkyte.blogspot.co.uk\/2010\/04\/that-old-restart-problem-again.html<\/a><\/p>\nThe Problem with Triggers<\/h3>\n
\n
Uncontrolled Processes<\/h3>\n
Maintenance Nightmare<\/h3>\n
Rollback Problems<\/h3>\n
What Are Triggers Good For?<\/h3>\n
When Should You Use Triggers?<\/h3>\n