Home Trying to Encapsulate a CASE/Conditional Statement Within A Trigger in MySQL

Questions

Trying to Encapsulate a CASE/Conditional Statement Within A Trigger in MySQL

November 11, 2022

I have a trigger:

CREATE TRIGGER Moves 
AFTER INSERT ON Rolls
FOR EACH ROW
UPDATE Players
CASE
    WHEN P_Location + NEW.Rolls < 17 THEN
    SET P_Location = P_Location + NEW.Rolls
    WHERE id = NEW.Player_id
    ELSE 
    SET P_Location = NEW.Rolls - (16 - P_Location)
END;

But the syntax is incorrect somewhere and generates errors. I’ve checked for similar problems on this site but the ones I looked at seemed to use methods (employing IF in particular) that don’t seem to work in this case. I want the primary conditional to be

IF P_Location + NEW.Rolls < 17 THEN
SET P_Location = P_Location + NEW.Rolls
WHERE id = NEW.Player_id

And the ELSE part to be

SET P_Location = NEW.Rolls - (16 - P_Location)

Within the trigger. Could anyone explain how I can do this please?

>Solution :

A CASE expression must evaluate to a scalar, but you’re lacing other clauses through the CASE expression.

You can do it this way, so the result of the CASE returns a scalar, and that scalar is assigned to the column P_Location.

UPDATE Players
SET P_Location = CASE WHEN P_Location + NEW.Rolls < 17
                 THEN P_Location + NEW.Rolls
                 ELSE NEW.Rolls - (16 - P_Location)
                 END
WHERE id = NEW.Player_id;