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

问题描述

这是我想要做的:

当表 ACCOUNTS 中有一个新的 INSERT 时，我需要更新 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.

编辑

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.

使用存储过程，您将手动提交更改(插入和更新).我还没有在 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屋！