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

From: Stephens, Chris <chris_stephens_at_admworld.com>
Date: Wed, 20 Aug 2008 10:12:17 -0500
Message-ID: <7070047601C21A4CB387D50AD3661F6E0A913A64@050EXCHANGE.research.na.admworld.com>


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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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.




--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 20 2008 - 10:12:17 CDT

Original text of this message