mysql - 创建 MySQL 触发器时出现语法错误

Question

我在尝试触发时遇到了一些问题。

这是我对触发器的 MySQL 查询:

delimiter //
CREATE TRIGGER `aggiornaProduzione`
BEFORE UPDATE ON `strutture` FOR EACH ROW
BEGIN
DECLARE temp bigint;
IF ( tipo=1/*mercato*/ AND old.livello <> new.livello )
THEN (
    SELECT round(2*livello*livello + 13.8*livello)
    FROM strutture WHERE tipo=1 AND city=old.city INTO temp;
    WHILE (temp%6<>0) temp=temp+1;
    END WHILE;
    UPDATE strutture SET produzione=temp WHERE city=new.city AND tipo=1;
    )
END IF;
END //

错误是

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; WHILE (temp%6<>0) temp=temp+1; END WHILE; UPDATE strutture SET ' at line 8

有人知道我为什么会收到此错误吗？

Answer 1

删除 THEN 子句两边的括号。此外，您的 WHILE 子句在语法上不正确:

delimiter //
CREATE TRIGGER `aggiornaProduzione`
BEFORE UPDATE ON `strutture` FOR EACH ROW
BEGIN
DECLARE temp bigint;
IF ( tipo=1/*mercato*/ AND old.livello <> new.livello )
THEN
    SELECT round(2*livello*livello + 13.8*livello)
    FROM strutture WHERE tipo=1 AND city=old.city INTO temp;
    WHILE temp%6<>0 DO
      SET temp=temp+1;
    END WHILE;
    UPDATE strutture SET produzione=temp WHERE city=new.city AND tipo=1;
END IF;
END //