Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Autoincrement without a sequence

Re: Autoincrement without a sequence

From: Lothar Armbrüster <lothar.armbruester_at_t-online.de>
Date: Tue, 12 Dec 2006 18:16:46 +0100
Message-ID: <87zm9trq4h.fsf@prometeus.none.local>


Charles <chas_at_syro.org> 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_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |
Received on Tue Dec 12 2006 - 11:16:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US