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

From: Otis Gospodnetic <otis_at_my-deja.com>
Date: Wed, 09 Feb 2000 16:13:29 GMT
Message-ID: <87s3mv$ho2$1@nnrp1.deja.com>

In article <950051301.16055.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> This is just the way it should work.

Thanks!
But this can easily make the sequence non-continuous, right? (e.g. if I do a few selects like I did below, the sequence goes up a little, and when the next (real) INSERT comes the PK will not be just +1 from the last inserted row but +N where N>1).

But OK :)

I'm still struggling with this issue a litte, though :( I need to make 2 INSERTs using COMMIT, like this:

• this row gets a PK from sequence INSERT INTO user_auth (username,password) VALUES ('u', 'p');
• this row has a FK that references the above PK INSERT INTO user_info (user_id,name) VALUES (????,'Name Here');

COMMIT; I'm not sure how to get the value from the PK of row inserted by first INSERT into a variable (or PL/SQL equivalent) so that I can use it in the second INSERT.
Also, since I want to use COMMIT (I don't want the first row to be inserted if the second INSERT fails) I'm not sure if it is even possible to get the PK from the first INSERT, is it?

Should/could I use a trigger on the user_info table (second INSERT), like this manybe:

create or replace trigger user_info_trig   before insert
  on user_info
  begin
    select user_id_seq.nextval
    into :new.user_id
    from dual;
end;

Or maybe something like this:

DEFINE CanIHaveAVariableHere int;

BEGIN
 INSERT INTO user_auth (username,password) VALUES ('u', 'p');  SELECT user_id_seq.nextval

    INTO CanIHaveAVariableHere
    FROM dual;
 INSERT INTO user_info (user_id,name) VALUES (CanIHaveAVariableHere,'Name Here');

• do I need COMMIT if I use BEGIN/END block?
• how do I ROLLBACK if one of the INSERTs fails? END;

Many thanks!

Otis
P.S.
Which Oracle book should I get for this types of questions? I bought Oracle8i DBA Handbook from Oracle Press, but that is not what I need, obviously :)

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

>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Feb 09 2000 - 10:13:29 CST