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

From: "Stephens, Chris" <chris_stephens@xxxxxxxxxxxx>
To: <toon@xxxxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 20 Aug 2008 10:12:17 -0500
I don't think Tom's article even implies that all triggers are evil.  I
didn't read it like that.   The point is that there is great potential
for abuse and I'm not claiming that characteristic is unique to triggers
by any means.

 

On using triggers to enforce data integrity constraints:  Isn't one of
the advantages to being able to make a declarative statement about the
data in your database the fact that the Optimizer then has access to
that information?  If you have the option to do it declaratively
wouldn't that be a pretty strong justification for doing so?

 

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Toon Koppelaars
Sent: Wednesday, August 20, 2008 8:33 AM
To: ORACLE-L
Subject: "All triggers are evil",..., really?

 

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"


CONFIDENTIALITY NOTICE: 
        This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law.  If the reader of 
this message is not the intended recipient or the employee or agent responsible 
for delivering this message to the intended recipient, you are hereby notified 
that any dissemination, distribution or copying of this communication is 
strictly prohibited.  If you have received this
communication in error, please notify us immediately by email reply.
References:
- "All triggers are evil",..., really?
  - From: Toon Koppelaars
RE: "All triggers are evil",..., really?

Other related posts:
