Re: Undoing a sequence increment with autonumbering

No. It "uses up the number" So what.
Jim
"Knut Hovda" <knut.hovda_at_pti.no> wrote in message news:787c1a4a.0302050752.3a532d9_at_posting.google.com...
> Hello,
>
> I use a sequence and a trigger to mimic autonumbering on Oracle, and
> this works fine. However, if an insert statement fails, the sequence
> is still incremented, thereby "using up" numbers that could have been
> used as ID's.
>
> If there a way to undo or avoid a sequence increment if the insert
> statement failed?
>
> The code I'm currently using is:
>
> CREATE TABLE Field (
> F_ID number(16) NOT NULL PRIMARY KEY,
> NAME varchar2(30) NOT NULL UNIQUE);
>
> CREATE SEQUENCE FieldSeq;
>
> CREATE OR REPLACE TRIGGER FieldTrig
> BEFORE insert ON Field
> FOR EACH ROW
> begin
> select FieldSeq.nextval into :new.F_ID from dual;
> end;
> /
>
> In advance, thanks for your help.
>
> Regards,
>
> Knut Hovda (knut.hovda_at_pti.no)
Received on Wed Feb 05 2003 - 17:48:26 CET