Using a Sequence from within a Trigger

[James Arvigo]

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

[David Moles]

Instead of

:new.testid := test1_seq.NextVal;

use

select test1_seq.NextVal into :new.testid from dual;

AFAIK, you can't access test1_seq.NextVal as a variable.

--
--------------------------------------------------
David Moles Fort Point Partners, Inc.
(415)537-7027 dmo...@ftpoint.com
--------------------------------------------------