Oracle SQL, trigger inserting row into table X as FK for currently inserted row in table Y -

so in oracle 11g have following:

create table object(     id number(8) not null,     category_enum_id number(8) not null );  create sequence object_seq  start     1  increment   1  nocache  nocycle; / create table tree(     id number(8) not null,     object_id number(8) not null,     name nvarchar2(128) );  create table category_enum(     id number(8) not null,     name nvarchar2(64) );  -- pk's alter table tree add constraint tree_pk primary key (id);  alter table object add constraint object_pk primary key (id);  alter table category_enum add constraint category_enum_pk primary key (id)  --- fk's alter table tree add constraint tree_object_fk foreign key (object_id) references object (id);  alter table object  add constraint object_category_fk foreign key (category_enum_id) references category_enum (id);  -- closed dictionary sample data insert category_enum (id, name) values (1, 'tree'); insert category_enum (id, name) values (2, 'herb'); insert category_enum (id, name) values (3, 'shroom');  -- triggers create or replace trigger tree_before_insert     before insert        on tree   referencing new new old old   each row   declare    category_id number;     begin       select id category_id category_enum name = 'tree' , rownum <= 1;           if :new.object_id null              :new.object_id := object_seq.nextval;              insert object (id, category_enum_id) values (:new.object_id, category_id);           end if;      end; / 

if next run:

insert tree (id, name) values (1, 'tree1'); insert tree (id, name) values (2, 'tree2'); 

i error:

... trigger tree_before_insert compiled error starting @ line 91 in command: insert tree (id, name) values (1, 'tree1') error report: sql error: ora-02291: integrity constraint (hr.tree_object_fk) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *cause:    foreign key value has no matching primary key value. *action:   delete foreign key or add matching primary key. 1 rows inserted. 

but if before above, insert features table (like below), works fine.

insert object (id, category_enum_id) values (0, 1);  insert tree (id, name) values (1, 'tree1'); insert tree (id, name) values (2, 'tree2'); 

so problem occurs first insert, others work fine, ids assigned object_seq.nextval proper. doing wrong ?

jah bless ya help.

edit have removed unnecessary code, it's more clear , shorter.

the problem here have primary key constraint placed on "tree" table , foreign key constraint placed on "object" table. new record try inserting table foreign key constraint, make sure available in table has primary key mapping.

example shown below:  **tables:** create table tab1 (id number, name varchar2(100) )  create table tab2 (id number, name varchar2(100) )  **constraints added:** alter table tab1 add constraint pk_tab1_id primary key (id) alter table tab2 add constraint fk_tab2_id foreign key(id) references tab1(id)  **insert statement on second table:** insert tab2(values(1,'abc')  **error:** sql error: ora-02291: integrity constraint (prave.fk_tab2_id) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *cause:    foreign key value has no matching primary key value. *action:   delete foreign key or add matching primary key.  **solution:** **insert table pk first below:** insert tab1 values(1,'abc') 1 rows inserted. insert tab2 values(1,'abc') 1 rows inserted.