Re: Undoing a sequence increment with autonumbering
From: Frank <fvanbortel_at_netscape.net>
Date: Thu, 06 Feb 2003 19:30:04 +0100
Message-ID: <3E42A9AC.6080609_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
>>
Date: Thu, 06 Feb 2003 19:30:04 +0100
Message-ID: <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.
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 Received on Thu Feb 06 2003 - 19:30:04 CET