Re: get id after insert

1. In n-user environment, how would anyone know that the record THEY just inserted was the last one inserted into the table?
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
>
>
>
Received on Tue Dec 21 1999 - 16:43:01 CST