Re: Trigger to set ID field using sequence

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

>

>

>