Trigger to set ID field using sequence

From: Mike DiChiappari <mike_at_mysticsoft.com>
Date: 17 Jun 2002 17:43:11 GMT
Message-ID: <ael73f$6is@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.

Thanks,
Mike Received on Mon Jun 17 2002 - 12:43:11 CDT