Re: how to catch the sequence value generated in a trigger

A copy of this was sent to "Vincent Lemaire" <v.Lemaire_at_kheops.com> (if that email address didn't require changing) On Fri, 28 May 1999 16:10:52 +0200, you wrote:

>I wrote this script :
>
>create or replace trigger tr before insert
>on t for each row
>declare
> nv number;
>begin
> select s.nextval into nv from dual;
> :new.c := nv;
> raise_application_error (-20101,nv);
>end;
>
>the raise_application_error call is followed by an automatic rollback
>and the new record is not inserted, althought I want it to be inserted and
>the sequence value returned to my application
>do you have any idea to avoid the rollback ? or any other method to return
>the sequence value ?
>

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

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 28 1999 - 09:31:03 CDT