Re: Getting NEXTVAL just prior to a COMMIT

From: <eaker_at_grda.avc.calgary.ab.ca>
Date: 1995/09/12
Message-ID: <433vfk$nta_at_rover.ucs.ualberta.ca>#1/1

In <433udo$it7_at_tpd.dsccc.com>, jstrange_at_imtn.dsccc.com (John Strange) writes:
>place you code in the ON-INSERT trigger
> if :field_name is null then
> select seq_name.nextval from dual ;
> end if;
>
>Craig Harper (caharper_at_hooked.net) wrote:
>:> We are looking for suggestions on how to assign an ID number based on the
>:> NEXTVAL of a sequence generator, after all the other fields of the table
>:> have been filled in.
>
>
>:> Craig Harper
>

I would suggest using the PRE-INSERT trigger instead. The ON-INSERT trigger overwrites  the default logic of this trigger, which is to perform the insert of a row into the database. PRE-INSERT fires just before the ON-INSERT trigger, which (presumably) you want to perform its default functionality.

The code above won't quite work, here is the corrected code:

In the PRE-INSERT trigger:

IF :key_field IS NULL THEN
  SELECT seq_name.nextval INTO :key_field FROM dual; END IF; I prefer to use cursors, so I usually write:

DECLARE
  CURSOR seq IS
    SELECT seq_name.nextval
    FROM DUAL;
BEGIN
  IF :key_field IS NULL THEN
    OPEN seq;
    FETCH seq INTO :key_field;
    IF seq%notfound THEN

      <error coding here>
      CLOSE seq;
      RAISE FORM_TRIGGER_FAILURE;

    END IF;
    CLOSE seq;
  END IF;
END; At any rate, this is the type of code I've been using for 4 years and it works fine.

..Lyne Received on Tue Sep 12 1995 - 00:00:00 CEST