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?
那么,我到底怎么才能让它发挥作用呢?
2 个解决方案
#1
26
If you change your trigger to BEFORE
instead of AFTER
you 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
;
#2
7
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.
或者使用存储过程更新表。
#1
26
If you change your trigger to BEFORE
instead of AFTER
you 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
;
#2
7
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.
或者使用存储过程更新表。