在同一表上更新后在触发器中更新表 [英] Updating table in trigger after update on the same table

问题描述

在同一张表上进行更新后，如何在触发器中更新表的列?
这是触发因素:

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)

现在当我像更新表一样

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?

推荐答案

如果将触发器更改为BEFORE而不是AFTER，则可以这样操作:

If you change your trigger to BEFORE instead of AFTER you could do it like this:

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
;

这篇关于在同一表上更新后在触发器中更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！