mysql创建触发器1064错误,MySQL触发器定义-1064错误

My proposed trigger:

#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

But I get the following error:

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

Adding in END IF does not make any difference.

passStandard is set to an INT NOT NULL.

Edit

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

But I still get this error:

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

解决方案

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