是否在更新Table1字段时从Table2中插入Table3中的行？

Question

我有一个包含字段“stock_id”的Table1Stocks和包含为每个股票订购的产品的Table2Productsordered以及一个Table3Store，每次有人更新Table1Stocks并将字段“Status”更改为“Received”时，我需要将Table2Productsordered中的行插入到Table3Store，其中Table2Productsordered字段“id_stock”=Table1Stocks“stock_id”的行中，Table2Productsordered中的“id_stock”是一个引用Table1Stocks“stock_id”的外我怎么能这么做？

表1更新第二行状态的股票

表2ProductSorded

输出插入新行的Table3Store

Answer 1

我认为MySQL不允许您为插入和更新定义一个触发器，所以这些触发器需要是单独的。语法如下所示：

CREATE TRIGGER trig_Table1Stocks_received_insert
    AFTER INSERT ON Table1Stocks
    FOR EACH ROW
BEGIN
    INSERT INTO Table3Store (Stocknum, ProductNum)
        SELECT p.id_stock, p.product_id
        FROM Table2Productsordered p
        WHERE NEW.stock_id = p.id_stock AND
              NEW.status = 'Received';
END;

update触发器类似：

CREATE TRIGGER trig_Table1Stocks_received_update
    AFTER UPDATE ON Table1Stocks
    FOR EACH ROW
BEGIN
    INSERT INTO Table3Store (Stocknum, ProductNum)
        SELECT p.id_stock, p.product_id
        FROM Table2Productsordered p
        WHERE NEW.stock_id = p.id_stock AND
              NEW.status = 'Received' AND
              OLD.status <> 'Received';
END;

这是一个dB<>小提琴。