CODEWITHSUNDEEP
mysql
Matthew Chambers
Matthew Chambers

Reputation: 877

Mysql Prevent insert with insert trigger condition not met

I am trying to setup a trigger so an insert does not occur if a condition is not met.

I thought the below was the way to do it but i am not sure

I am getting an error /* SQL Error (1407): Bad SQLSTATE: '45000 ' */

Can anyone let me know why I am getting this error and the best way for me to prevent an insert if the condition is not met in mysql.

DELIMITER $$
SHOW WARNINGS$$
USE `warrington_central`$$ 

CREATE TRIGGER before_insert_image_comment_section_check
BEFORE INSERT ON image_comment FOR EACH ROW
BEGIN

 DECLARE error_msg varchar(255);
IF New.section != (SELECT id from section where section = "image")
    THEN SET    error_msg = "Cannot insert a comment into this section as it is the wrong section type";
    SIGNAL SQLSTATE '45000 'SET MESSAGE_TEXT = error_msg;


END IF;

END
$$

SHOW WARNINGS$$

Upvotes: 0

Views: 2946

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

SQLSTATE is required to be a a 5 character string unless previously declared using DECLARE ... CONDITION;

SIGNAL SQLSTATE '45000 'SET MESSAGE_TEXT = error_msg;

You're trying to set SQLSTATE to '45000 ' (note the space) which is 6 characters long. Fix the spacing and you should not see the message again (it is also reflected in your error message, but the space is a bit hard to see)

Upvotes: 4

Related Questions