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

From: Rumpi Gravenstein <>
Date: Wed, 20 Aug 2008 10:25:58 -0400
Message-ID: <>


There is at least one more class of triggers I would include in your list -- row auditing.

I for one think that a trigger that maintains row insert and update timestamp and user field is acceptable, and even a desirable use of a trigger.

On 8/20/08, Toon Koppelaars <> wrote:
> 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
> toon_at_rulegen_dot_com
> www_dot_rulegen_dot_com
> Author: "Applied Mathematics for Database Professionals"

Rumpi Gravenstein

Received on Wed Aug 20 2008 - 09:25:58 CDT

Original text of this message