同一表MySQL 5.6上的Update触发器

Question

我有一个'foods‘表，其中有'featured’、'restaurant_id‘、'retaurant_stock’列。如何使用触发器或mySQL版本5.6上提供的任何其他解决方案，在条件下更新同一表上的'featured‘列后，更新列'restaurant_stock’？计算列在mySQL 5.6版上不可用。

这是我的触发器：

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

现在，当我更新'featured‘列时，我得到以下错误消息：

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

​

​

感谢您的帮助！

Answer 1

您不能以这种方式更新当前行，您可以使用set更改新值

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 ;

Answer 2

CREATE TRIGGER tr_bu_foods
BEFORE UPDATE 
ON foods
FOR EACH ROW
-- update restaurant_stock 
SET NEW.restaurant_stock = NEW.restaurant_id * (NEW.featured = 1);