oracle - Update inserted row with trigger -

i inserting table:

fruit:  fruit_id   |   name -----------|---------- 1          |  apple ---------------------- 

and have table :

basket:  basket_id   |   fruit_id   |   name ------------|--------------|----------- 345         |   1          |   apple 789         |   2          |   grape 

when insert:

insert fruit (fruit_id) values (2) 

i want update "name" column based on name basket table.

i trying use trigger this:

create trigger add_fruit_name after insert     on fruit     each row declare begin     update fruit     set (name) =       (select name basket     :new.fruit_id = basket.fruit_id);     commit; end; / 

my error when try insert using trigger this:

table fruit mutating, trigger/function may not see 

any ideas?

mutating trigger means data changes on trigger firing, in case, you're updating (dml operation) same table, on insert (dml operation) same one, means trigger won't see possibly , error + has commit in it, without being in autonomous_transation (read more on pragma understand it). now, general rule, triggers should not commit, unless they're in autonomous transaction, done declaring above mentioned pragma (only in extreme cases). write trigger so:

create trigger add_fruit_name before insert     on fruit     each row declare begin     select b.name       :new.name       basket b      b.fruit_id = :new.fruit_id;    exception     when no_data_found       raise_application_error (-20001,'no fruit found in table basket fruit_id: ' || to_char(:new.fruit_id));     when others       raise_application_error (-20002, 'trigger add_fruit_name raised error' || sqlerrm);  end; / 

another approach edit function/procedure inserts rows fruit table , make update appropriately.

a small, important article understand - check out!

cheers