MySQL 在同一表上更新后更新触发器中的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4547465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating table in trigger after update on the same table
提问by nhaa123
How can I update table's column in a trigger after update on the same table?
Here's the trigger:
在同一个表上更新后如何更新触发器中的表列？
这是触发器：
CREATE TRIGGER upd_total_votes AFTER UPDATE ON products_score
FOR EACH ROW
UPDATE
products_score
SET
products_score.votes_total =
(SELECT
(votes_1 + votes_2 + votes_3 + votes_4 + votes_5)
FROM
products_score
WHERE
id = new.id)
Now when I update the table like
现在当我更新表格时
UPDATE products_score SET votes_1 = 5 WHERE id = 0
this doesn't work, as I get the following:
这不起作用，因为我得到以下信息：
#1442 - Can't update table 'products_score' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
So how on earth I can get this to work?
那么我到底如何才能让它发挥作用呢？
回答by Klaus Byskov Pedersen
If you change your trigger to BEFOREinstead of AFTERyou could do it like this:
如果您将触发器更改为BEFORE而不是AFTER您可以这样做：
CREATE TRIGGER upd_total_votes BEFORE UPDATE ON products_score
FOR EACH ROW
BEGIN
SET new.votes_total = new.votes_1 + new.votes_2 + new.votes_3 + new.votes_4 + new.votes_5
END
;
回答by Chandu
You can't have this the way you have setup because a trigger can't query other rows of the same table that it is defined on. Istead you can use a Before Update Trigger toachieve what you want:
您不能按照您设置的方式进行设置，因为触发器无法查询定义它的同一表的其他行。相反，您可以使用更新前触发器来实现您想要的：
CREATE TRIGGER upd_total_votes BEFORE UPDATE ON products_score FOR EACH ROW
BEGIN
SET NEW.votes_total = NEW.votes_1 + NEW.votes_2 + NEW.votes_3 + NEW.votes_4 + NEW.votes_5;
END;
Or use a stored procedure to update the table.
或者使用存储过程来更新表。
