mysql - 如何在 UPDATE 中更新具有相同 prodID 的行？

Question

我收到以下错误:“无法更新存储函数/触发器中的表‘Product’，因为它已被调用此存储函数/触发器的语句使用。”

CREATE TRIGGER `SalesDB`.`updateSum` AFTER UPDATE ON SalesDB.Product FOR EACH ROW
BEGIN
  DECLARE totalStock INT;

  SELECT SUM(stock) 
      INTO totalStock
      FROM Product
      WHERE Product.prodID= NEW.prodID;

  UPDATE Product SET StockSum = totalStock
      WHERE  prodID = NEW.prodID;
END;

我知道存在递归问题，但我该怎么做呢？有没有办法使用触发器来做这种事情？这是否应该在存储过程中完成，我如何在更新产品事件上运行 stroed 过程？

我需要更新产品的库存，然后遍历具有相同 prodID 的所有行计算 SUM 并更新所有行的 SumTotal 字段。

Answer 1

已更新不幸的是，由于触发器实现的限制，您不能在 MySql 中执行此操作。

可能的选择是:

1. 创建存储过程并在每次更新 Product 表后调用它
2. 将 stockSum 存储在单独的表中(例如 ProductStock)，使用触发器(INSERT、UPDATE)更新 stockSum 值，创建连接的 Product 和 ProductStock 的 View
3. 如果可以接受一些延迟，则使用 MySql 事件或 cron 作业更新 Product 表中的 stockSum

恕我直言，最好的方法是 2 分离 Product 和 ProductStock 表:

ProductStock 可能看起来像这样

CREATE TABLE ProductStock
(
  prodID int NOT NULL PRIMARY KEY,
  stockSum decimal(12, 3)
);

现在触发

CREATE TRIGGER tg_product_update
AFTER UPDATE ON Product 
FOR EACH ROW
CALL sp_updateProductStock(NEW.prodID);

CREATE TRIGGER tg_product_insert
AFTER INSERT ON Product 
FOR EACH ROW
CALL sp_updateProductStock(NEW.prodID);

和一个存储过程

CREATE PROCEDURE sp_updateProductStock(IN ProductID INT)
REPLACE INTO ProductStock (prodID, stockSum) 
SELECT ProductID, 
       (SELECT SUM(COALESCE(stock, 0)) 
          FROM Product
         WHERE prodID = ProductID);

最后一个 View

CREATE VIEW vw_Product AS
SELECT p.id, p.prodID, ... , p.stock, s.stockSum
  FROM Product p JOIN ProductStock s
    ON p.prodID = s.prodID

这是 SQLFiddle 例子