Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Autoincrement without a sequence
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