Re: SQL Server Autonumber equivalent in Oracle 8i
Date: Mon, 20 Nov 2000 21:05:32 GMT
Message-ID: <8vc3ml$bm$1_at_nnrp1.deja.com>
In our last gripping episode 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.
>
Code really should be tested before it is posted:
create or replace trigger autoincrement
before insert on testit
for each row
begin
:new.rec_seq := testit_seq.nextval;
end;
Warning: Trigger created with compilation errors.
SQL> show errors
Errors for TRIGGER AUTOINCREMENT:
LINE/COL ERROR
-------- ---------------------------------------------------------------
2/2 PL/SQL: Statement ignored
2/29 PLS-00357: Table,View Or Sequence
reference 'TESTIT_SEQ.NEXTVAL'
not allowed in this context
Although I have been unable to access this site today I have in the past and remember that there are a number of good suggestions:
http://govt.us.oracle.com/~tkyte/Mutate/index.html
Usually this type of trigger, if written in the conventional manner, will generate 'mutating table' errors. Tom has a way around that at the site listed above.
-- David Fitzjarrell Oracle Certified DBA Sent via Deja.com http://www.deja.com/ Before you buy.Received on Mon Nov 20 2000 - 22:05:32 CET