triggers - what is the real cause of mysql error 1442? -

well have looked lot of places on internet cause of mysql error #1442 says

can't update table 'unlucky_table' in stored function/trigger because used statement invoked stored function/trigger

some bug in mysql or feature doesnt provide.

mysql triggers can't manipulate table assigned to. other major dbms support feature mysql add support soon.

some claim due recursive behavior when insert record mysql doing lock stuff. can't insert/update/delete rows of same table insert.. because trigger called again , again.. ending in recursion

during insert/update have access new object contains of fields in table involved. if before insert/update , edit field(s) want change in new object become part of calling statement , not executed separately (eliminating recursion)

now cant understand why recursive. have case in have 2 tables table1 , table2 , run sql query as

update table1 set avail = 0 id in (select id table2 duration < now() - interval 2 hour); 

now have after update trigger on table1 as

create trigger trig_table1 after update on table1 each row begin if old.avail=1 , new.avail=0 delete table2 id=new.id; end if; 

now when execute update query 1442 error. whats recursive in case?

is error lack of feature in mysql? or have how mysql executes queries? or there logically wrong executing such queries? 

you cannot refer table when updating it.

/* sql not support */ update tablename 1 = (select 1 tablename) 

from mysql docs:

a trigger can access both old , new data in own table. trigger can affect other tables, not permitted modify table being used (for reading or writing) statement invoked function or trigger. (before mysql 5.0.10, trigger cannot modify other tables.)