SQL Error (1442) , any solution?

Question

I created this trigger in MySQL (after update on table product):

BEGIN
 set @parentid := (select parent_id from product where product_id = new.product_id);
 if (old.price <> new.price) then
  update product set price=new.price where product_id = @parentid and price > new.price;
 end if;
END

But, when I update price, I get this error

: SQL Error (1442) Can't update table 'product' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. */

I created two tables and two triggers like:

Trigger 1 (after update on table product):

BEGIN
 if (old.price <> new.price) then
    insert into product_price
    (product_id, price , date_added)
    values
    (new.product_id, new.price, SYSDATE());
 end if;
END

Trigger 2 (after insert on table product_price):

BEGIN
 SET @parentid := (select parent_id from product where product_id = new.product_id);
 if (@parentid >0) then
  update  product set price=new.price where product_id in (select @parentid);
 end if;
END

But, I got that error again.

I really need to update parent price when child price changed, do you have any solution?

Thanks.

Answer 1

You cannot use triggers, since you need to update the same table that is being updated. You need to move this logic into the application code or use a stored procedure instead of triggers.