MySQL 触发器定义 - 1064 错误

Question

我建议的触发器:

#START TRIGGER
delimiter //
DECLARE msg VARCHAR(255);
CREATE TRIGGER passStandard_check BEFORE INSERT ON Module
FOR EACH ROW 
BEGIN 
IF NEW.passStandard < 0 || NEW.passStandard > 1 THEN 
set msg = concat('Trigger Error: Pass Standard: ', cast(NEW.passStandard as char));
signal sqlstate '45000' set message_text = msg;
END
//
delimiter ;
#END TRIGGER

但是我得到以下错误:

Error Code: 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 'DECLARE msg VARCHAR(255); CREATE TRIGGER passStandard_check BEFORE INSERT ON Mod' at line 1

添加 END IF 没有任何区别。

passStandard 设置为 INT NOT NULL。

---

编辑

我已将 DECLARE 语句移到 BEGIN 之后:

#START TRIGGER
delimiter //
CREATE TRIGGER passStandard_check BEFORE INSERT ON Module
FOR EACH ROW 
BEGIN 
DECLARE msg VARCHAR(255);
IF NEW.passStandard < 0 || NEW.passStandard > 1 THEN 
set msg = concat('Trigger Error: Pass Standard: ', cast(NEW.passStandard as char));
signal sqlstate '45000' set message_text = msg;
END IF;
END
//
delimiter ;
#END TRIGGER

但我仍然得到这个错误:

Error Code: 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 'sqlstate '45000' set message_text = msg; END IF; END' at line 7

Answer 1

您需要在过程中声明变量“msg”并使用 END IF

#START TRIGGER
delimiter //
CREATE TRIGGER passStandard_check BEFORE INSERT ON Module
FOR EACH ROW 
BEGIN 
    DECLARE msg VARCHAR(255); /* << PUT THIS HERE */
    IF NEW.passStandard < 0 || NEW.passStandard > 1 THEN 
        set msg = concat('Trigger Error: Pass Standard: ', cast(NEW.passStandard as char));
        signal sqlstate '45000' set message_text = msg;
    END IF; /* << YOU WILL NEED THIS TOO (DONT FORGET THE SEMICOLON :D) */
END//
delimiter ;
#END TRIGGER