Re: Undoing a sequence increment with autonumbering

Bad idea.(and yes I've seen this solution before) Your whole system is serialized around 1 point. Only 1 person can do an invoice at a time. So everyone waits on one person. In the real world pieces of paper do get lost or the "PO" that is sequentially numbered is written on and the wrong thing was written on it. (making it useless). If you must account for holes then (who did it) then use an autonomous transaction to capture the "lost" number. (see asktom.oracle.com and do a search). By still using a sequence and using autonomous transactions you can account for all sequence numbers.(and thus meet a "legal" - read accounting - requirement) Jim

--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Frank" <fvanbortel_at_netscape.net> wrote in message
news:3E4173A6.2010601_at_netscape.net...


> 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


>