MySQL触发器定义-1064错误 [英] MySQL trigger definition - 1064 error

问题描述

我建议的触发器：

#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

但出现以下错误：

错误代码：1064。您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以在DECLARE msg VARCHAR（255）附近使用正确的语法；在第1行上创建触发器passStandard_check，然后在Mod'上插入

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 不起作用，则添加 END

Adding in END IF does not make any difference.

passStandard 设置为 INT NOT NULL 。

我已经删除了 DECLARE 语句在 BEGIN 之后：

I've moved the DECLARE statement after the 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

但我仍然收到此错误：

错误代码：1064。您的SQL语法有错误；查看与您的MySQL服务器版本相对应的手册以获取正确的语法，以在sqlstate 45000附近使用message_text = msg;万一; END'在第7行

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

推荐答案

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

You need to declare your variable "msg" inside your procedure and use 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

这篇关于MySQL触发器定义-1064错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！