Re: Autoincrement without a sequence

On 12.12.2006 17:53, 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.

Short answer: I believe this is not concurrency safe. Why do you not want to use a sequence? Sequences are built for exactly this situation and give you much simpler code. Or does Ora 7 have no sequences?

Regards

        robert Received on Tue Dec 12 2006 - 11:02:04 CST