問題描述

這是我想要做的:

當(dāng)表 ACCOUNTS 中有一個新的 INSERT 時(shí)，我需要更新 ACCOUNTS 中的行，其中 pk = NEW.edit_on 通過設(shè)置 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 ;

要求不是我操作新插入的列，而是已經(jīng)存在的列，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 ... 已被調(diào)用此觸發(fā)器的語句使用

However, I can't update the same table: Can't update table ACCOUNTS ... already used by the statement that invoked this trigger

請?zhí)岢鼋鉀Q方法

PS:我已經(jīng)完成了更新表格在同一個表上更新后的觸發(fā)器，插入同一個表觸發(fā)器mysql, 在同一個表上插入后觸發(fā)器更新和 mysql 觸發(fā)器在表上插入后插入和更新 但他們似乎沒有回答我的問題.

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

推薦答案

似乎無法在觸發(fā)器中完成所有這些操作.根據(jù)文檔:

It seems that you can't do all this in a trigger. According to the documentation:

在存儲函數(shù)或觸發(fā)器中，不允許修改已被調(diào)用函數(shù)或觸發(fā)器的語句使用(讀取或?qū)懭?的表.

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.

根據(jù) 這個答案，看來你應(yīng)該:

創(chuàng)建一個存儲過程，插入/更新目標(biāo)表，然后更新其他行，所有這些都在一個事務(wù)中.

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.

這篇關(guān)于MySQL - 插入后更新同一個表的觸發(fā)器的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！