Update Trigger on same table MySQL 5.6

Question

I have a table 'foods' with columns 'featured', 'restaurant_id', 'retaurant_stock'. How can I update column 'restaurant_stock' using trigger or any other solution available on mySQL version 5.6 after update of column 'featured' on the same table on a condition? Calculated columns are not available on mySQL version 5.6.

Here's my trigger:

CREATE TRIGGER update_restaurant_stock BEFORE UPDATE ON foods
OR EACH ROW
BEGIN
IF NEW.featured = 1 THEN
UPDATE `foods` SET NEW.restaurant_stock = (NEW.restaurant_id);
ELSE
UPDATE `foods` SET NEW.restaurant_stock = 0;
END IF;
END

Now when I update column 'featured' I get the following error message:

#1442 - Can't update table 'foods' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Thanks for you help!

Answer 1

You can't update the current row that way, you can use set to change the new values

DELIMITER $$
CREATE TRIGGER update_restaurant_stock BEFORE UPDATE ON foods
OR EACH ROW
BEGIN
IF NEW.featured = 1 THEN
    SET NEW.restaurant_stock = (NEW.restaurant_id);
ELSE
    SET NEW.restaurant_stock = 0;
END IF;
END$$
DELIMITER ;