Re: Generating unique key numbers

On 29 Nov 97 15:39:10 GMT, "Michal Motalík" <cross_at_zl.inext.cz> wrote:

>I am developing a client/server project which will run on both 
>Interbase and Oracle. I wrote a trigger which sets unique numbers 
>from generator to primary index column on Interbase. I want to 
>write similar trigger on Oracle but i have a problem.
>
>I can not create trigger:
>
>CREATE TRIGGER tabtrig
>BEFORE INSERT ON tab
>FOR EACH ROW
>BEGIN
>  :NEW.id := tabseq.NEXTVAL;
>END;
>
>The sequence "tabseq" was created.
>I get the error PLS-00357.
>(reference to sequence is not permitted in these context)
>
>Does anybody know where the problem is?
>                   
>Michal Motalík
>cross_at_zl.inext.cz

You have to select the next sequence value in DML...you can't get it from a straight assignment statement. I usually just use a cursor to get the value, as follows:

CREATE TRIGGER tabtrig
BEFORE INSERT ON tab
FOR EACH ROW
DECLARE
   cursor c1 is select tabseq.nextval from dual;    seqval number;
BEGIN
   open c1;
   fetch c1 into seqval;
   close c1;
  :NEW.id := seqval;
END; Regards,
Dave Macpherson Received on Mon Dec 01 1997 - 00:00:00 CST