Results 1 to 3 of 3

Thread: auto increment

  1. #1
    Join Date
    Feb 2004
    Posts
    79

    Unanswered: auto increment

    how to create a filed that would auto increment in a table- and be a primary key-in oracle 9i

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: auto increment

    Originally posted by sampav
    how to create a filed that would auto increment in a table- and be a primary key-in oracle 9i
    You need:

    1. Sequence
    2. Trigger

    CREATE SEQUENCE my_sequence
    INCREMENT BY 1
    START WITH 1
    MINVALUE 1
    MAXVALUE 99999999999999
    NOCYCLE
    NOCACHE
    /

    CREATE OR REPLACE TRIGGER trg_my_table
    BEFORE INSERT
    ON my_table --TABLE NAME
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE ln_num NUMBER;
    BEGIN
    SELECT my_sequence.nextval into ln_num FROM dual;
    :new.ID:=ln_num; -- ID is column of table my_tablename
    End;
    /

  3. #3
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    I agree.

    The command to create a sequence can be very simple, for most purposes. "CREATE SEQUENCE myseq;" That's it, no parameters required other than the name (myseq.) Now you have a sequenece that can generates unique numbers for a very, very long time (see the free Oracle SQL Reference guide that comes with your Oracle software.)

    The trigger can be simplified too. you can select the sequence's next val directly into the column (i.e., the magic line is SELECT myseq.nextval INTO :new.id FROM dual). However, if you need to do other processing of the number before assigning it to the ID column, then using the variable as an intermediate step is a good idea because it helps readability in long triggers.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •