mysql 错误 1442_MySQL触发器无法更新表-得到错误1442

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?

解决方案

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.