Re: Need assist w/ BEFORE INSERT trigger

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 15 May 2004 16:05:24 +1000
Message-ID: <40a5b317$0$3036$afc38c87@news.optusnet.com.au>

Martin Burbridge wrote:

> Ed Stevens <nospam_at_noway.nohow> wrote in message news:<oq0aa0hgbrsdhido89vo6p0i85igk31mu7_at_4ax.com>...
> 

>>Here's what I have:
>>
>>CREATE OR REPLACE TRIGGER TRG_MSGRTR_FORCE_PK
>>BEFORE INSERT
>>ON MESSAGE_ROUTER
>>REFERENCING OLD AS OLD NEW AS NEW
>>FOR EACH ROW
>>begin
>>:NEW.MSGRTE_MSG_SEQNBR := SQ_MSG_SLNO.NEXTVAL;
>>END;
>>/
>>
>>which yeilds this error
>>
>>PLS-00357: Table, view or sequence reference 'SQ_MSG_SLNO.NEXTVAL' not
>>allowed in this context.
>>

> 
> 
> Good idea, you just need to wrap the sequence in a select e.g.
> 
> select sq_msg_slno.nextval 
> into :new.msgrte_msg_seqnbr
> from dual;
> 
> On a side note why is the redundant line referencing old as old
> ... etc so common, is it from the docs?

I didn't see the original post, but if this is about how you get automatically-assigned incrementing sequence numbers, see posts passim in the reuse of index blocks thread here, or visit www.dizwell.com and check out the Basic Admin FAQ's first few questions.

Sorry if it's not relevant, either. Feel free to ignore.

Regards
HJR Received on Sat May 15 2004 - 01:05:24 CDT