Auto Sequencing When Insert

From: Kenneth Xu <xu_dong_at_irel.com.sg>
Date: Fri, 10 Jul 1998 17:53:43 +0800
Message-ID: <35a5e49d.0@news.cyberway.com.sg>

Hi all,

We used to be Informix users before and new to Oracle.

We used serial data type in Informix to generate the auto sequencing number, application do need to specify a value for such a field when inserting a record to database.

Now we want to change the database engine to Oracle but cannot find a match. We know Oracle provides a sequence object which can do the similar job, so we use a trigger to simulate that behave.

Unfortunately, We encountered the compilation error with following code:

---

create table TestTable(

    UserID number(9),
    Name char(50)
);

create sequence TestSeq;

create trigger Test_BeforeInsert
before insert on TestTable for each row begin

    if :new.UserID is null then

        :new.UserID := TestSeq.nextval;     end if;
end;

---

It is very much appreciated if somebody point me a direction.

TIA, Ken. Received on Fri Jul 10 1998 - 04:53:43 CDT