sql - Mysql trigger issue (i think it's firing) -

i have issue trigger on mysql database. have table such follows:

id int not null auto_increment (pk) parent_id int not null, rank int not null 

what i'm trying use trigger update rank next highest +10 when have same parent_id, doesn't seem working.

delimiter $$ drop trigger if exists after_insert $$ create trigger after_insert  after insert on mytable each row begin    if exists (select rank mytable parent_id = new.parent_id , id != new.id order rank desc limit 1)   update mytable set rank  = 10   id = new.id;   else   update mytable set rank = 20   id = new.id;   end if;     end $$ 

i've tried setting new rank variable , calling update statement using that, , again didn't work. created table log values being selected , worked can't quite understand what's going on. case of, although trigger "after insert" insert hasn't happened can't update row it's inserted? reason ask is, i've tried updating rank different values e.g 1 , 2 depending on statement goes to, ends being 0.

i think you're on right track thought:

is case of, although trigger "after insert" insert hasn't happened can't update row it's inserted?

from faq:

b.5.9: can triggers access tables?

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.

the documentation isn't clear you're doing won't work. otoh, documentation isn't clear you're trying work either.

i think you'd better off using before insert trigger , setting new.rank in there. then, new row have right rank value when inserted table rather patching after. also, you'd able simplify existence check this:

exists(select rank mytable parent_id = new.parent_id) 

as new.id wouldn't have useful value , new row wouldn't in table anyway; order by , limit unnecessary you're checking if exists took them out.

a before insert trigger seems match intent better anyway , give correct data inserted table.