Re: get id after insert

The first five words is the key i'm looking for. I must know no matter what that noone could have inserted a greater value between my insert and the return of the id. And unfortunally I can't use insert..returning.

tia
roland

> 2. Unless inserts were only permitted through a procedure/function as
in
>
> declare id number;
> begin
> exec id:=insert_a_value('some data');
> end;
>
> where insert_a_value() was something like:
>
> create function insert_a_value( inbound ...) return number is
> temp number;
> begin
> temp:=sequence_name.nextval;
> insert into my_table values ( TEMP, inbound );
> return (temp);
> end;
>
> Then one would never know if the current value of the sequence was
from
> their insert.
>
> Vyacheslav Soldatov <sslava_at_cbank.kz> wrote in message
> news:83nl30$6o8$1_at_mail.nursat.net...
> > Try it
> >
> > 1.
> > create sequence s_sequence start with 1 nocycle;
> >
> > 2.
> > create package pkg_sequence as
> > function get_next return number;
> > function get_curr return number;
> > end pkg_sequence;
> > /
> > 3.
> > create package body pkg_sequence as
> >
> > v_curr number;
> >
> > function get_next return number is
> > begin
> > select s_sequence.nextval
> > into v_curr
> > from dual;
> > return v_curr;
> > end get_next;
> >
> > function get_curr return number is
> > begin
> > return v_curr;
> > end get_curr;
> >
> > end pkg_sequence;
> >
> > 4.
> > crate trigger tr_sequence before insert on your_table
> > if inserting then :new.id := pkg_sequence.get_next;
> > end;
> >
> > 5.
> > -- in your code
> > declare
> > v_id integer;
> > begin
> > insert into your_table(id, column1) values (1, column1_value);
> > v_id := pkg_sequence.get_curr;
> > end;
> >
> >
> > But may be there is a better solution ... :)))
> >
> > Slava
> >
> >
> >
>
>

--
Roland Carlsson
Certified Java programmer
Skövde
Sweden

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 22 1999 - 04:20:59 CST