how to make a trigger for another table mysql

i have 2 tables

tbl 1

id  product_id  quantity    status

tbl 2

id  product_id  status

with tbl1.product_id = tbl2.product_id, i want to make a trigger, so if i update tbl1.status in tbl1.product_id, then it will become automatically update status for tbl2.product_id with the same value of the status

so if let’s say the table was like this

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 |
    +----+------------+--------+

If i want to update tbl1.product_id = 15 into status = 0, then tbl2.product_id = 15 will become status = 0 too like this

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 |
        +----+------------+--------+

Answer

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

Leave a Reply

Your email address will not be published. Required fields are marked *