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: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 12 Dec 2006 09:13:34 -0800
Message-ID: <1165943614.72092@bubbleator.drizzle.com>


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.
>
>
> insert into seqtest (label) values ('first');
> insert into seqtest (label) values ('second');
> insert into seqtest (label) values ('third');
> insert into seqtest (label) values ('fourth');
> insert into seqtest (label) values ('fifth');
>
> SQL> commit;
>
> Commit complete.
>
>
> SQL> select * from seqtest;
>
> SERIAL LABEL
>
> ---------- ----------------------------
> 0 first
> 1 second
> 2 third
> 3 fourth
> 4 fifth
>
> ---------------------------------------------------------------------------
> / Charles J. Fisher | "Four hostile newspapers are more to be feared /
> / chas_at_syro.org | than a thousand bayonets." /
> / http://rhadmin.org | --Napoleon /
> ---------------------------------------------------------------------------

This is a bad idea. Use a sequence.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Dec 12 2006 - 11:13:34 CST

Original text of this message

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