Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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
![]() |
![]() |