(MySQL) Can't update table 'PARTS' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Question

I want the amount to be updated if the entry is in the table else a new one inserts.

delimiter $$
CREATE TRIGGER t 
BEFORE INSERT 
ON PARTS
for each row

BEGIN
  if exists (SELECT Part_name FROM PARTS where part_name = new.part_name) then
      UPDATE PARTS SET amount=amount+NEW.amount WHERE 
      Part_name=NEW.Part_name;
   end if;
END $$
delimiter ;

When I'm trying:

mysql> insert into PARTS(Part_name,amount,Storage_ID) values('barley',1000,1);

Then I got this error:

ERROR 1442 (HY000): Can't update table 'PARTS' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

What is my fault? Any idea?

Answer 1

You don't need a trigger. You want insert on duplicate key update:

insert into PARTS(Part_name, amount, Storage_ID)
    values('barley', 1000, 1)
    on duplicate key update amount = amount + values(amount);

For this to work, you need a unique index or primary key on parts(part_name). Or perhaps on parts(part_name, storage_id). It is unclear how that fits in.