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

From: Robert Freeman <robertgfreeman@xxxxxxxxx>
To: JApplewhite@xxxxxxxxxxxxx, toon@xxxxxxxxxxx
Date: Wed, 20 Aug 2008 08:38:42 -0700 (PDT)
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.

Boooyah.....

RF


 Robert G. Freeman
Author:
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: http://robertgfreeman.blogspot.com (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: "JApplewhite@xxxxxxxxxxxxx" <JApplewhite@xxxxxxxxxxxxx>
To: toon@xxxxxxxxxxx
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>; oracle-l-bounce@xxxxxxxxxxxxx
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"
<toon@xxxxxxxxxxx> 
  
Sent by: oracle-l-bounce@xxxxxxxxxxxxx 
08/20/2008 08:34 AM 
Please respond to
toon@xxxxxxxxxxx 
 To ORACLE-L <oracle-l@xxxxxxxxxxxxx>  
cc 
 
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"
Re: "All triggers are evil",..., really?

Other related posts:
