带有触发器和结束语句的MariaDB错误

Question

我得到了一个MariaDB server version for the right syntax to use near 'END IF; END'

我试着删除那些错误的分号，我以为我是用正确的语法做的。

有人能找到我搞砸的地方吗？

/* Trigger on account table to prevent users entering a opening balance less than 51 */
DELIMITER $
CREATE TRIGGER account_opening_balance_check
BEFORE INSERT ON account
FOR EACH ROW 
BEGIN 
    IF NEW.opening_balance <= 50.00 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Your opening balance must be greater then 50.';
    ROLLBACK
    END IF;
END$
DELIMITER ;

Answer 1

您忘记了ROLLBACK命令的分号：

/* Trigger on account table to prevent users entering a opening balance less than 51 */
DELIMITER $
CREATE TRIGGER account_opening_balance_check BEFORE INSERT ON account
    FOR EACH ROW 
        BEGIN 
            IF NEW.opening_balance <= 50.00 THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Your opening balance must be greater then 50.';
                ROLLBACK;
            END IF;
        END$
DELIMITER ;

更新2021-12-14 11:00

如果试图使触发器停止执行插入操作，则需要故意将逻辑运行时错误放入其中。

我有一个10岁的帖子，告诉你如何做到这一点：在MySQL中触发以防止插入

我的想法来自于第11章，第254-256页的书MySQL存储过程编程的副标题‘验证数据与触发器’。

在这种情况下，只需注入无法运行的SQL (例如从不存在的表中进行选择)。

/* Trigger on account table to prevent users entering a opening balance less than 51 */
DELIMITER $
CREATE TRIGGER account_opening_balance_check BEFORE INSERT ON account
    FOR EACH ROW 
        BEGIN 
            IF NEW.opening_balance <= 50.00 THEN
                SELECT num FROM mysql.dummytable WHERE id = 1;
            END IF;
        END$
DELIMITER ;