Re: using a sequence inside an insert trigger
Date: 1995/04/20
Message-ID: <pconnors.798342421_at_BIX.com>#1/1
eaglesCo_at_ix.netcom.com (Anna Horton) writes:
>OK, I'm at the end of my rope. I am using access as the front end ( and
> no I can't change the project is too far along ). I am trying to mimic
>the action of the automatic counter field using SEQUENCE and an insert
>trigger on the table. PL/SQL does not appear to allow the use of a
>sequence in the body of the PL/SQL. Is a stored procedure and some hack
>on the access side of the equation the only way to do this?
Assuming you're using an Oracle sequence, the way to do it is like this:
DECLARE
NEXT_SEQ NUMBER;
CURSOR UPDATE_NEXT_SEQ IS
SELECT :MY_SEQUENCE.NEXTVAL FROM DUAL;
BEGIN
OPEN UPDATE_NEXT_SEQ
FETCH UPDATE_NEXT_SEQ INTO NEXT_SEQ;
CLOSE UPDATE_NEXT_SEQ;
... and so on.
This gets the next value out of the sequence MY_SEQUENCE and stores it in a PL/SQL variable, which you can then use like any other. I prefer doing it this way to using CURRVAL all the time because this gives you the same results while using up only one database fetch.
-Pat Connors Received on Thu Apr 20 1995 - 00:00:00 CEST