Re: Sequence/autonumber

From: Hakan <heren_at_home.com>
Date: 2000/07/24
Message-ID: <397CA0CF.F7C95D46@home.com>#1/1

hi,

one way is to use a trigger.

SQLWKS> create table temp
     2>       (data varchar2(10),
     3>        autonumber number)
     4> /

Statement processed.

SQLWKS> create sequence tmp
     2>  increment by 1
     3>  start with 1
     4>  maxvalue 9999999999
     5>  cycle
     6>  nocache
     7>  order
     8> /

Statement processed.

SQLWKS> create or replace trigger test_trigger
     2>   before insert on temp
     3>   for each row
     4> begin
     5>   select tmp.nextval into :new.autonumber from dual;
     6> end;
     7> /

Statement processed.

SQLWKS> begin
     2> for i in 1..5
     3> loop
     4>   insert into temp(data) values('test');
     5> end loop;
     6> commit;
     7> end;
     8> /

Statement processed.
SQLWKS> select * from temp

     2> /
DATA       AUTONUMBER
---------- ----------
test                1
test                2
test                3
test                4
test                5

5 rows selected.

code to test:
create table temp

      (data varchar2(10),
       autonumber number)

/
create sequence tmp
 increment by 1
 start with 1
 maxvalue 9999999999
 cycle
 nocache
 order
/
create or replace trigger test_trigger
  before insert on temp
  for each row
begin
  select tmp.nextval into :new.autonumber from dual; end;
/
begin
for i in 1..5
loop
  insert into temp(data) values('test'); end loop;
commit;
end;
/
select * from temp
/

Hakan
Kalle W wrote:
>
> How do I accomplish what in other database-engines are called
> autonumber? Like in Access for example. That is unique
> generation of indexes/record-keys. Is there a datatype that
> manages this automatically?
>
> /Kalle W
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
Received on Mon Jul 24 2000 - 00:00:00 CDT