Re: SQL Server Autonumber equivalent in Oracle 8i
Date: Sun, 10 Dec 2000 01:45:51 GMT
Message-ID: <90un8f$e1k$1_at_nnrp1.deja.com>
[Quoted] 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 Sun Dec 10 2000 - 02:45:51 CET