Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Trigger to set ID field using sequence
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>...Received on Mon Jun 17 2002 - 13:38:13 CDT
>
>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
>>
>>
>>
>
>