在插入觸發器后更新另一個表的列值-有解無憂

我有兩個不同的表：OrderItem 和 Item。兩個表都有一個名為“pieces”的列。在表格的情況下，OrderItem件是 OrderItem 串列中每個專案的數量，在表格Item中，件代表產品的剩余件（例如在倉庫中）。

所以我正在構建一個插入后觸發器，它將在Items插入完成后更新片段OderItem

delimiter //

create trigger TrgItemPieces
after insert on OrderItem
for each row
begin
 declare PiecesNew int;
  select a.pieces - new.Pieces into PiecesNew from Item a where a.ID = new.ItemId;
  update Item set pieces = PiecesNew where id = new.ItemId;
 end//

delimiter ;

但是當我進行插入時， INSERT INTO OrderItem VALUES ([OrderID],[ItemID],Pieces);我收到以下錯誤：Error Code: 1442. Can't update table 'Item' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.. 我做錯了什么以及如何解決？

編輯：我通過在插入陳述句中進行選擇來獲取 ItemID INSERT INTO ORDERITEM VALUES(1,(select i.ID from Item where i.Name = 'Item1'),5);

uj5u.com熱心網友回復：

這是 MySql 避免死鎖的安全措施。

但是您可以通過不直接從將通過觸發器更新的表中進行選擇來避免它。

例如通過使用變數。

CREATE TRIGGER TrgOrderItemAftIns
AFTER INSERT ON ORDERITEM
FOR EACH ROW
BEGIN
  INSERT INTO DEBUG_TABLE (MSG) VALUES (CONCAT('TrgOrderItemAftIns: ', NEW.ITEMID,',',NEW.PIECES));
  UPDATE ITEM SET PIECES = PIECES - NEW.PIECES WHERE ID = NEW.ITEMID;
END;

SET @OrderID = (select ID from TabOrder where Name = 'Order 1');
SET @ItemID  = (select ID from ITEM where Name = 'Item1');
INSERT INTO ORDERITEM (OrderID, ITEMID, PIECES) VALUES 
(@OrderID, @ItemID, 5);

SET @OrderID = (select ID from TabOrder where Name = 'Order 1');
SET @ItemID  = (select ID from ITEM where Name = 'Item2');
INSERT INTO ORDERITEM (OrderID, ITEMID, PIECES) VALUES 
(@OrderID, @ItemID, 1);

SET @OrderID = (select ID from TabOrder where Name = 'Order 2');
SET @ItemID  = (select ID from ITEM where Name = 'Item1');
INSERT INTO ORDERITEM (OrderID, ITEMID, PIECES) VALUES 
(@OrderID, @ItemID, 3);

關于db<>fiddle的演示在這里

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/411782.html

標籤：

上一篇：如何從MySQLConnector/J直接檢索UTCOffsetDateTime

下一篇：每行的PHP洗掉按鈕/更改該行表中的值