Re: Auto-increment sequence

Martin Doherty wrote:

> Mike, see my posting earlier today to Owen Gibbons "Re: setting a
> default ID with BC4J Entity".
>
> If you use a sequence object to generate keys, gaps *will* occur. May I
> ask why you need your numbers to be in unbroken sequence? If your only
> requirement is uniqueness, a gap shouldn't cause a problem. If your
> auditors get into a panic because there is no record of Purchase Order #
> 1234, you'll have to rethink your approach to generating IDs.
>
> Martin
>
> Mike Trozzo wrote:
>
> >Hi,
> >
> >I wrote a sequence to generate unique ID numbers for a table, along with a
> >before insert trigger. I'm also writing an application in Delphi to access
> >this database. I'm using 9i and Delphi 5.
> >
> >My problem is this: When I run the insert procedure, and come up against a
> >not null violation, the sequence increments anyway, and the next time I
> >successfully insert a record, the previous number is skipped. How do I
> >avoid this problem and keep the number in sequence?
> >
> >Thanks,
> >Mike Trozzo
> >
> >
> >
> >

The simple solution to what Martin has pointed out is as follows:

BEGIN
   INSERT INTO target_table using NEXTVAL EXCEPTION
   WHEN OTHERS THEN
      INSERT INTO error_log using CURRVAL END; That way no number is ever lost and you have a complete auditable trail.

Daniel Morgan Received on Thu Nov 14 2002 - 10:18:46 CST