Re: Does Oracle has an automatic counter field data type?

Hi Sami,

Yes, Oracle does have such a possibility. The objects to be used are sequences. You can create a sequence like this:

Create sequence MySequence start with 1 increment by 1

Select a new value (it's automatically increased by the number following 'increment by' when you get the next value ) is done like:

declare myvar number;
select mysequence.nextval
into :myvar
from dual;

Now you can create a before insert trigger on the table where the primary key is to be used in

create trigger mytrigger
on mytable
before insert
new as new
old as old
myvar number;
begin
select mysequence.nextval
into myvar
from dual;
:new.myprimarykey := myvar
end;

You may have to check on the exact syntax, but this is roughly the idea of solving the problem.

Roel Peeters
r.peeters_at_euro-partners.nl
Euro-Partners Informatici

Sami Heikkinen <sheikkin_at_rieska.oulu.fi> wrote in article <6gcr37$i5l$3_at_ousrvr3.oulu.fi>...
> Hi!
>
> Does Oracle has an automatic counter field data type for generating
> primary keys (incrementally) automatically. I need to implement this kind
> of surrogate key for tables. This kind of field type is used in MS Access
> and is type of counter field. It works like this: when a new record is
> added into table the primary key field is automatically generated by the
> DBMS without a need for user interference.
>
>
> --
> ================================================
> = Sami Heikkinen Department of Information =
> = Processing Science =
> = University of Oulu =
> = Finland =
> =----------------------------------------------=
> = MAIL: PHONES: =
> = Valkkyla 5 B 17 home: 08-3136530 =
> = 90100 OULU mobile 049-944251 =
> = job: 08-5652815 =
> = E-MAIL: =
> = sheikkin_at_rieska.oulu.fi =
> = Sami.P.Heikkinen_at_ntc.nokia.com =
> ================================================
>
Received on Tue Apr 07 1998 - 00:00:00 CEST