Updating a Table in a Trigger After Update on the Same Table

In SQL, updating a table in a trigger after an update on the same table presents a potential issue. This is because the table is already locked for the update operation, and attempting to access the table within a trigger executed as part of the same transaction can result in a conflict.

To circumvent this restriction, you can update the affected columns in the trigger by using the BEFORE option instead of AFTER. This allows you to update the table's columns before the original update operation occurs, ensuring that the necessary changes are made to the table before the transaction is finalized.

Consider the following example:

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 example, the trigger will execute before each update operation on the products_score table. It will calculate the new value for the votes_total column based on the values of the updated columns (votes_1 to votes_5) and store it in the new.votes_total virtual column provided by the trigger context.

When you update the table, the trigger will ensure that the votes_total column is updated accordingly, even though the update statement doesn't explicitly update the votes_total column itself.