Home » SQL & PL/SQL » SQL & PL/SQL » .nextval in Insert Statement
.nextval in Insert Statement [message #36756] Tue, 18 December 2001 01:13 Go to next message
Philipp Salzgeber
Messages: 1
Registered: December 2001
Junior Member
Hi,

in a PL/SQL procedure I insert a bunch of rows in a table...:

insert into cuprankings (memberid, cupid, season, id, rankInCat, cupCategoryID)
select distinct ru.memberid,
ra.cupid,
ra.season,
cuprankings_seq.nextval,
0,
cc.id
from runs ru, races ra, cupcategories cc, cups cu
where ru.raceid=ra.id
and cc.catid=ru.catid
and ra.cupid=cu.id
and cu.calcmode>0
and ru.cuppoints>=0
and ra.cupid=cc.cupid;

this gives me an "ORA-02287: sequence number not allowed here" error. I tried to use a before insert trigger to get the .nextval value, but I did not manage to get it right, how do I have to create such a trigger?

tia

Philipp Salzgeber

----------------------------------------------------------------------
Re: .nextval in Insert Statement [message #36757 is a reply to message #36756] Tue, 18 December 2001 01:41 Go to previous message
trig
Messages: 1
Registered: December 2001
Junior Member
CREATE OR REPLACE TRIGGER bi_table
Before insert
on cuprankings
FOR EACH ROW
Begin
select cuprankings_seq.nextval into :new.id from dual ;
END;

----------------------------------------------------------------------
Previous Topic: Re: Error in Stored Procedure
Next Topic: sound
Goto Forum:
  


Current Time: Wed Apr 01 07:15:06 CDT 2020