Oracle AFTER INSERT trigger with .NEXTVAL

[adknbvi]

Hello,

I have a table (TABLE_A) with an ID field that we use seq.nextval to populate on INSERT.
TABLE_A:
ID
<other stuff>
USER_NAME

And I have a second table (TABLE_B) which may contains multiple records for each ID:

TABLE_B:
ID
USER_NAME
When we insert into TABLE_A, we provide all the <other stuff> and the USER_NAME in the insert statement.

Is it possible to build an AFTER INSERT trigger to insert a row into TABLE_B using the ID and USER_NAME fields from TABLE_A? I haven't been able to find any examples where an AFTER INSERT trigger is used when the ID is not known until after the record is inserted into TABLE_A.

Thanks in advance for any insight!
Valerie

 

[Dagon]

It seems to be feasible, even with foreign key constraints.

SQL> create sequence tab1_seq
Sequence created.
SQL> create sequence tab2_seq
Sequence created.
SQL> drop table table_b
Table dropped.
SQL> drop table table_a
Table dropped.
SQL> create table table_a (id number primary key, other_stuff varchar2(500), username varchar2(20))
Table created.
SQL> create table table_b (id number primary key, taba_id number constraint foreign references table_a(id), other_detail number)
Table created.
SQL> create or replace trigger tabtrig_a
after insert on table_a
for each row
begin
  insert into table_b values (tab2_seq.nextval, tab1_seq.currval, 1);
  insert into table_b values (tab2_seq.nextval, tab1_seq.currval, 2);
  insert into table_b values (tab2_seq.nextval, tab1_seq.currval, 3);
end;
Trigger created.
SQL> select * from user_errors wherE name = 'TABTRIG_A'
no rows selected.
SQL> INSERT into table_a select tab1_seq.nextval, 'junk '||id, user
from 
(select 1 id from dual union all select 2 id from dual)
2 rows created.
SQL> select * from table_b

        ID    TABA_ID OTHER_DETAIL
---------- ---------- ------------
         1          1            1
         2          1            2
         3          1            3
         4          2            1
         5          2            2
         6          2            3

6 rows selected.

For Oracle-related work, contact me through Linked-In.