MySQL - 插入后更新同一个表的触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12877732/
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 for updating same table after insert
提问by th3an0maly
Here's what I'm trying to do:
这是我想要做的：
When there's a new INSERTinto the table ACCOUNTS, I need to update the row in ACCOUNTSwhere pk= NEW.edit_onby setting status='E'to denote that the particular (old) account has been edited.
当INSERT表中有新内容时ACCOUNTS，我需要通过设置来更新ACCOUNTSwhere pk= 中的行以表示特定（旧）帐户已被编辑。NEW.edit_onstatus='E'
DELIMITER $$
DROP TRIGGER IF EXISTS `setEditStatus`$$
CREATE TRIGGER `setEditStatus` AFTER INSERT on ACCOUNTS
FOR EACH ROW BEGIN
update ACCOUNTS set status='E' where ACCOUNTS.pk = NEW.edit_on ;
END$$
DELIMITER ;
The requirement is NOTthat I manipulate the newly insertedcolumn, but an already existingcolumn with pk = NEW.edit_on
要求不是我操作新插入的列，而是已经存在的列pk = NEW.edit_on
However, I can't update the same table: Can't update table ACCOUNTS ... already used by the statement that invoked this trigger
但是，我无法更新同一张表： Can't update table ACCOUNTS ... already used by the statement that invoked this trigger
Please suggest a workaround
请提出解决方法
PS: I have already gone through Updating table in trigger after update on the same table, Insert into same table trigger mysql, Update with after insert trigger on same tableand mysql trigger with insert and update after insert on tablebut they dont seem to answer my question.
PS：我已经走过了更新表触发器在同一个表更新后，插入到相同的表触发的mysql，更新与后同桌INSERT触发器和表后插入MySQL的触发器与插入和更新，但他们似乎没有给答案我的问题。
Edit
编辑
ACCOUNTSTable:
ACCOUNTS桌子：
CREATE TABLE `ACCOUNTS` (
`pk` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(9) unsigned NOT NULL,
`edit_on` bigint(10) unsigned DEFAULT NULL,
`status` varchar(1) NOT NULL DEFAULT 'A',
PRIMARY KEY (`pk`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2147483726 DEFAULT CHARSET=latin1
回答by pjama
It seems that you can't do all this in a trigger. According to the documentation:
似乎您无法在触发器中完成所有这些。根据文档：
Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
在存储的函数或触发器中，不允许修改已被调用函数或触发器的语句使用（用于读取或写入）的表。
According to this answer, it seems that you should:
根据这个答案，您似乎应该：
create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.
创建一个存储过程，插入/更新目标表，然后更新其他行，所有这些都在一个事务中。
With a stored proc you'll manually commit the changes (insert and update). I haven't done this in MySQL, but this postlooks like a good example.
使用存储过程，您将手动提交更改（插入和更新）。我还没有在 MySQL 中这样做过，但这篇文章看起来是一个很好的例子。
回答by Rangel
This is how I update a row in the same table on insert
这就是我如何在插入时更新同一个表中的一行
activationCodeand emailare rows in the table USER.
On insert I don't specify a value for activationCode, it will be created on the fly by MySQL.
activationCode并且email是表中的行USER。在插入时，我没有为 指定值activationCode，它将由 MySQL 动态创建。
Change usernamewith your MySQL username and db_namewith your db name.
更改username为您的 MySQL 用户名和db_name您的数据库名称。
CREATE DEFINER=`username`@`localhost`
TRIGGER `db_name`.`user_BEFORE_INSERT`
BEFORE INSERT ON `user`
FOR EACH ROW
BEGIN
SET new.activationCode = MD5(new.email);
END
回答by JCLG
Had the same problem but had to update a column with the id that was about to enter, so you can make an update should be done BEFORE and AFTER not BEFORE had no id so I did this trick
有同样的问题，但必须用即将进入的 id 更新一列，所以你可以做一个更新，应该在 BEFORE 和 AFTER not BEFORE 之前没有 id 所以我做了这个技巧
DELIMITER $$
DROP TRIGGER IF EXISTS `codigo_video`$$
CREATE TRIGGER `codigo_video` BEFORE INSERT ON `videos`
FOR EACH ROW BEGIN
DECLARE ultimo_id, proximo_id INT(11);
SELECT id INTO ultimo_id FROM videos ORDER BY id DESC LIMIT 1;
SET proximo_id = ultimo_id+1;
SET NEW.cassette = CONCAT(NEW.cassette, LPAD(proximo_id, 5, '0'));
END$$
DELIMITER ;
回答by Raymond B
On the last entry; this is another trick:
在最后一个条目上；这是另一个技巧：
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = ... and table_name = ...
回答by Chitraranjan Ngangbamcha
Instead you can use before insert and get max pkid for the particular table and then update the maximium pkid table record.
相反，您可以在插入之前使用并获取特定表的最大 pkid，然后更新最大 pkid 表记录。
回答by KenZo
DELIMITER $$
DROP TRIGGER IF EXISTS `setEditStatus`$$
CREATE TRIGGER `setEditStatus` **BEFORE** INSERT on ACCOUNTS
FOR EACH ROW BEGIN
SET NEW.STATUS = 'E';
END$$
DELIMITER ;
