Re: Triggers and sequences

Michael's response if completely correct. I'll add that you can't SELECT currval unless you've previously SELECTed nextval from the same session.

Regards,

--
David C. Sisk
Need tech info on Oracle? Visit The Unofficial ORACLE on NT site at http://www.ipass.net/~davesisk/oont.htm Like original music? Listen to song samples and buy a CD at http://www.mp3.com/disparityofcult

js wrote in message <935056818.28744.0.nnrp-07.c1ed6246_at_news.demon.co.uk>...
>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 - 22:15:15 CDT