MySQL - 插入后更新同一张表的触发器
这是我想要做的:
当有一个新的 INSERT 到表 ACCOUNTS 中时，我需要更新 ACCOUNTS 中的行 pk = NEW.edit_on 通过设置 status='E' 来表示特定(旧)帐户已被编辑.
When there's a new INSERT into the table ACCOUNTS, I need to update the row in ACCOUNTS where pk = NEW.edit_on by setting status='E' to denote that the particular (old) account has been edited.
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 ;
要求不我操作 新插入的列，而是 已经存在列 pk = NEW.edit_on
The requirement is NOT that I manipulate the newly inserted column, but an already existing column with pk = NEW.edit_on
但是，我无法更新同一张表:无法更新表 ACCOUNTS ... 已被调用此触发器的语句使用
However, I can't update the same table: Can't update table ACCOUNTS ... already used by the statement that invoked this trigger
请提出解决方法
PS:我已经完成了 更新表在同一张表更新后触发，插入同一张表触发mysql, 在同一张表上使用插入后触发器更新和 mysql 在表上插入和更新后触发 但他们似乎没有回答我的问题.
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 table and mysql trigger with insert and update after insert on table but they dont seem to answer my question.
编辑
账户表:
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
推荐答案
看来你不能在一个触发器中完成这一切.根据文档:
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.
根据 这个答案，看来你应该:
创建一个存储过程，插入/更新目标表，然后更新其他行，所有这些都在一个事务中.
create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.
使用存储过程，您将手动提交更改(插入和更新).我没有在 MySQL 中这样做，但 这篇文章 看起来不错例子.
With a stored proc you'll manually commit the changes (insert and update). I haven't done this in MySQL, but this post looks like a good example.
相关文章