MySQL-插入后触发更新同一表 [英] MySQL - Trigger for updating same table after insert

问题描述

这就是我想要做的:

当表ACCOUNTS中有新的INSERT时，我需要通过设置status='E'来表示ACCOUNTS其中的pk = NEW.edit_on中的行，以表示特定(旧)帐户具有已被编辑.

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

但是，我无法更新同一张表:Can't update table ACCOUNTS ... already used by the statement that invoked this trigger

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.

修改

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

推荐答案

似乎您无法在触发器中完成所有这些操作.根据文档:

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.

使用存储的proc，您将手动提交更改(插入和更新).我尚未在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.

这篇关于MySQL-插入后触发更新同一表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！