MySQL Trigger无法更新表 - 获取ERROR 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.

它将表的int_views和ext_views列相加,并将它们除以总页面浏览量。

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?

触发对我来说似乎很简单。触发器无法运行的原因是什么?

2 个解决方案

#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.

症状是,您在INSERT触发器内运行UPDATE(对于所有行) - 都修改了表,这是不允许的。

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.

请注意,这是一个BEFORE INSERT触发器,因为您希望在将行写入表之前更改该行。

#2

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

如果您尝试在同一个表上更新/插入导致触发器触发,请不要使用常见的sql命令

-> 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.

这不行。仅使用set来指定您更新的列的值。

Example:

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

#1