Autoincrement without a sequence

From: Charles <chas_at_syro.org>
Date: Tue, 12 Dec 2006 16:53:50 GMT
Message-ID: <Pine.BSO.4.58.0612121044570.17960@bart.rhadmin.org>

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              /
---------------------------------------------------------------------------

Received on Tue Dec 12 2006 - 10:53:50 CST