MySQL 触发器无法更新表 - 出现错误 1442
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12203859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Trigger cannot update table - getting ERROR 1442
提问by egidra
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_viewsand ext_viewscolumn 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?
触发器对我来说似乎很简单。触发器无法运行是否有原因？
回答by Eugen Rieck
The symptomis, that you are running an UPDATE(for all rows) inside a INSERTtrigger - both modify the table, which is not allowed.
该症状是，你正在运行UPDATE一个内部（所有行）INSERT触发-无论是修改表，这是不允许的。
That said, if I guess the intention of your trigger correctly, you do not want to update allrows, 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 INSERTtrigger, as you want to change the row before it is written to the table.
请注意，这是一个BEFORE INSERT触发器，因为您希望在将行写入表之前更改该行。
回答by George Mulokozi
If you try to update/insert on the same table that cause trigger to fire do not use the common sqlcommand 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";
回答by Umar Niazi
I was in a similar condition where I had to run two triggers:
我处于类似的情况，我不得不运行两个触发器：
- UPDATE 3 fields on INSERTing a new ROW
- UPDATE 3 fields on UPDATEing a ROW
- 在插入新行时更新 3 个字段
- 在 UPDATEing a ROW 上更新 3 个字段
After lot of efforts, I was finally able to write the TRIGGER in following way:
经过一番努力，我终于能够以如下方式编写TRIGGER：
FOR updating values on INSERT
用于更新 INSERT 上的值
CREATE TRIGGER `INSERT_DISCOUNT_SERVICES` BEFORE INSERT ON `services`
FOR EACH ROW SET
NEW.discount_5_rate = (NEW.ndis_rate*0.05),
NEW.discount_10_rate=(NEW.ndis_rate*0.10),
NEW.discount_15_rate=(NEW.ndis_rate*0.15)
Similarly
相似地
FOR updating values on UPDATE
FOR 在 UPDATE 上更新值
CREATE TRIGGER `UPDATE_DISCOUNTS_SERVICES` BEFORE UPDATE ON `services`
FOR EACH ROW SET
NEW.discount_5_rate = (NEW.ndis_rate*0.05),
NEW.discount_10_rate=(NEW.ndis_rate*0.10),
NEW.discount_15_rate=(NEW.ndis_rate*0.15)
