There are a lot of good discussion on the topic of triggers in general here:
http://stackoverflow.com/questions/460316/are-database-triggers-evil
You have to be very careful when using triggers. These are the main points of concern:
1. triggers cause a side-effect, they change data in ways that were not expected given the (primitive) operator insert, update or delete that was executed and caused the trigger to fire. They change the well known semantics of an INSERT, UPDATE or DELETE statement. Changing the semantics of these three primitive SQL operators will bite other developers who later in the future need to work on your database tables that do not behave in expected ways anymore when operated upon them with the SQL primitives.
2. You can get into "trigger hell", where one trigger causes other triggers to fire which are extremely hard to diagnose when things go wrong.
Those two reasons alone are enough for me to shy away from them. But I guess if your very, very careful about your use of them, then go ahead and use them.
-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
Sent: Friday, September 19, 2014 3:18 PM
To: Midrange Systems Technical Discussion
Subject: Re: Anyone using Triggers to enforce data validation & business rules?
I question doing stuff in triggers that are best left in constraints.
Specifically you just mentioned referential integrity. Which means stuff like "don't allow an order line entry if there is no order header record"
kind of stuff. On this you should simply do something like ADDPFCST FILE(ORDLINE)
TYPE(*REFCST)
KEY(LORDNUM)
PRNFILE(ORDHEAD)
PRNKEY(HORDNUM)
DLTRULE(*RESTRICT)
UPDRULE(*RESTRICT)
Try that on your order line file. Now try an RPG write into that order line file with an order number that does not exist in the order header file.
Also, simple checking should also be done with contraints. Now the syntax below may be garbage (as I'm trying to leave for the weekend) but I hope you get the idea.
ADDPFCST FILE(ORDLINE)
TYPE(*CHKCST)
CHKCST('lactrec in(''A'', ''Z'')') This validates the active record code in the order line file.
Again, try that RPG write with an invalid record code and see what happens.
Before you start thinking "but if I do all my contraints in my trigger I can do the error handling there and display the message just so" remember, what if the input is coming from something other than 5250?
This is one of the advantages of DDL over DDS. In DDL source you can put all the constraints. In DDS you have to use a 'make' program to run a bunch of ADDPFCST commands.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Nathan Andelin <nandelin@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 09/19/2014 04:04 PM
Subject: Re: Anyone using Triggers to enforce data validation &
business rules?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Would it be possible, for interactive jobs, to do a *BEFORE trigger and
handle the error in the trigger program itself with an error window?
It makes sense to me to put data validation and referential integrity
constraints in *BEFORE triggers - where an exception automatically stops
the DBMS from changing the record.
I suppose an error window in the Trigger program would work for 5250
users.
But what about web applications, etc?
Nathan.
midrange.com
