在mysql中编辑其他表的触发器

qlzsbp2j  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(250)

我有以下触发器:

DELIMITER $$

DROP TRIGGER IF EXISTS trg_day_1_status_update$$
USE `tbl_user_status`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `tbl_user_status`.`trg_day_1_status_update` AFTER INSERT ON `tbl_tf_day_1` FOR EACH ROW
    BEGIN
    UPDATE tbl_user_status
        SET NEW.roadmap_day = tbl_tf_day_1.roadmap_day,
        SET NEW.user_status = "active",
        SET NEW.latest_submit = tbl_tf_day_1.submitted_on,
        SET NEW.latest_tf_id = tbl_tf_day_1.tf_id,
        SET NEW.d0 = 1,
        SET NEW.latest_cig_intake = tbl_tf_day_1.q_id_3_ftnd,
        SET NEW.latest_cigintake_submit = tbl_tf_day_1.submitted_on
    WHERE id_user = tbl_tf_day_1.id_user LIMIT 1;
    END;
$$
DELIMITER ;

但我一直有个错误:

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 'SET NEW.user_status = "active",
        SET NEW.latest_submit = tbl_tf_day_1.submitted' at line 5

我试过很多方法,包括删除字段前的'new',更改要插入的值,甚至在插入之前触发这个触发器。不知道怎么了,请帮帮我!

xoefb8l8

xoefb8l81#

你只需要使用 SET 关键字一次,然后用逗号分隔字段,如下所示:

UPDATE tbl_user_status SET
    NEW.roadmap_day = tbl_tf_day_1.roadmap_day,
    NEW.user_status = "active",
    NEW.latest_submit = tbl_tf_day_1.submitted_on,
    NEW.latest_tf_id = tbl_tf_day_1.tf_id,
    NEW.d0 = 1,
    NEW.latest_cig_intake = tbl_tf_day_1.q_id_3_ftnd,
    NEW.latest_cigintake_submit = tbl_tf_day_1.submitted_on
WHERE id_user = tbl_tf_day_1.id_user LIMIT 1;
tjjdgumg

tjjdgumg2#

这里有一些错误,set只需要声明一次,set语句的方向不正确,equals(=)左边的列应该是您要更新的表中要设置的列,equals(=)右边的列应该是您要更新的列。限定符(但不适用于'active'等常量),等号(=)右侧的where子句列名应以new开头。我还对您试图更新的表名有点怀疑,哪些seeems要与create语句中的db/schema共享一个名称。

相关问题