MySQL - 插入后触发更新同一个表的触发器

Here's what I'm trying to do:

这是我正在尝试做的事情：

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.

当表ACCOUNTS中有一个新的INSERT时，我需要通过设置status ='E'更新ACCOUNTS中的行，其中pk = NEW.edit_on表示特定（旧）帐户已被编辑。

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 NOT that I manipulate the newly inserted column, but an already existing column 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

但是，我无法更新同一个表：无法更新调用此触发器的语句已使用的表ACCOUNTS ...

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 table and mysql trigger with insert and update after insert on table but they dont seem to answer my question.

PS：我已经通过在同一个表上更新后触发器中的更新表，插入到同一个表触发器mysql，在同一个表上插入触发器后更新和在插入表后插入和更新的mysql触发器但是他们似乎没有回答我的问题。

Edit

编辑

ACCOUNTS Table:

帐户表：

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

5 个解决方案

#1

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 post looks like a good example.

使用存储过程，您将手动提交更改（插入和更新）。我没有在MySQL中这样做过，但这篇文章看起来就是一个很好的例子。

#2

This is how i update a row in the same table on insert

这是我如何在插入时更新同一个表中的行

activationCode and email are 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 'username' with your mysql username and 'db_name' with your db name.

使用您的mysql用户名更改'username'，使用您的db名称更改'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

#3

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即将进入的列，所以你可以做一个更新应该在之前和之前没有没有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 ;

#4

On the last entry; this is another trick:

在最后一个条目;这是另一个伎俩：

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = ... and table_name = ...

#5

Instead you can use before insert and get max pkid for the particular table and then update the maximium pkid table record.

相反，您可以在插入之前使用并获取特定表的max pkid，然后更新maximium pkid表记录。

#1