Re: How do I create an Auto Increment Column?

dmausner_at_ameritech.x.net (dave mausner) wrote in <8EB3DE649dlm_at_206.141.192.241>:

>perhaps the easiest way would be to define the oracle table with an
>integer datatype for the auto-increment column. then attach an "insert
>for each row" trigger which sets the value of the column to a sequence
>nextval.
>
>you define a sequence using "create sequence MY_SEQ" and you reference
>the next value by the incantation "MY_SEQ.nextval".

Two comments on this after recently having the same question (I'm going on my 5 working day with Oracle).

1. If you will ever need to insert records that already have a value for the auto-increment field (like in multi-master replication), check for null before calling NextVal and don't assign a value if it's not null.
2. Make sure your trigger is BEFORE INSERT. May seem obvious to most but wasn't clearly specified.

Example:

CREATE SEQUENCE MySequence INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER MyTrigger
BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
  IF (:new.AutoInc is NULL)
  THEN
   BEGIN
    SELECT MySequence.NEXTVAL into :new.AutoInc FROM DUAL;    END;
  END IF;
END; Hope this helps. Good luck.

Wayne Menzie Received on Thu Jan 06 2000 - 23:03:19 CST