MySQL Trigger cannot update table - getting ERROR 1442

If you try to update/insert on the same table that cause trigger to fire do not use the common sql command like

-> UPDATE TABLE_NAME SET COLUMN_NAME = VALUE WHERE CONDITION_LIST;  
-> INSERT INTO TABLE_NAME VALUES("VALUE1","VALUE2");

This will not work. Only use set to assign the value of the column you update.

Example:

CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE ON table_name
FOR EACH ROW
SET NEW.COLUMN_NAME = "VALUE";

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.

Tags:

Mysql