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 summarizes the column int_views and ext_views table and divides them into total pageviews.

Whenever I try to add a new line to the 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 does not start?

Answer 1

Symbol is that you run UPDATE (for all rows) inside an INSERT trigger - both modify a table that is not allowed.

However, if I correctly guess your trigger, you do not want to update all rows, but just the row you just inserted. You can easily achieve this with

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

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

Answer 2

If you try to update / insert into the same table that triggered the trigger, do not use the general sql command, for example

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

This will not work. Use only set to set the value of the updated column.

Example:

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