Re: how to get value of auto-increment PK of row just inserted?

This is just the way it should work.

Hth,

Sybrand Bakker, Oracle DBA

Otis Gospodnetic <otis_at_my-deja.com> wrote in message news:87q6d5$831$1_at_nnrp1.deja.com...
> Hi,
>
> In article <949686432.18196.0.pluto.d4ee154e_at_news.demon.nl>,
> "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> > If you used a sequence to generate the pk you can use select currval
> from
> > dual to return the value of the PK.
> > Sybrand Bakker, Oracle DBA
>
> Thanks, I think this helps.
> However, any idea why this is happening:
>
> 1. why can't I do "select user_id_seq.currval from dual" before
> doing "select user_id_seq.nextval from dual" ?
>
> 2. why does the sequence increase when I do simple selects such
> as "select user_id_seq.nextval from dual" ? Is this normal?
>
> For example:
>
> SQL> select user_id_seq.currval from dual;
> select user_id_seq.currval from dual
> *
> ERROR at line 1:
> ORA-08002: sequence USER_ID_SEQ.CURRVAL is not yet defined in this
> session
>
> SQL> select user_id_seq.nextval from dual;
>
> NEXTVAL
> ----------
> 429
>
> SQL> select user_id_seq.nextval from dual;
>
> NEXTVAL
> ----------
> 430
>
> Thanks,
>
> Otis
>
>
> > Otis Gospodnetic <otis_at_my-deja.com> wrote in message
> > news:87ev4a$cn7$1_at_nnrp1.deja.com...
> > > Hi,
> > >
> > > This must be a basic question. I have 2 tables (examples below)
> where
> > > the second one has a FOREIGN KEY that references a PRIMARY KEY of
> the
> > > first table.
> > >
> > > This PRIMARY KEY (user_id column, see below) has a trigger set on
> it to
> > > auto-increment user_id by +1 on insert.
> > >
> > > My question is:
> > > When I want to insert a new row in the second table, how do I know
> what
> > > user_id to use? In other words, how do I get the user_id of the row
> I
> > > just inserted in the first table?
> > >
> > > For example:
> > >
> > > INSERT INTO user_auth (username,password) VALUES ('u', 'p');
> > >
> > > <Now how do I get user_auth.user_id of the row that was just
> inserted?>
> > > <I need it for the second insert, below>
> > >
> > > INSERT INTO user_info (user_id,name) VALUES (????,'Mali Medo');
> > >
> > > Thanks,
> > >
> > > Otis
> > > P.S.
> > > Example tables:
> > >
> > > create table user_auth
> > > (
> > > user_id int
> > > CONSTRAINT pk_user_id PRIMARY KEY,
> > > username varchar(32) NOT NULL,
> > > password varchar(16) NOT NULL,
> > > hint varchar(128)
> > > );
> > >
> > > create table nm_user_info
> > > (
> > > user_id int,
> > > FOREIGN KEY (user_id) REFERENCES
> > > user_auth(user_id) ON DELETE CASCADE,
> > > name varchar(128)
> > > );
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Feb 08 2000 - 17:06:03 CST