Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using a sequence inside BEFORE TRIGGER

786725Jul 31 2010 — edited Jul 31 2010
Hi all,

I just created a testtable and a sequence to use as a primary key column value for that table.

I tried to create a BEFORE INSERT trigger on that table and in the trigger i tried to set up the primary key column value using the sequence
but while compiling i am getting the error "Error(9,30): PLS-00357: Table,View Or Sequence reference 'SEQ_OF_TESTTABLE.NEXTVAL' not allowed in this context"

My Version:Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

All the objects created with the same user. I would appraciate any help, thanks all


EDIT
I solved the problem using the below
create or replace
TRIGGER Bef_Ins_On_Testtable
BEFORE INSERT ON TestTable
FOR EACH ROW
declare 

ntemp_id INT;

BEGIN

SELECT SEQ_OF_TESTTABLE.NEXTVAL INTO ntemp_id FROM DUAL ;

DBMS_OUTPUT.PUT_LINE('İNSERTED');
:NEW.VSURNAME := 'HAKKİ' ; 
:NEW.NID := ntemp_id;

END;
But i wonder why i can use the sequence(just as seqeunce_name.NEXTVAL) in INSERT statement and why cant in trigger?

Edited by: user9371286 on 31.Tem.2010 04:15

Edited by: user9371286 on 31.Tem.2010 04:21

Edited by: user9371286 on 31.Tem.2010 04:27
This post has been answered by ZahKar on Jul 31 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 28 2010
Added on Jul 31 2010
4 comments
16,533 views