Re: Auto-incrementing primary key?

S Metro wrote:
>
> 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
> >

Sure the user can get CURRVAL but there's no guarantee that someone else hasn't made another hit on the sequence in between. If you need to know what the key value is for further access to that same record then select and save myseq.nextval and use the saved value in the insert and subsequent queries for that row.
Ed Received on Thu Jan 15 1998 - 00:00:00 CST