Here's the code for the trigger to which I am referring:
CREATE TRIGGER spsl/l2dual_upd
AFTER DELETE OR UPDATE OF DSCONF ON plc_data/l2dual
BEGIN
IF UPDATING THEN
DELETE FROM plc_data/l2dual WHERE DSCONF=2;
ELSEIF DELETING THEN
UPDATE plc_data/l2dual SET dsconf=1 WHERE dslotref =(SELECT dslotref FROM plc_data/l2dual WHERE dsconf=0 FETCH FIRST ROW ONLY);
END IF;
END
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Brian Piotrowski
Sent: Friday, April 7, 2017 2:30 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Multiple SQL in Triggers
Thanks, Rob.
I did that, and now a new error appears. The error this time is " Clauses not valid in same definition.". In checking what the error means there are about a dozen reasons why it failed, but I'm not sure which one pertains to my problem.
/b;
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Friday, April 7, 2017 2:22 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Multiple SQL in Triggers
According to the book I'm reading (page 260 of SQL/400 Developer's Guide by Paul Conte and Mike Cravitz) You need to change this:
BEGIN
IF UPDATING
<DELETE SQL STATEMENT>
ELSEIF DELETING
<UPDATE SQL STATEMENT>
END IF
END
to this:
BEGIN
IF UPDATING
<DELETE SQL STATEMENT>;
ELSEIF DELETING
<UPDATE SQL STATEMENT>;
END IF;
END
The problem is that you are assuming that SQL says "Hey, he put an ELSEIF here, that implies that we were supposed to stop processing the previous IF statement", and you're also assuming that SQL says "Hey, he put an END IF here, that implies that we were supposed to stop processing the previous IF statement".
Add the three semicolons.
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: Brian Piotrowski <bpiotrowski@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 04/07/2017 02:16 PM
Subject: Multiple SQL in Triggers
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Hi All,
I'm trying to get a trigger defined, and it's giving me a bit of grief.
Hopefully, one of you might be able to point me in the right direction.
A bit of background...one of our maintenance groups would like to use data from a table on our IBM i that will read the first record where the flag
(DSCONF) = 1. Once it reads the data, it will use the data and write an acknowledgement (in this case it will change the DSCONF flag from 1 to 2).
What I need to do at this point is wait for the PLC to change that record from a 1 to a 2. Once it does that I need to delete that record, and change the next record's DSCONF = 1. Then the process starts all over again (PLC reads the DSCONF=1 record, then updates it with DSCONF=2, row is deleted, the next row DSCONF=1, etc...).
I was able to accomplish the first challenge in creating a trigger that fires after the update and deletes the record where DSCONF=2. However, I'm having trouble with the second statement. I've tried creating a separate trigger that fires once the update has been completed, but it fails. So I thought about putting both statements into a trigger, but I just can't seem to get the syntax correctly for it to work.
Basically, I need to do this:
CREATE TRIGGER UPD_L2DUAL
AFTER DELETE OR UPDATE ON dsconf ON l2dual BEGIN IF UPDATING <DELETE SQL STATEMENT> ELSEIF DELETING <UPDATE SQL STATEMENT> END IF END
When I submit this, the system complains with an "ELSEIF not expected". I put a semicolon after the DELETE SQL statement and then it complains that "Keyword END not expected" (in this case it's the "END IF" statement it doesn't like). I'm not sure what to do from here. I tried putting in another semicolon after the last UPDATE statement but it didn't like it.
On another note, my assumption is the event will be triggered when the PLC deletes the record where DSCONF=2 and then it will fire again when it detects the deletion and change the next record DSCONF from 0 to 1. Is that correct thinking?
Any advice would be appreciated.
Thankee-sai!
/b;
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Brian Piotrowski
Manager - I.T.
Simcoe Parts Service, Inc.
Ph: 705-435-7814 x5995
Fx: 705-435-5029
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
http://www.simcoeparts.com<
http://www.simcoeparts.com/>
Please consider the environment. Don't print this e-mail unless you really need to.
The information contained in this communication is confidential and intended only for the use of those to whom it is addressed. If you have received this communication in error, please notify me by telephone (collect if necessary) and delete or destroy any copies of it. Thank you!
midrange.com
