MySQL Trigger cannot update table - getting ERROR 1442

Question

I have the following trigger:

CREATE TRIGGER sum
AFTER INSERT
ON news
FOR EACH ROW
UPDATE news SET NEW.sum = (NEW.int_views + NEW.ext_views)/NEW.pageviews

It sums the int_views and ext_views column of a table and divides them by the total pageviews.

Whenever I try to add a new row to news, I get the following error:

ERROR 1442 (HY000) at line 3: Can't update table 'news' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

The trigger seems pretty simple to me. Is there a reason why the trigger fails to run?

Answer 1

The symptom is, that you are running an UPDATE (for all rows) inside a INSERT trigger - both modify the table, which is not allowed.

That said, if I guess the intention of your trigger correctly, you do not want to update all rows, but only the newly inserted row. You can achieve that easily with

CREATE TRIGGER sum
BEFORE INSERT
ON news
FOR EACH ROW
SET NEW.sum = (NEW.int_views + NEW.ext_views)/NEW.pageviews

Mind that this is a BEFORE INSERT trigger, as you want to change the row before it is written to the table.