How Can I Prevent 'Can't update table in stored function/trigger' Errors When Using After Update Triggers?

Trigger Updates After Same Table Update

In database systems, triggers are used to automatically perform specific actions when certain events occur within a table. One common challenge arises when attempting to update the same table from within a trigger after an update on that table.

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 trying to update the table as follows:

UPDATE products_score SET votes_1 = 5 WHERE id = 0;

the trigger fails with 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.

To resolve this, the trigger must be modified to execute before the update event, rather than after. This allows the trigger to update the table before the original update statement is applied:

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 modification, updating the table as before will successfully trigger the votes_total column to be updated.

The above is the detailed content of How Can I Prevent 'Can't update table in stored function/trigger' Errors When Using After Update Triggers?. For more information, please follow other related articles on the PHP Chinese website!