Re: Simple Datatype Question

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 01 Oct 1998 21:05:03 GMT
Message-ID: <3613ed68.28168514@dcsun4.us.oracle.com>

On 1 Oct 1998 20:49:18 GMT, "Rob McElroy" <RobbieLee_at_Worldnet.att.net> wrote:

>Is their a field datatype in Oracle that is similar to Access's AutoNumber
>Datatype or SQL Servers Identity datatype?
>
>What I want is a field that generates a unique number for each new record.
>
>I realize this is an easy question, bear with me, I am learning the workings
>of Oracle.
>

You need to use a sequence and a before insert trigger.

eg.

SQL> create sequence seq_id start with 1 increment by 1;

Sequence created.

SQL> create table T(
2 id number,
3 value varchar2(20)
4 );

Table created.

SQL> create or replace trigger bi_t
  2 before insert on T
  3 for each row
  4 begin
  5 select seq_id.nextval

  6      into :new.id
  7      from dual;

  8 end;
  9 /

Trigger created.

SQL> insert into t ( value ) values ( 'Test' );

1 row created.

SQL> insert into t ( value ) values ( 'Test Again' );

1 row created.

SQL> select * from T;

        ID VALUE

---------- --------------------
         1 Test
         2 Test Again

Hope this helps.

chris.

>Thanks for your time!
>
>
>
Received on Thu Oct 01 1998 - 16:05:03 CDT