Auto-Incremented Oracle PK - Trigger/Sequence

[Tom Adamo]

I've been working with Oracle auto-incremented PK columns (which in Oracle, you need to create a sequence and a before insert trigger). I noticed gaps in my data and I'd like to point out what I found in case someone else needs the info. Also, I'll note that I'm on Oracle version 11g here.

I created the trigger as documented with Rose::DB::Object like so:

CREATE TRIGGER mytable_insert BEFORE INSERT ON mytable

FOR EACH ROW

BEGIN

    SELECT NVL(:new.id, mytable_id_seq.nextval)

       INTO :new.id FROM dual;

END;

For whatever reason, even when new.id has a NOT NULL value, the nextval is executed. Although, the correct value gets stored in the database, the nextval was executed (bumping on the sequence).

I've found writing the trigger like this will not cause any gaps in the data:

CREATE TRIGGER mytable_insert BEFORE INSERT ON mytable

FOR EACH ROW

BEGIN

   IF :new.id IS NULL THEN

       :new.id := schema.mytable_id_seq.nextval;   

   END IF;

END;

- Tom