Triggers and sequences

I am developing a system which will in essence perform the folowing task.

Insert rows into a master table and insert rows into a child table.

The master table rows will have a primary key based upon a sequence. The primary key column will be filled in using a TRIGGER.

i.e.:

create OR REPLACE trigger master_table_record_insert before insert
on master_table for each row
begin
  select master_sequence.nextval into :new.code from sys.dual; end;

This is the master table.
There will be a child table which has a dual column primary key. Column one is the primary key value from the corresponding row in the master table.
Column two is based upon another sequence.

They are updated in the same transaction.

therefore I am able to do ...

create OR REPLACE trigger child_table_record_insert before insert
on child_table for each row
begin
  select master_sequence.currval into :new.master_code from sys.dual;   select child_sequence.nextval into :new.child_code from sys.dual; end;

My two concerns are..

1. Will the value of currval be for MY session only or will it be affected by other user's transactions?
   (I have tried running two SQL sessions, selecting from a sequence in one
   and then selecting currval, selecting nextval in the other session and checking currval in the original and it seems to work fine).
2. How do I go about the child table insert trigger when I am only adding rows to it and not to the master (basically using currval is not an option here)?
   (my first thought would be that I would have to read in the primary key from
   the master table, assign that value to the appropriate column in the child in my application then the trigger would only use CURRVAL if the column was null)

Any help will be greatly appreciated. Received on Thu Aug 19 1999 - 04:58:42 CDT