Re: Undoing a sequence increment with autonumbering

Doesn't matter if it is in an on- insert trigger. You can't do commits in triggers. So it could still get rolled back. (and the number "lost" - examples of how it could be lost include a db problem causing a rollback.) You are serializing around one point - not a good idea.(won't scale) The paper analogy is the same - often companies generate preprinted blank PO forms and have the numbers printed on the forms sequentially. (just like numbers on checks.). Same problem, what if a form gets lost or someone makes a type and needs to change something or rips up the form and throws it in the garbage - same thing. At least into he database you have a list of all the PO's that got generated and what their numbers are (regardless of whether they are sequential or not). I am assuming there is sufficient security in the database to not allow a PO to be generated and then the person erase all their tracks.

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:3E42A9AC.6080609_at_netscape.net...


> Jim Kennedy wrote:

> > 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

> >>

> >

> >

> >

>


> I wouldn't wait for that person - it's all in a on-insert


> trigger. So, even if the person decides to go off for lunch,

> with the screen all filled in, but not committed,

> breaks his leg upon the way back, slightly intoxicated, winds

> up in hospital for a day or two and pressed 'Enter' only then,

> no sweat.

> You would have to put your coding in the right spot, of course.

>


> Your paper examples do not have any meaning; you can still produce


> the logging from your system, proving that number once existed,

> but was withdrawn, because the wrong stuff was printed on

> it... You can prove it by reporting that off your systems.

> You cannot when using sequences, unless uncached sequences

> are used, as cached ones will always end up having holes.

>


> Up to the OP to decide, I'd say - maybe he was better off with


> your explanation of autonomous transactions in the first place,

> in stead of 'so what'.

>


> Frank


>