Home -> Usenet -> comp.databases.oracle -> Undoing a sequence increment with autonumbering

Undoing a sequence increment with autonumbering

From: Knut Hovda <knut.hovda_at_pti.no>
Date: 5 Feb 2003 07:52:55 -0800
Message-ID: <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 - 16:52:55 CET












Original text of this message