title: what is the real cause of mysql error 1442?
body: <p>well i have looked for a lot of places on the internet for the cause of the <code>mysql error #1442</code> which says </p>
  
  <blockquote>
    <p>Can't update table 'unlucky_table' in stored function/trigger because
    it is already used by statement which invoked this stored
    function/trigger</p>
  </blockquote>
  
  <p>some say that this is a bug in mysql or a feature that it doesnt provide.</p>
  
  <p><a href="https://stackoverflow.com/questions/1582683/mysql-trigger-stored-trigger-is-already-used-by-statement-which-invoked-stored-tr/2314264#2314264">MySQL triggers can't manipulate the table they are assigned to. All other major DBMS support this feature so hopefully MySQL will add this support soon.</a></p>
  
  <p>Some claim that this is due to recursive behavior 
  <a href="http://forums.mysql.com/read.php?99,122354,122505#msg-122505" rel="nofollow noreferrer">when you insert a record mysql is doing some lock stuff. you can't insert/update/delete rows of the same table where you insert.. because then the trigger would called again and again.. ending up in a recursion</a></p>
  
  <p><a href="http://forums.mysql.com/read.php?99,122354,201300#msg-201300" rel="nofollow noreferrer">During the insert/update you have access to the NEW object which contains all of the fields in the table involved. If you do a before insert/update and edit the field(s) that you want to change in the new object it will become a part of the calling statement and not be executed as a separately (eliminating the recursion) </a></p>
  
  <p>now i cant understand why this is recursive. i have a case in which i have 2 tables <code>table1</code> and <code>table2</code> and i run an sql query as</p>
  
  <pre><code>update table1 set avail = 0 where id in (select id from table2 where duration &lt; now() - interval 2 hour);
  </code></pre>
  
  <p>now i have an <code>after update trigger</code> on <code>table1</code> as</p>
  
  <pre><code>CREATE TRIGGER trig_table1 AFTER UPDATE ON table1
  FOR EACH ROW begin
  if old.avail=1 and new.avail=0 then
  delete from table2 where id=new.id;
  end if;
  </code></pre>
  
  <p>now when i execute the update query i get a 1442 error.
  whats recursive in this case?</p>
  
  <pre><code>is this error a lack of feature in mysql?
  OR
  does this have to do with how mysql executes queries?
  OR
  is there something logically wrong with executing such queries?
  </code></pre>