Re: Using a Sequence from within a Trigger

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.

James Arvigo wrote:
>
> 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              Fort Point Partners, Inc.
(415)537-7027                   dmoles_at_ftpoint.com
--------------------------------------------------