我有一个包含字段“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
我认为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<>小提琴。
