MySQL触发器中封装CASE/条件语句的语法错误修正方案咨询

问题描述 #

我编写了如下触发器代码：

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;

但该代码存在语法错误，运行时会报错。我希望实现的逻辑是：
当P_Location + NEW.Rolls < 17时，执行：

SET P_Location = P_Location + NEW.Rolls
WHERE id = NEW.Player_id

否则执行：

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

错误原因 #

原代码的核心问题在于语法结构错误：

• CASE不能直接嵌套在UPDATE语句开头作为分支结构
• WHERE子句位置错误，应放在整个UPDATE语句末尾
• 分支逻辑没有正确关联到目标更新行

正确实现方式 #

MySQL 简洁写法（推荐） #

在UPDATE的SET子句中使用CASE表达式实现分支逻辑，同时通过末尾的WHERE指定要更新的玩家：

CREATE TRIGGER Moves
AFTER INSERT ON Rolls
FOR EACH ROW
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;

MySQL IF语句块写法 #

如果更习惯用IF逻辑，可将触发器包裹在BEGIN...END语句块中（需修改语句分隔符）：

DELIMITER //
CREATE TRIGGER Moves
AFTER INSERT ON Rolls
FOR EACH ROW
BEGIN
    IF (SELECT P_Location FROM Players WHERE id = NEW.Player_id) + NEW.Rolls < 17 THEN
        UPDATE Players
        SET P_Location = P_Location + NEW.Rolls
        WHERE id = NEW.Player_id;
    ELSE
        UPDATE Players
        SET P_Location = NEW.Rolls - (16 - P_Location)
        WHERE id = NEW.Player_id;
    END IF;
END //
DELIMITER ;

PostgreSQL 版本 #

PostgreSQL需通过触发器函数实现，写法如下：

CREATE TRIGGER Moves
AFTER INSERT ON Rolls
FOR EACH ROW
EXECUTE FUNCTION update_player_location();

CREATE OR REPLACE FUNCTION update_player_location()
RETURNS TRIGGER AS $$
BEGIN
    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;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

逻辑说明 #

所有写法均实现了需求逻辑：

• 当玩家当前位置与掷出点数之和小于17时，直接累加点数更新位置
• 超出16（终点位置）时，计算超出部分并反向设置位置（即“反弹”逻辑）

内容的提问来源于stack exchange，提问作者Abubakar Popoola