Re: Auto-incrementing primary key?

I also have the same dillemma. That solution sounds great, but what if after inserting the row, you immediately need the value of the key. Since the trigger used the NEXTVAL, can a user get the CURRVAL to find out what was inserted?

Scott Metro
(Take out nospam when replying via email)

mark tomlinson wrote in message
<34be244e.169300050_at_newshost.us.oracle.com>...
>On Wed, 14 Jan 1998 13:33:57 -0500, Jerry Cunningham
><gcunning_at_census.gov> wrote:
>
>You could do the following:
>
>Lets create a table foo:
>
>create table foo (SEQ number, DATA varchar2);
>
>and now a sequence:
>
>create sequence foo_idx;
>
>and now a trigger for the table:
>
>create or replace trigger foo_seq_ins
>before insert on foo
>for each row
>begin
>select foo_idx.nextval into :new.seq from dual;
>end;
>
>now a SQL statement such as:
>INSERT INTO FOO (DATA) VALUES ('TEST');
>
>will automatically generate the SEQ column for you...
>
>-mark
>
>>A colleague of mine is developing an application that will be accessing
>>both an Oracle and a Sybase SQLAnywhere database. The SQLAnywhere
>>database has what it calls an "Identifier Field" which is an
>>automatically incremented primary key. Does Oracle have anything like
>>this?
>>
>>With Oracle's sequences, you have to explicitly insert the NEXTVAL from
>>the sequence, correct? Is there any way that Oracle can automatically do
>>
>>this? Another DBA told us that she might have heard of something called
>>an "auto-increment constraint" (or something like that).
>>
>>As always, thanks for your help.
>>
>>
>>- Jerry
>
Received on Thu Jan 15 1998 - 00:00:00 CST