mysql - 创建 SQL 触发器以更新行值

Question

我有一个包含列“modem_id”和“user”的 SQL 表“Inbox”我想在 modem_id 为 1 时创建一个 SQL 触发器，“用户”列正在插入值“A”； 2 用户 'B'...

我已经用 if else 创建了一个 SQL 触发器:

delimiter //
CREATE TRIGGER upd_user_inbox BEFORE insert ON inbox
    FOR EACH ROW
    BEGIN
    IF NEW.modem_id = 0 THEN
             UPDATE `inbox` SET `user` = 'A' WHERE modem_id = 1; 
         ELSEIF NEW.modem_id = 2 THEN
             UPDATE `inbox` SET `user` = 'B' WHERE modem_id = 2; 
            ELSEIF NEW.modem_id = 3 THEN
             UPDATE `inbox` SET `user` = 'C' WHERE modem_id = 3; 
            ELSEIF NEW.modem_id = 4 THEN
             UPDATE `inbox` SET `user` = 'D' WHERE modem_id = 4; 
            ELSEIF NEW.modem_id = 5 THEN
             UPDATE `inbox` SET `user` = 'E' WHERE modem_id = 5; 
            ELSEIF NEW.modem_id = 6 THEN
             UPDATE `inbox` SET `user` = 'F' WHERE modem_id = 6; 
            ELSEIF NEW.modem_id = 7 THEN
             UPDATE `inbox` SET `user` = 'G' WHERE modem_id = 7; 
            ELSEIF NEW.modem_id = 8 THEN
             UPDATE `inbox` SET `user` = 'H' WHERE modem_id = 8; 
         END IF;
     END;//
delimiter ;

触发器执行成功但不工作。如果“if-else”不起作用，谁能教我改用“case”？

谢谢

Answer 1

https://dev.mysql.com/doc/refman/5.7/en/case.html

CASE NEW.modem_id
WHEN 0 THEN ...
WHEN 2 THEN ...
...
END CASE