MySQL触发器无法更新表-出现错误1442 [英] MySQL Trigger cannot update table - getting ERROR 1442

问题描述

我有以下触发器:

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

它将表的int_views和ext_views列求和，然后将它们除以总浏览量.

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?

推荐答案

症状是，您正在INSERT触发器内运行UPDATE(针对所有行)-都修改表格，这是不允许的.

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

请注意，这是一个BEFORE INSERT触发器，因为您想在将行写入表之前对其进行更改.

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

这篇关于MySQL触发器无法更新表-出现错误1442的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！