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

In article <7im831$cp9$1_at_minus.oleane.net>, Vincent Lemaire <v.Lemaire_at_kheops.com> writes
>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 ?

Triggers aren't really the places for getting feedback, although you could try writing to a pipe or something similar.

It would probably be easier to assign the sequence value during your insert and get the currval then.
--
Andy Hardy. PGP key available on request