How to Resolve 'Can't update table ... because it is already used' Errors in MySQL Triggers?

Trigger Interruption while Updating Table: Resolving the Issue

In database programming, it is sometimes necessary to update a table's column using a trigger after an update operation on the same table. However, this can lead to an error if the trigger attempts to update the table while it is already being updated.

Consider the following 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)

When an update operation is performed using this trigger, the error "#1442 - Can't update table 'products_score' in stored function/trigger because it is already used by statement which invoked this stored function/trigger" is encountered.

To resolve this issue, change the trigger timing to BEFORE instead of AFTER. This allows the trigger to execute before the update operation, avoiding the table being locked during the update. The updated trigger should look 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
;

With this modified trigger, the table's column can be successfully updated after the update operation.

The above is the detailed content of How to Resolve 'Can't update table ... because it is already used' Errors in MySQL Triggers?. For more information, please follow other related articles on the PHP Chinese website!