Update column value of another table in After Insert Trigger-CodePudding

I have two different tables: OrderItem and Item. Both tables have a column named "pieces". In the case of table OrderItem the pieces are the amount of each item in the OrderItem's list and in the table Item the pieces represent the remaining pieces of a product (in a warehouse for example).

So I am building an after insert trigger that will update the pieces in Items after an insert is done in 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 ;

But when I do an insert INSERT INTO OrderItem VALUES ([OrderID],[ItemID],Pieces); I get the following error: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.. What am I doing wrong and how to fix it?

EDIT: I get the ItemID by doing a select in the insert statement INSERT INTO ORDERITEM VALUES(1,(select i.ID from Item where i.Name = 'Item1'),5);

CodePudding user response：

It's a safety measure from MySql to avoid deadlocks.

But you can avoid it by not directly selecting from the table that will be updated via the trigger.

For example by using variables.

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);

Demo on db<>fiddle here