Re: SEQUENCE in a TRIGGER

From: Matt Marrow <marrowm_at_logica.com>
Date: 1996/02/23
Message-ID: <4gk1vs$8h5_at_romeo.logica.co.uk>#1/1

The problem is the syntax, not the sequence. Instead of the trigger you specified, try the following:

CREATE TRIGGER TRIG_NEXT_KEY_TRANSACTION

	BEFORE INSERT ON TRANSACTION
	FOR EACH ROW
BEGIN
	SELECT next_key_gen.nextval INTO :NEW.P_KEY
	FROM DUAL;

END; I tried this on a test system here and it worked just fine.

I hope this helps

Matt

marrowm_at_logica.com

All views expressed are my own and do not necessarily represent those of my employer.

In article <4gidcd$l2f_at_thor.atcon.com>,

   nathalie_at_MAIL.ATCON.COM (Nathalie) wrote:
>I created a SEQUENCE:
>
>CREATE SEQUENCE NEXT_KEY_GEN INCREMENT BY 1 START BY 1;
>
>
>and then a TRIGGER for my table TRANSACTION:
>
>CREATE TRIGGER TRIG_NEXT_KEY_TRANSACTION
> BEFORE INSERT ON TRANSACTION
> FOR EACH ROW
>BEGIN
> :NEW.P_KEY := next_key_gen.nextval;
>END;
>
>
>and I get the error message "PLS-00357: Table, view or sequence
>reference 'NEXT_KEY_GEN' not allowed in this context"
>
>Does that mean that I can't use a sequence generator in a trigger? It
>would be quite useful, wouldn't it? I'd like to use it because I
>wouldn't have to worry about primary keys from the Powerbuilder end of
>it.
>
>Thanks
>
>Nathalie
>nathalie_at_atcon.com
>
>DeltaWare Systems Inc.
>Prince Edward Island, Canada
>
>
Received on Fri Feb 23 1996 - 00:00:00 CET