Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!news.mailgate.org!nntp.infostrada.it!area.cu.mi.it!news.newsland.it!newsfeed00.sul.t-online.de!newsmm00.sul.t-online.de!t-online.de!news.t-online.com!not-for-mail From: lothar.armbruester@t-online.de (=?utf-8?q?Lothar_Armbr=C3=BCster?=) Newsgroups: comp.databases.oracle.server Subject: Re: Autoincrement without a sequence Date: Tue, 12 Dec 2006 18:16:46 +0100 Organization: T-Online Lines: 41 Message-ID: <87zm9trq4h.fsf@prometeus.none.local> References: Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit X-Trace: news.t-online.com 1165943741 03 6704 ThAtLMFlLMUSSONK 061212 17:15:41 X-Complaints-To: usenet-abuse@t-online.de X-ID: VC9riBZOweOa7bcvlZkg51sZs6kNnzC+iiXqMacgjWWNME-DK2Murk User-Agent: Gnus/5.1007 (Gnus v5.10.7) Emacs/21.4 (gnu/linux) Cancel-Lock: sha1:EU10FFABs4rdSqyyk91GMGAm8bM= Xref: usenetserver.com comp.databases.oracle.server:419024 X-Received-Date: Tue, 12 Dec 2006 12:16:12 EST (text.usenetserver.com) Charles writes: > 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? > [...] > > 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. > [...] Hello Charles, this approach is not multi user safe, since two concurrent sessions do not see rows inserted by the other one. So each session gets the same value for max(serial). You will have to ensure one one session inserting at any given point of time. Or use sequences since that is the purpose what they are for. ;-) Hope that helps, Lothar -- Lothar Armbrüster | lothar.armbruester@t-online.de Hauptstr. 26 | 65346 Eltville |