Re: "All triggers are evil",..., really?

From: Robert Freeman <>
Date: Wed, 20 Aug 2008 08:38:42 -0700 (PDT)
Message-ID: <>

I'm a fan of triggers, but you have not lived until the day you realize that through some derivative of the uncertainty principle that your trigger at some point became disabled and has been so for several days. Of course, the solution is to monitor them, etc.... but even a few seconds of a disabled trigger can be deadly.



 Robert G. Freeman
Oracle Database 11g New Features (Oracle Press) Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press) Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Feature
Blog: (Oracle Press) The LDS Church is looking for DBA's. You must be LDS to apply (please don't write to me and tell me I'm breaking the law. A church can choose to hire members of it's own faith. Look it up if you don't believe me).

  • Original Message ---- From: "" <> To: Cc: ORACLE-L <>; Sent: Wednesday, August 20, 2008 8:13:10 AM Subject: Re: "All triggers are evil",..., really?

Glad you wrote this, as I was just about to. Sure Triggers are misused and I've seen some real bone-headded stuff. In fact, we're living with a bunch in our 3rd Party COTS Finanacial / HR system.

However, they can be very useful by
enforcing business rules immediately and in the database, so no matter what code hits a table, it's forced to "do the right thing".  Triggers really are no different than any other pieces of code that need to be rigorously spec'd out, planned, written, and reviewed.

Jack C. Applewhite - Database Administrator Austin I.S.D. - MIS Department
512.414.9715 (wk) / 512.935.5929 (pager)  

"Toon Koppelaars"

Sent by:
08/20/2008 08:34 AM
Please respond to

Subject "All triggers are evil",...,

I need to get this of my chest....

I disagree that "all triggers are evil". Sure, a lot are evil, but not all.
From where I stand you can use (table) triggers for two purposes:

  1. To implement "business logic" (that is: any code that performs inserts/updates/deletes, or initiates other events 'outside' the DBMS).
  2. To implement "data integrity constraints".

I fully agree that when triggers are used for the former purpose, they are bad. For all the reasons mentioned, most notably having stuff happen automagically.
But I have to disagree with the case where triggers are used for the latter purpose. Granted, using them to implement constraints is a tricky and rather complex task. But it can be done. Just because it's tricky and rather complex, doesn't imply it's evil. If done correctly it gives you an great 'separation of concerns' when coding database applications. If all constraint validation is correctly 'tucked away' behind triggers, then all of your other application code (the 'business logic' code) can be devoid of constraint validation code: it only needs to handle the exceptions raised by the constraint validation code. You'll end up with a cleaner 'business logic' layer, that is easier to maintain.

Under the hood, a foreign key (FK) is just a bunch of (tricky and rather complex) triggers. These triggers happen to be 'hardwired' into the kernel at various places and have been designed and programmed once by a couple of smart programmers in Redwood Shores. But conceptually a FK is just a bunch of (call them) "declarative" hooks in the kernel that trigger code to validate the FK predicate whenever your application performs insert/updates/deletes on the involved tables, including taking care of the necessary serialization of concurrent transactions.

Now, does this make a FK evil? I think not...

The big pro of a declarative FK is just that: it's declarative. And therefor easy to maintain. You can enable it, disable it, drop it, defer it's execution, etc. When you code (tricky and rather complex) triggers to maintain integrity constraints, you don't have this pro. You need to make sure that you code them in such a way, that you are still able to easily maintain the code, i.e. maintain the integrity constraints. <warning_blatant_sales_pitch_coming_up>This by the way is exactly what RuleGen gives you. </warning_blatant_sales_pitch_coming_up>

Ideally we should finally get (from our DBMS vendor) support for the CREATE ASSERTION command which has been in the SQL standard for a long, long time. Only when we have this support, will I too agree that "all triggers are evil".

Toon Koppelaars
RuleGen BV

Author: "Applied Mathematics for Database Professionals" 

Received on Wed Aug 20 2008 - 10:38:42 CDT

Original text of this message