How to Resolve the MySQL Trigger-Induced Table Update Error?

Trigger-Induced Table Update Dilemma

A common challenge arises when attempting to update the same table within its own trigger after an update. This becomes evident when a trigger is designed to modify a specific column in the table based on changes to other columns within the same row.

Example: Calculating Column Totals

Consider the following scenario, where we want to update the "votes_total" column in the "products_score" table every time a score is modified:

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)

However, upon executing an update on the table, we encounter an 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.

This error is caused by the circular reference in the trigger's logic. The update statement attempts to modify the "votes_total" column, which the trigger also relies on to calculate the new total.

Solving the Dilemma

To circumvent this issue, we can modify the trigger to execute before the update instead of 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
;

In this modified trigger, we calculate the new "votes_total" value and assign it to the "new" row object before the actual update takes place. This allows the update statement to proceed without encountering a circular reference issue.

The above is the detailed content of How to Resolve the MySQL Trigger-Induced Table Update Error?. For more information, please follow other related articles on the PHP Chinese website!