Using a Sequence from within a Trigger

Hello,

I'm trying to write a ON INSERT trigger that uses a SEQUENCE to make sure that each new record's primary key field gets inserted with the next higher value for that table. I've been unable to make this work. All the sequence usage examples I find are always phrased as INSERT statements:

INSERT INTO TableName VALUES(a, b, sequence.NextVal);

But I need to do this in the trigger instead. Something like:

CREATE SEQUENCE test1_seq START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE CACHE 20 NOORDER; CREATE OR REPLACE TRIGGER test1_bi_rtrg
BEFORE INSERT ON test1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
  :new.testid := test1_seq.NextVal;
END; Where "testid" will be the Primary Key field for table "Test1".

However, this doesn't work. I get the following errors:

SQL> / Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER ORACLE.TEST1_BI_RTRG:

LINE/COL ERROR

2/3      PL/SQL: Statement ignored
2/35     PLS-00357: Table,View Or Sequence reference
         'ORACLE.TEST1_SEQ.NEXTVAL' not allowed in this context

SQL> Can someone please advise how's the right way to do this?

Thanks much!

--
James Arvigo

============================================================


* SQL Server & Oracle DBA

* Software & Intranet Developer

* The Maxim Group

* Austin, Texas

*----------------------------------------------------------


* EMAILS:

* JArvigo -AT- Hotmail.com

============================================================