Re: SQL Server Autonumber equivalent in Oracle 8i

From: <rwhita_at_my-deja.com>
Date: Sun, 10 Dec 2000 01:45:51 GMT
Message-ID: <90un8f$e1k$1@nnrp1.deja.com>

Hello I'm looking for the same anwser. I've tried the following but I get this error when compiling it. Warning: Trigger created with compilation errors.

Here is the script I tried

CREATE SEQUENCE PARTID
 INCREMENT BY 1
 START WITH 1
 NOCACHE; CREATE TABLE ITEMS (
 DATECREATED DATE DEFAULT SYSDATE,
 PARTNUMBER NUMBER,
 DESCRIPTION VARCHAR2(40),
 PRICE NUMBER(4,2)
);

CREATE OR REPLACE TRIGGER AUTOINCREMENT
BEFORE INSERT ON ITEMS
FOR EACH ROW
BEGIN
  :new.PARTNUMBER := :PARTID.nextval;
END;
/

I've been all the way through the Oracle 8 Complete Reference guide looking for the anwser but nothing. They show how to use a sequence in a insert statement but thats all. Any help would be great.

In article <8vbqic$nmr$1_at_nnrp1.deja.com>,   mikkrolewski_at_my-deja.com wrote:
> In article <8vbpap$mjr$1_at_nnrp1.deja.com>,
> jkipp_at_my-deja.com wrote:
> > In article <8vbgkh02kif_at_enews3.newsguy.com>,
> > "Robert Martin" <rwmartin_at_sisconet.com>
 wrote:
> > > I am a newbie to Oracle and I have read
 that I can create an SQL
 Server
> > > Autonumber equivalent by using a sequence
 and a trigger. My problem
 is, I
> > > have never written a trigger. I created my
 sequence, and from the
 looks of
> > > it in the OEM it appears to be fine. How do
 I write the trigger?
 Does
 anyone
> > > know of any good documentation on the net
 on writing Oracle
 triggers?
 Please
> > > help. Thanks :)
> > >
> > > Oh and I am working in NT
> >
> > My question is very similar to yours. See:
 creating sequence for
> > autonumber. I am too wondering about
 triggers. I will let you know
 what
> > I can find out.
> >
> > JIm
> >
> > > -------------------------------------------
> > > Robert Martin (rwmartin_at_sisconet.com)
> > > Design Engineer
> > > SISCO Inc,
> > > 6605 19 1/2 Mile Road
> > > Sterling Heights, MI 48314-1408
> > > Phone : 810-254-0020 (ext 125)
> > > Fax : 810-254-0053
> > >
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >

>
> Most of these questions are part of the user
 manuals supplied by Oracle.
>
> You might try investing in some of the Oracle
 books like the Oracle 8i
> The Complete Reference by Osborne press for
 about $70.

>

> create or replace trigger autoincrement
> before insert on <table>
> for each row
> begin

>

> :new.<field> := <seq>.nextval;
> end;

>
> where <table> is the name of the table, <seq>
 is the name of the
> sequence, etc.

>
> I am reasonably sure this will work -- did not
 try it on database.
>
> Obviously, this will consume sequences if you
 roll back an insert as
> the trigger is used every time an row insert is
 attempted.

>

> Michael Krolewski

>

> Sent via Deja.com http://www.deja.com/
> Before you buy.

>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Dec 09 2000 - 19:45:51 CST