title: MySQL - Trigger for updating same table after insert
body: <p>Here's what I'm trying to do:</p>
  
  <p>When there's a new <code>INSERT</code> into the table <code>ACCOUNTS</code>, I need to update the row in <code>ACCOUNTS</code> where <code>pk</code> = <code>NEW.edit_on</code> by setting <code>status='E'</code> to denote that the particular (old) account has been edited.</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>The requirement is <strong>NOT</strong> that I manipulate the <em>newly inserted</em> column, but an <em>already existing</em> column with <code>pk = NEW.edit_on</code></p>
  
  <p>However, I can't update the same table: <code>Can't update table ACCOUNTS ... already used by the statement that invoked this trigger</code></p>
  
  <p>Please suggest a workaround</p>
  
  <p>PS: I have already gone through <a href="https://stackoverflow.com/questions/4547465/updating-table-in-trigger-after-update-on-the-same-table">Updating table in trigger after update on the same table</a>, <a href="https://stackoverflow.com/questions/8504904/insert-into-same-table-trigger-mysql">Insert into same table trigger mysql</a>, <a href="https://stackoverflow.com/questions/6925651/update-with-after-insert-trigger-on-same-table">Update with after insert trigger on same table</a> and <a href="https://stackoverflow.com/questions/12870465/mysql-trigger-with-insert-and-update-after-insert-on-table">mysql trigger with insert and update after insert on table</a> but they dont seem to answer my question.</p>
  
  <p><strong>Edit</strong></p>
  
  <p><code>ACCOUNTS</code> Table:</p>
  
  <pre><code>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
  </code></pre>