Re: Triggers and sequences

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 01 Mar 1999 23:02:32 GMT
Message-ID: <36dc1a7a.23717714@inet16.us.oracle.com>

On Mon, 1 Mar 1999 21:48:43 -0000, "Peter Almond" <almond_at_rapid.co.uk> wrote:

>I'm considering using a sequence to generate primary keys in a database, and
>generating them in a "BEFORE INSERT" trigger. I have tried this out and it
>works.
>
>It seems like a good idea, because it means the logic is in one place, and
>the client application generating rows doesn't have to worry about how to
>get the next sequence number.
>
>BUT !
>
>How does the client program GET the sequence number after adding a row ?. If
>child records are being added at the same time, I have to know the primary
>key of the parent so I can set the foreign key values.
>
>I am using various methods of accessing the db, including Pro*C , OO4O (VB)
>and PL/SQL.
>
>I'm looking for a good general approach to this. What do others do ?

  1 declare
  2 l_id number;
  3 begin
  4 -- either use the returning clause   5 insert into a ( bw )
  6 values ( 'chris' )
  7 returning id into l_id;
  8 dbms_output.put_line( 'The returning clause got ' || l_id );   9 -- or do this
 10 select my_seq.currval

 11      into l_id
 12      from dual;

 13 dbms_output.put_line( 'The select of the currval got ' || l_id );  14* end;
SQL> /
The returning clause got 6
The select of the currval got 6

PL/SQL procedure successfully completed.

SQL> select * from a;

        ID BW
---------- ----------

         6 chris

I believe that the returning clause is an Oracle 8 feature, the select of the currval will work in older versions.

hope this helps.

chris.

>
>Any help much appreciated,
>
>Pete
>
>
>

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Mar 01 1999 - 17:02:32 CST