MySQL:无法在触发器中使用信号

2022-01-01 00:00:00 signals sql database mysql triggers

我正在尝试使用 MySQL 触发器生成错误消息.下面是我的代码:

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".

对此有什么建议吗?除此之外，有些人说使用 SIGNAL 会遇到问题，因为它可能依赖于 MySQL 版本.有什么建议吗?

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?

推荐答案

set message_text 子句是信号语法的一部分 - 不应该有分号 (;) 它们之间.此外，它使用 = 运算符，而不是 :=:

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 ;

相关文章