MySQL - Trigger to update the same table after insert operation

Question

This is what I'm trying to do:

When there is a new INSERT into table ACCOUNTS, I need to update ACCOUNTS by setting status='E'phpcnend line where pk = NEW.edit_on cphpcn to indicate that a specific (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 ;

Requirement is not my operation newly inserted column, but already existing column, where pk = NEW.edit_on< ; /p>

However, I cannot update the same table: Cannot update table ACCOUNTS ... has been used by the statement that called this trigger

Please suggest a solution

PS: I have done update table in trigger after update on same table, insert into same table trigger mysql, update on same table using trigger after insert and insert and update mysql trigger after insert on table but they don't seem to answer my question.

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

Answer 1

This is how I update rows in the same table on insert

activationCode and email are rows in table USER. When inserting, I did not specify a value for activationCode, which will be dynamically created by MySQL.

Change username to your MySQL username and db_name to your database 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

Answer 2

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

Based on this answer, it seems you should:

With stored procedures, you will commit changes (inserts and updates) manually. I haven't done this in MySQL, but this article looks like a good example.