Need to update values in table which has caused trigger (Error 1442)

Question

I'm trying to update the values in table which has caused the trigger. I know the reason of the error, but i need solution how to do it. I need to update old value of product quantity if new.id = id in table which if it's already exists. I can't update it in trigger and in the stored function. I'v spent hours of thinking about solution, how can i do it?

DROP TRIGGER IF EXISTS insert_product_trigger;
delimiter //
CREATE TRIGGER insert_product_trigger
BEFORE INSERT ON product
FOR EACH ROW
BEGIN
IF exists(select id_product from product where id_product = new.id_product) THEN
update product set quantity = quantity + new.quantity where id_product = new.id_product;
END IF;
END; //
delimiter ;

I need to update the old value of quantity if product exists (just add new quantity which is in insert to the old one) and cancel insert or insert it if not.

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

Answer 1

If product has a unique or primary key forget the trigger and

insert into product(id,qty) values (x,y)
on duplicate key update qty = qty + y;

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Answer 2

You can't use a trigger to do what you're trying to do. A trigger cannot stop a DML statement (in this case, an INSERT) from proceeding, which is what you seem to want to do here. Instead, you need to use the correct DML statement:

INSERT INTO PRODUCT
  (ID_PRODUCT, QUANTITY)
VALUES
  (some_id_product, some_quantity)
ON DUPLICATE KEY UPDATE QUANTITY = QUANTITY + some_quantity