Re: Trigger to set ID field using sequence

Mike,
You need to create a row level trigger to do this.

CREATE TRIGGER MYDB.visitors_auto_id
BEFORE INSERT ON MYDB.visitors
FOR EACH ROW
BEGIN
     SELECT MYDB.VISITORSEQ.NEXTVAL INTO :new.ID FROM DUAL; END; Hth
Anthony Hogan

Mike DiChiappari wrote:
>
> 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 - 12:54:32 CDT