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

From: Toon Koppelaars <toon_at_rulegen.com>
Date: Wed, 20 Aug 2008 15:32:33 +0200
Message-ID: <bba9fbc50808200632w6212e351t3b1a40c8052c310b@mail.gmail.com>

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 - 08:32:33 CDT

Original text of this message