问题描述
我有以下触发器:
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!