MySQL: unable to use SIGNAL in triggers
I am trying to create an error message using a 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 gives rise to an error. I don't know what the error is, because it says nothing about the error, it's just "Error".
Any advice on this? Also, some people say usage is SIGNAL
prone to problems because it depends on the MySQL version. Any advice?
+3
source to share
1 answer
the clause set message_text
is part of the signal syntax - there must be no semicolon ( ;
) between them . Also, it uses an operator =
rather than :=
:
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 ;
+5
source to share