mysql - 如何为另一个表mysql创建触发器

Question

我有 2 张桌子

表 1

id  product_id  quantity    status

表 2

id  product_id  status

使用 tbl1.product_id = tbl2.product_id，我想触发，所以如果我更新 tbl1.product_id 中的 tbl1.status，那么它将自动更新 tbl2.product_id 的状态，并具有相同的状态值

所以如果让我们说桌子是这样的

tbl1

    +----+------------+----------+--------+
    | id | product_id | quantity | status |
    +----+------------+----------+--------+
    |  1 |         15 |        2 |      1 |
    |  2 |         16 |        4 |      1 |
    +----+------------+----------+--------+

tbl2

    +----+------------+--------+
    | id | product_id | status |
    +----+------------+--------+
    |  1 |         15 |      1 |
    |  2 |         16 |      1 |
    +----+------------+--------+

如果我想将 tbl1.product_id = 15 更新为 status = 0，那么 tbl2.product_id = 15 也会像这样变为 status = 0

tbl1
    
        +----+------------+----------+--------+
        | id | product_id | quantity | status |
        +----+------------+----------+--------+
        |  1 |         15 |        2 |      0 |
        |  2 |         16 |        4 |      1 |
        +----+------------+----------+--------+

    tbl2
    
        +----+------------+--------+
        | id | product_id | status |
        +----+------------+--------+
        |  1 |         15 |      0 |
        |  2 |         16 |      1 |
        +----+------------+--------+

标签： mysqltriggers

Answer 1

CREATE TRIGGER update_table2
AFTER UPDATE
ON table1
FOR EACH ROW
    UPDATE table2
    SET status = NEW.status
    WHERE product_id = NEW.product_id;