在插入触发器之后更新另一个表的列值

Question

我有两个不同的表：OrderItem和Item。两个表都有一个名为"；碎片"；。在表OrderItem的情况下，件数是OrderItem列表中每个项目的数量，而在表Item中，件数表示产品的剩余件数(例如仓库中(。

因此，我正在构建一个插入后触发器，该触发器将在OderItem中完成插入后更新Items中的片段

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.。我做错了什么？如何解决？

编辑：我通过在插入语句INSERT INTO ORDERITEM VALUES(1,(select i.ID from Item where i.Name = 'Item1'),5);中进行选择来获得ItemID

Answer 1

这是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<gt；小提琴这里