Path: text.usenetserver.com!out03a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!news2.google.com!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail From: Robert Klemme Newsgroups: comp.databases.oracle.server Subject: Re: Autoincrement without a sequence Date: Tue, 12 Dec 2006 18:02:04 +0100 Lines: 37 Message-ID: <4u85k5F16aj4cU1@mid.individual.net> References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Trace: individual.net fciXtXnbWu9c2Xb4Eeu0oAw13GncCQ/KSd2N/bvKiVhA1Ws5U= User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) In-Reply-To: Xref: usenetserver.com comp.databases.oracle.server:419020 X-Received-Date: Tue, 12 Dec 2006 12:01:59 EST (text.usenetserver.com) On 12.12.2006 17:53, Charles wrote: > One of my users is inserting 200 rows into a table once every three months > on an Oracle 7 instance. Performance is not an issue. Are there any > dangers or disadvantages to using the trigger below? I/we understand that > sequence values will be reused if deleted. Is it best to reserve sequences > for high-volume applications? Will the existence of a (full scan) unique > index in general provide both integrity and reasonable performance? > > Pardon me if this has been answered in a faq or in previous discussions. > > > > SQL> create table seqtest (serial number primary key, label varchar2(2000)); > > Table created. > > > create or replace trigger seqtest_autoincrement > before insert on seqtest > for each row > declare > nextval number; > begin > select max(serial) + 1 into nextval from seqtest; > :new.serial := nvl(nextval, 0); > end; > / > > Trigger created. Short answer: I believe this is not concurrency safe. Why do you not want to use a sequence? Sequences are built for exactly this situation and give you much simpler code. Or does Ora 7 have no sequences? Regards robert