Re: Triggers and sequences

js a écrit dans le 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).
>

currval is relevant for your session.
Several sessions have different currval for the same sequence.

>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)
>

You're right, you can modify your trigger like that: if :new.master_code is null then

   select master_sequence.currval into :new.master_code from sys.dual; end if;

Else I don't know how you can insert a new child if you previously don't know its parent.

Regards. Received on Thu Aug 19 1999 - 07:48:46 CDT