Re: Undoing a sequence increment with autonumbering
Jim Kennedy wrote:
> 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)
>
>
>
Some (mostly legal) requisites prescribe the use of
sequential numbering of documents, e.g. bills. And with
sequential I mean "no holes".
The 'standard' solution is to use a code table; you can
have one system wide (create table code_table (id varchar2(30),
last_value number);) or per table (code_tablename_table (last_value
number);).
Your trigger would:
select last_value from your code table
insert into your regular table
update your code table set last_value=last_value + 1
commit;
In case of a rollback, the update on code_table is rolled back,
too.
To make things idiot proof, do a select for update, thus locking
the record (in case of a single code table per table this would
effectively lock the table...) for other, simultaneous transactions.
Not as effective as a sequence, but pretty standard when you are
required to deliver contiguous sequentially numbered records.
Frank
Received on Wed Feb 05 2003 - 21:27:18 CET
Original text of this message
