Re: id of last inserted row?

Mark Harrison wrote:
> I've got a table with an autoincremented identifier, as
> specified below (some cols truncated for brevity).
>
> I do something like
> insert into bar(name) values('mark');
>
> and I would like to get back the id that
> was just inserted for me.
>
> How can I do that?
>
> Many TIA!
> Mark
>
>
>
> REM FOO BAR
> CREATE TABLE "FOO"."BAR"
> ( "ID" NUMBER(20,0) NOT NULL ENABLE,
> "NAME" VARCHAR2(64 BYTE) NOT NULL ENABLE,
> CONSTRAINT "BAR_PK" PRIMARY KEY ("ID") ENABLE
> ) ;
> CREATE OR REPLACE TRIGGER "FOO"."INS_BAR" BEFORE INSERT ON BAR
> FOR EACH ROW
> BEGIN SELECT BAR_SEQ.nextval INTO :new.ID FROM dual; END;
> /
> ALTER TRIGGER "FOO"."INS_BAR" ENABLE;
> CREATE UNIQUE INDEX "FOO"."BAR_PK" ON "FOO"."BAR" ("ID")
> ;
>
> --
> Mark Harrison
> Pixar Animation Studios

While Michael has given you an answer to your immediate question, I would challenge you to look closer to your data model. Why are you using a pseudokey to begin with?
You example seems to have a fine natural key "NAME". (well it would be if it included a UNIQUE constraint).

HTH,
   Ed Received on Tue Aug 01 2006 - 14:32:19 CDT