时间戳

首页 > 解决方案 > 如果条件显示语法错误，则在内部声明 - MYSQL 触发器

mysql - 如果条件显示语法错误，则在内部声明 - MYSQL 触发器

问题描述

我尝试在插入之前使用触发器更新一些列

  DROP TRIGGER IF EXISTS update_p_posts_places;
DELIMITER $$
CREATE TRIGGER update_p_posts_places BEFORE
INSERT
ON
    `p_posts` FOR EACH ROW
BEGIN      
    DECLARE
        p_post_group_id_ int;
    SELECT
        `p_post_subgroup`.`p_post_group_id`
    INTO
        p_post_group_id_
    FROM
        `p_post_subgroup`
    WHERE
        `p_post_subgroup`.`p_post_subgroup_id` = NEW.p_post_subgroup_id;
        IF(p_post_group_id_ = 5) THEN
        BEGIN
        DECLARE
            place1_id_ int;
            place2_id_ int;
            place3_id_ int;
            place4_id_ int;
            place5_id_ int;
        SELECT
            `Places`.`place1_id`,
            `Places`.`place2_id`,
            `Places`.`place3_id`,
            `Places`.`place4_id`,
            `Places`.`place5_id`
        INTO
            place1_id_, place2_id_, place3_id_, place4_id_, place5_id_
        FROM
            `Places`
        WHERE
            `Places`.`place5_id` = NEW.p_post_place_id LIMIT 1;
    SET NEW.place5_id = place5_id_;
    SET NEW.place1_id = place1_id_;
    SET NEW.place2_id = place2_id_;
    SET NEW.place3_id = place3_id_;
    SET NEW.place4_id = place4_id_;
    END $$
    ELSE
SET NEW.place5_id = NULL;
END IF;
END $$
DELIMITER ;

它显示了一些语法错误。

#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在 'DECLARE place1_id_ int; 附近使用的正确语法；place2_id_ int; ' 在第 20 行

标签： mysql

解决方案

这是一个工作触发器。我测试了在 MySQL 5.7 上创建它。

CREATE TRIGGER update_p_posts_places BEFORE INSERT ON `p_posts`
FOR EACH ROW
BEGIN      
    -- all declarations must be before any other statements
    DECLARE p_post_group_id_, place1_id_, place2_id_, place3_id_,
        place4_id_, place5_id_ int;
    SELECT
        `p_post_subgroup`.`p_post_group_id`
    INTO
        p_post_group_id_
    FROM
        `p_post_subgroup`
    WHERE
        `p_post_subgroup`.`p_post_subgroup_id` = NEW.p_post_subgroup_id;
    IF(p_post_group_id_ = 5) THEN
        SELECT
            `Places`.`place1_id`,
            `Places`.`place2_id`,
            `Places`.`place3_id`,
            `Places`.`place4_id`,
            `Places`.`place5_id`
        INTO
            place1_id_, place2_id_, place3_id_, place4_id_, place5_id_
        FROM
            `Places`
        WHERE
            `Places`.`place5_id` = NEW.p_post_place_id LIMIT 1;
        SET NEW.place5_id = place5_id_;
        SET NEW.place1_id = place1_id_;
        SET NEW.place2_id = place2_id_;
        SET NEW.place3_id = place3_id_;
        SET NEW.place4_id = place4_id_;
    ELSE
        SET NEW.place5_id = NULL;
    END IF;
END $$

您可以将一个DECLARE用于多个局部变量，但您必须使用 like var1, var2, var3, ... int。换句话说，在最后只为类型命名一次。请参阅文档：https ://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html

不需要BEGIN..END内部，IF并且绝对不要$$在最后一个之后使用，END因为这将在完成之前终止解析器对整个 CREATE TRIGGER 语句的解释。

推荐阅读