Re: Surrogat data types

From: David Pattinson <david_at_addease.com.au>
Date: Fri, 21 May 1999 10:16:42 +1000
Message-ID: <3744A5EA.C124A750@addease.com.au>

Achim,

I have the following trigger calling a sequence, to simulate an autoincrementing key in Oracle.

(When I insert from SQL*Plus the value assigned jumps by two or three instead of one as expected. I am the only connection inserting into the table. This is not a big problem as I still get a key value, but I'd like to know why it's happening.)

---

CREATE SEQUENCE tUserHistory_Seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10
/

CREATE OR REPLACE TRIGGER          tUserHistory_BIT_SEQ
BEFORE INSERT ON          tUserHistory

REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT tUserHistory_SEQ.NextVal INTO :NEW.SEQUENCEID FROM DUAL;
END;
/

---

Regards, David.

Achim Reiners wrote:

> Hello,
>
> I'm looking for a surrogat data type for primary keys in ORACLE.
> Something like SERIAL in Informix or IDENTITY in Sybase.
> The database should generate a unique value by itself.
>
> I'm not yet very familar with ORACLE. There is a construct called
> SEQUENCE
> in ORACLE generating unique numbers. But as far as I know there is no
> relation between the sequence and the column that should have
> incremented values.
> The problem is that the program/person doing the insert is responsible
> for choosing the
> correct sequence type. This is a quite bad situation, isn't it.
>
> My question:
> Is there another way to have ORACLE generate surrogat-keys in a way that
> the user
> does not have to keep track of choosing the right sequence-type?
>
> Thanks for any help
>
> Achim
>
> P.S.: As I don't read this group regularly, respond by Email in
> addition.
Received on Thu May 20 1999 - 19:16:42 CDT