herbsao herbsao - 3 months ago 11
SQL Question

Using CASE in MySQL Trigger

I've done some research on the site, and doesn't appear this exact question is a duplicate, so here goes...

I'm creating a trigger in MySQL (after insert) with the goal to set two values (val1, val2) depending on the value of a third field (val3). I'm using CASE because there are only 5 possible values for val3. Problem is that my syntax is throwing errors in MySQL workbench, specifically: "When is not a valid input at this position".

Based on documentation I've read, CASE is valid to use in MySQL triggers, so it must be my syntax. Any help would be greatly appreciated. (Also, in case anyone asks, I also tried using IF/IFELSE, but also got errors. Either one (IF, CASE) works for me...

Here's the code I have now:

CREATE TRIGGER `trig1`
AFTER INSERT ON `tbl`
FOR EACH ROW
BEGIN
CASE
WHEN NEW.val3 = 1 THEN
UPDATE `tbl`
SET val3 = NEW.val3,
val1 = 0,
val2 = 1;
WHEN NEW.val3 = 2 THEN
UPDATE `tbl`
SET val3 = NEW.val3,
val1 = 0.25,
val2 = 0.75;
WHEN NEW.val3 = 3 THEN
UPDATE `tbl`
SET val3 = NEW.val3,
val1 = 0.5,
val2 = 0.5;
WHEN NEW.val3 = 4 THEN
UPDATE `tbl`
SET val3 = NEW.val3,
val1 = 0.75,
val2 = 0.25;
WHEN NEW.val3 = 5 THEN
UPDATE `tbl`
SET val3 = NEW.val3,
val1 = 1,
val2 = 0;
END CASE;
END;

Answer

Update

I simplified the update to derive val1 and val2 based on val3 so you don't need case or if/else anymore

    UPDATE `tbl`
    SET val3 = NEW.val3,
    val1 = (NEW.val3-1)/4,
    val2 = 1-((NEW.val3-1)/4);

Previous Query

CREATE TRIGGER `trig1` 
AFTER INSERT ON `tbl`
FOR EACH ROW
BEGIN       
    UPDATE `tbl`
    SET val3 = NEW.val3,
    val1 = case NEW.val3 when 1 then 0 when 2 then 0.25 
        when 3 then 0.5 when 4 then 0.75 when 5 then 1 end,
    val2 = case NEW.val3 when 1 then 1 when 2 then 0.75 
        when 3 then 0.5 when 4 then 0.25 when 5 then 0 end
    ;
END; 
Comments