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

From: Dba DBA <>
Date: Wed, 20 Aug 2008 11:08:04 -0400
Message-ID: <>

Where is this post coming from? I did not see any posts on here that said this. I have seen this before. I think it stems from people mis-using triggers. I have seen places where people over use them and have cascading triggers. I think the best use for triggers are to enforce things like what Toon does and for adding primary key counters, and automated create_date fields, etc... I don't think its a good idea to put extensive logic in triggers. Too hard to maintain and its harder to find bugs. I go about as far as Toons goes.

This kind of thing is fairly normal. Your in a shop where people screw up and over use triggers or you read about a stupid use of triggers and then it becomes "triggers are evil". I think they need to be constrained and used in proper ways.

On Wed, Aug 20, 2008 at 10:25 AM, Rumpi Gravenstein <>wrote:

> Toon,
> 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 - 10:08:04 CDT

Original text of this message