Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Trigger to set ID field using sequence

Re: Trigger to set ID field using sequence

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 17 Jun 2002 19:38:13 +0100
Message-ID: <1024339034.971.0.nnrp-10.9e984b29@news.demon.co.uk>

I stand corrected about the error message - it is entirely appropriate to the error that I failed to spot when reading the trigger.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Jonathan Lewis wrote in message
<1024338404.575.0.nnrp-10.9e984b29_at_news.demon.co.uk>...

>
>See the FAQ
>
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>In this case, it looks as if the error message is
>the problem, rather than the approach you are taking.
>
>
>--
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Next Seminars
> UK June / July
> Australia July / August
>http://www.jlcomp.demon.co.uk/seminar.html
>
>Mike DiChiappari wrote in message ...
>>I am in the process of "porting" a SQL Server database to Oracle. SQL
>>Server has the concept of IDENTITY. So when a row is created/inserted,
the
>>id column (which is declared as IDENTITY) gets a new unique id that is
>>automatically incremented.
>>
>>I am trying to do the same with Oracle. The idea is to create a sequence
>>and have a trigger automatically set the ID field when a new row is
>>inserted. My PL/SQL to create the trigger is:
>>
>>CREATE TRIGGER MYDB.visitors_auto_id
>>BEFORE INSERT ON MYDB.visitors
>>BEGIN
>> :new.ID := MYDB.VISITORSEQ.NEXTVAL;
>>END;
>>
>>When I try to run this PL/SQL, I get the error:
>>
>>CREATE TRIGGER MYDB.visitors_auto_id
>> *
>>ORA-04082: NEW or OLD references not allowed in table level triggers
>>
>>If I understand this correctly, :NEW and :OLD can only be used in views.
>Is
>>that true? If so, how can I simply fill the ID field in my table to a
>>unique number (such as a sequence). It seems like this is a common thing
to
>>do, so there must be a simple way to do it.
>>
>>Thanks,
>>Mike
>>
>>
>>
>
>
Received on Mon Jun 17 2002 - 13:38:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US