| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Insert and get sys PK?
A copy of this was sent to KTL <KTL_at_retailsolutions.com>
(if that email address didn't require changing)
On Wed, 09 Jun 1999 15:57:56 -0400, you wrote:
>I have a table that has a numberic primary key.
>I also have a sequence that populates the primary key via a Before
>Insert trigger.
>
>How can I insert a record, and GET the primary key assigned to that
>record all in one statement?
>
>The trigger just
>
>select filelist_seq.nextval
>into :new.filelistnbr
>from dual;
>
>that is all..
>
>
>Suggestions.. ?
>
>-K
>
>
>
>
As long as the trigger only processes ONE row (eg: you never "insert into T select * from some_table_with_more_then_one_row") you can simply:
select s.currval from dual
in your application to find the last value used in the INSERT.
Additionally, with Oracle8.0 and up you can code:
insert into t ( ... ) values ( .... )
returning NV into :my_variable;
to get the assigned value back.
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |