Re: Auto Sequencing When Insert

From: David Mortenson <dmortenson_at_home.com>
Date: Sun, 12 Jul 1998 04:26:20 GMT
Message-ID: <35a83a95.54462663@news.grnvle1.sc.home.com>

Try:

CREATE OR REPLACE TRIGGER Test_BeforeInsert BEFORE INSERT ON TestTable
FOR EACH ROW
BEGIN

	If :NEW.UserID IS NULL THEN
		SELECT TestSeq.NEXTVAL
		INTO :NEW.UserID
		FROM dual;
	END IF;

END preins_songs;
/

On Fri, 10 Jul 1998 17:53:43 +0800, "Kenneth Xu" <xu_dong_at_irel.com.sg> wrote:

>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 Sat Jul 11 1998 - 23:26:20 CDT