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_at_rulegen.com>
 

Sent by: oracle-l-bounce_at_freelists.org
08/20/2008 08:34 AM
Please respond to
toon_at_rulegen.com

To
ORACLE-L <oracle-l_at_freelists.org>
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"

--
http://www.freelists.org/webpage/oracle-l