Updating table in trigger after update on the same table

Question

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?

Klaus Byskov Pedersen · Accepted Answer · 2010-12-28 16:26:59Z

31

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
;

answered Dec 28, 2010 at 16:26

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chandu · Accepted Answer · 2010-12-28 16:27:40Z

8

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.

answered Dec 28, 2010 at 16:27

Chandu

83.2k19 gold badges136 silver badges136 bronze badges

1 Comment

vladiastudillo Over a year ago

same error message calling a stored procedure inside the trigger after update

Collectives™ on Stack Overflow

Updating table in trigger after update on the same table

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related