Re: Trigger to set ID field using sequence

"Mike DiChiappari" <mike_at_mysticsoft.com> wrote in news:ael73f$6is_at_dispatch.concentric.net:

> 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.

The problem is you made it a table level trigger. It needs to be row level. The way you do that is to add the line "FOR EACH ROW" after the table name.

CREATE TRIGGER MYDB.visitors_auto_id
BEFORE INSERT ON MYDB.visitors
FOR EACH ROW
BEGIN ...

-- 
Ken Denny
http://www.kendenny.com/

If you think nobody cares about you, try missing a couple of payments.