Autonumber
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Autonumber

  1. #1
    Join Date
    Jul 2001
    Posts
    1
    Hi,

    I have an Oracle table created. The field 'SourceID' which is now a number field in Oracle used to the Primary Key in Access.

    Now, I fished thru the forums to get this answer and the only thing that I found was use a sequence and a trigger, which I have no idea how to do.

    So, could somebody point me in the right direction by either sending some code (currently i do all adding, updating, etc from my local machine sql statements) and direct some docs.

    Thanks in advance.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    SQL> create table xyz (id number(10), data varchar2(10));
    Table created.
    
    SQL> create sequence autonum ;
    Sequence created.
    SQL> select autonum.nextval from dual;
    
       NEXTVAL
    ----------
             1
    
    SQL>  (OK, I skipped a couple steps here while I debugged my trigger.  The result is below)
    SQL> edit
    Wrote file afiedt.buf
    
      1  create or replace trigger xyz_bi before insert on xyz
      2  for each row
      3  begin
      4     select autonum.nextval into :new.id from dual;
      5* end;
    SQL> /
    
    Trigger created.
    
    SQL> desc xyz
     Name                                      Null?    Type
     ----------------------------------------- -------- ------------------
     ID                                                 NUMBER(10)
     DATA                                               VARCHAR2(10)
    
    SQL> insert into xyz (data) values ('Z');
    
    1 row created.
    
    SQL> insert into xyz (data) values ('Y');
    
    1 row created.
    
    SQL> insert into xyz (data) values ('X');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from xyz;
    
            ID DATA
    ---------- ----------
             2 Z
             3 Y
             4 X
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

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



Click Here to Expand Forum to Full Width