MySQL: Unable to use SIGNAL in Trigger

问题:

I am trying to generate an error message using the MySQL trigger. Below is my code:

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Initial_Fees`
FOR EACH ROW
BEGIN
    IF ((SELECT Activation from Portfolio WHERE idPortfolio = New.idPortfolio)=false) THEN
        SIGNAL SQLSTATE '45000';
        SET MESSAGE_TEXT := 'Disabled Thing';
    END IF;
END$$   
DELIMITER ; 

But this always generates an error. I don't know what the error is, because It is not saying anything about the error, it is just "Error".

Any advice on this please? Apart from that, some people say using SIGNAL is subjected to issues because it can be MySQL version dependent. Any advice?

回答1:

the set message_text clause is part of the signal syntax - there should not be a semicolon (;) between them. Additionally, it uses a = operator, not a :=:

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Initial_Fees`
FOR EACH ROW
BEGIN
    IF ((SELECT Activation from Portfolio WHERE idPortfolio = New.idPortfolio)=false) THEN
        SIGNAL SQLSTATE '45000' -- Note: no semicolon
        SET MESSAGE_TEXT = 'Disabled Thing'; -- Note the = operator
    END IF;
END$$   
DELIMITER ;