mysql - 语法错误：创建 MySql 触发器时在“”附近使用正确的语法

问题描述

我有一个相当复杂的触发器

CREATE TRIGGER update_table AFTER UPDATE ON `table`
            FOR EACH ROW BEGIN
                IF NEW.value != OLD.value AND NEW.value != 0 THEN
                    IF NEW.value > 0 AND NEW.value < 100 THEN
                        UPDATE table2 SET section1_value = section1_value + NEW.value - OLD.value WHERE id = 0;
                    ELSEIF NEW.value >= 100 AND NEW.value < 1000 THEN
                        UPDATE table2 SET section2_value = section2_value + NEW.value - OLD.value WHERE id = 0;
                    ELSEIF NEW.value >= 1000 AND NEW.value < 5000 THEN
                        UPDATE table2 SET section3_value = section3_value + NEW.value - OLD.value WHERE id = 0;
                    ELSEIF NEW.value >= 5000 AND NEW.value < 10000 THEN
                        UPDATE table2 SET section4_value = section4_value + NEW.value - OLD.value WHERE id = 0;
                    ELSEIF NEW.value >= 10000 AND NEW.value < 20000 THEN
                        UPDATE table2 SET section4_value = section4_value + NEW.value - OLD.value WHERE id = 0;
                    ELSEIF NEW.value >= 20000 THEN
                        UPDATE table2 SET section6_value = section6_value + NEW.value - OLD.value WHERE id = 0;
                    END IF;
                ELSE IF NEW.value = 0 THEN
                    IF OLD.value > 0 AND OLD.value < 100 THEN
                        UPDATE table2 SET section1_count = section1_count - 1, section1_value = section1_value - OLD.value WHERE id = 0;
                    ELSEIF OLD.value >= 100 AND OLD.value < 1000 THEN
                        UPDATE table2 SET section2_count = section2_count - 1, section2_value = section2_value - OLD.value WHERE id = 0;
                    ELSEIF OLD.value >= 1000 AND OLD.value < 5000 THEN
                        UPDATE table2 SET section3_count = section3_count - 1, section3_value = section3_value - OLD.value WHERE id = 0;
                    ELSEIF OLD.value >= 5000 AND OLD.value < 10000 THEN
                        UPDATE table2 SET section4_count = section4_count - 1, section4_value = section4_value - OLD.value WHERE id = 0;
                    ELSEIF OLD.value >= 10000 AND OLD.value < 20000 THEN
                        UPDATE table2 SET section4_count = section4_count - 1, section4_value = section4_value - OLD.value WHERE id = 0;
                    ELSEIF OLD.value >= 20000 THEN
                        UPDATE table2 SET section6_count = section6_count - 1, section6_value = section6_value - OLD.value WHERE id = 0;
                    END IF;
                END IF;
            END;

我有一个类似的触发器可以正常工作，我正在使用该/DB::unprepared()方法在 Laravel 迁移中创建这些触发器，但是当我尝试创建这个触发器时出现错误：


SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 32 (SQL: <query_text>)

我不知道出了什么问题，我的猜测是它与分号有关，但我不知道是什么。有什么帮助或建议吗？

标签： mysqllaraveleloquent

mysql - 语法错误：创建 MySql 触发器时在“”附近使用正确的语法

问题描述

解决方案

推荐阅读