Oninit cdi / Archive
Talk to an expert →

number of when clauses in create trigger statement

Posted in 2017

Topics: Server Administration, Triggers, Constraints & Referential Integrity

MARY GREEN — — source: IIUG Forums & Mailing Lists

Is it legal to have more than one WHEN in a create trigger statement? I am trying to run this SQL: create trigger "dba".t_up_loc_sb44 update of location on shelf_bin referencing old as oldval new as newval for each row when(oldval.location = '44' and newval.location <> '44' and newval.location <> '44Q') (delete from shelf_bin where location = '44Q' and part_no = oldval.part_no and shelf_bin = oldval.shelf_bin) when (oldval.location <> '44' and oldval.location <> '44Q' and newval.location = '44') (insert into dba.shelf_bin(shelf_code, part_no, location, shelf_bin) values(0, newval.part_no, newval.location,newval.shelf_bin)); When I do, I get SQL error -201, and the editor always takes me to the second when clause.

↪ replying to MARY GREEN
Jonathan — — source: IIUG Forums & Mailing Lists

Yes, it is legal to have multiple WHEN conditions. However, the separate WHEN conditions must be separated by commas, as indicated in the syntax diagram: https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_s qs_0618.htm#ids_sqs_0618 On Tue, Oct 10, 2017 at 6:01 PM, MARY GREEN <mgreen@flyravn.com> wrote: > Is it legal to have more than one WHEN in a create trigger statement? > > I am trying to run this SQL: > create trigger "dba".t_up_loc_sb44 update of location on shelf_bin > > referencing old as oldval new as newval > > for each row > > when(oldval.location = '44' and newval.location <> '44' > > and newval.location <> '44Q') > > (delete from shelf_bin where location = '44Q' > > and part_no = oldval.part_no > > and shelf_bin = oldval.shelf_bin) > > when (oldval.location <> '44' and oldval.location <> '44Q' > > and newval.location = '44') > > (insert into dba.shelf_bin(shelf_code, part_no, location, shelf_bin) > > values(0, newval.part_no, newval.location,newval.shelf_bin)); > > When I do, I get SQL error -201, and the editor always takes me to the > second > when clause. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2015.1101 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused."

↪ replying to Jonathan
MARY GREEN — — source: IIUG Forums & Mailing Lists

Thank you! I tried it, and it works beautifully.

Related threads