Re: Need assist w/ BEFORE INSERT trigger

"Ed Stevens" <nospam_at_noway.nohow> wrote in message news:oq0aa0hgbrsdhido89vo6p0i85igk31mu7_at_4ax.com...
> Oracle 8.1.7
>
> We have several apps, each with its own db, that insert rows into a
> table on a central db. Call this table MSG_ROUTER. These rows are
> picked up by a process that translates them to e-mails. Each row has
> to have a unique id. So a sequence was defined in the DB hosting
> MSG_ROUTER, and the apps get the next key value from that sequence.
>
> I was just made aware of this when a developer came to me and couldn't
> get her insert statement to work. After hearing that several apps,
> from several db's were inserting into this one table and jumping hoops
> to keep a sequence generated PK, I proposed we simply create a before
> insert trigger so the db could take care of the PK and get the apps
> out of that business. So of course it fell to me to write the
> trigger, which I've only done once before, and that over a year ago,
> after which that project was tossed out.
>
> 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.

How about:
 CREATE OR REPLACE TRIGGER TRG_MSGRTR_FORCE_PK  BEFORE INSERT
 ON MESSAGE_ROUTER
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW
declare seqNumber number;
 begin
select nvl(:NEW.MSGRTE_MSG_SEQNBR,SQ_MSG_SLNO.NEXTVAL) into seqNumber from dual;
 :NEW.MSGRTE_MSG_SEQNBR := seqNumber;
 END;
 /

Then if they specify an ID you won't generate one, and if they don't you will.
Jim Received on Fri May 14 2004 - 20:23:12 CDT