title: MySql - can AFTER INSERT trigger delete the row which invoked it
body: <p>Let me put it in simplest words possible - <strong>is it possible to delete the row, which actually set On the trigger</strong> i.e. I have an <code>AFTER INSERT ON &lt;table2&gt;</code> trigger, the SQL in the trigger <code>INSERT</code> / <code>UPDATE</code> another <code>&lt;table1&gt;</code> (based on a <code>WHERE</code>), and finally tends to delete the entry/row in the  (the row which basically fired the trigger).</p>
  
  <p>Trigger SQL:</p>
  
  <pre><code>DELIMITER ||
  DROP TRIGGER IF EXISTS trg_adddata_tmp ||
  CREATE TRIGGER trg_adddata_tmp
  AFTER INSERT ON adddata_tmp
  FOR EACH ROW 
      BEGIN
          IF EXISTS (SELECT * FROM adddata WHERE data_id = new.data_id AND account_name = new.account_name) THEN
              UPDATE adddata SET data_id = new.data_id, account_name = new.account_name, user_name = new.user_name, first_name = new.first_name, last_name = new.last_name WHERE data_id = new.data_id AND account_name = new.account_name;
          ELSE
              INSERT INTO adddata (data_id, account_name, user_name, first_name, last_name)
              VALUES(new.data_id, new.account_name, new.user_name, new.first_name, new.last_name);
          END IF;
          DELETE FROM adddata_tmp WHERE id = new.id;
      END;
  ||  
  </code></pre>
  
  <p>Without the <code>DELETE</code> (just above the <code>END;</code>) the trigger works fine - <code>UPDATE</code> if exist otherwise <code>INSERT</code> - with <code>DELETE</code> statement gives the following error:</p>
  
  <pre><code>Error Code: 1442
  Can't update table 'adddata_tmp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
  </code></pre>
  
  <hr>
  
  <p>By the way, the error is pretty self-explanatory, but still wanted to make sure if this is possible - if not this way, may be some other way around i.e. I want the <code>adddata_tmp</code> table to be empty (or clean-ed up) all the time (on <code>INSERT</code> copies the data to main <code>adddata</code> table)</p>
  
  <p>One idea, I have in mind is, to use an <code>EVENT</code> to clean-up the <code>adddata_tmp</code> based on some <code>status</code> field - which gets set as the last statement in the trigger (in place of <code>DELETE</code>).</p>