Why Does Updating a Table Inside a Trigger Cause Error #1442, and How Can This Be Solved?

Trigger Pitfall: Updating a Table Referenced by the Trigger

Updating the same table within a trigger can lead to issues. One such instance is presented where an attempt is made to update the products_score table using a trigger that executes after an update.

The trigger, as shown in the provided code, tries to calculate and update the votes_total column based on the values of other columns (votes_1, votes_2, ..., votes_5) in the same products_score table. However, when this update is triggered, an error occurs:

#1442 - Can't update table 'products_score' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Solution: Using a BEFORE Trigger

To resolve this problem, the trigger must be modified to execute before the row update, not after. By executing before the update, the trigger can access and modify the new values being assigned to the votes_total column.

The revised trigger using a BEFORE update clause is as follows:

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, the trigger successfully calculates and updates the votes_total column before the actual row update occurs, resolving the error and allowing the desired functionality.

The above is the detailed content of Why Does Updating a Table Inside a Trigger Cause Error #1442, and How Can This Be Solved?. For more information, please follow other related articles on the PHP Chinese website!