Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with Trigger

Re: Problem with Trigger

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 17 Dec 2000 22:33:45 +0100
Message-ID: <91jc2q$43km5$2@ID-62141.news.dfncis.de>

CREATE OR REPLACE TRIGGER "CUSTOMERDATA"."SETTWO" before INSERT OR UPDATE OF "CODE" ON "CUSTOMERDATA"."CTBR"  BEGIN
    :new.FirstTwoCharacter=LEFT(:new.CODE, 2) ; END; Any manipulation like this needs to be addressed on the BEFORE event, not on AFTER.
This is discussed in the application developers guide.

BTW: please do not crosspost, and definitely do not crosspost to groups not all providers transmit.

Regards,

Sybrand Bakker, Oracle DBA

"Sebastian Scholz" <sebastian_scholz_at_tuwien.ac.at> wrote in message news:91j8ri$4fe$1_at_news.tuwien.ac.at...
> Hello,
>
> I'm new to Oracle and need some help with a trigger. I need one which
 places
> the first two character of one field of a Table into a seperate one.
>
> Columns of the Table: (called CustomerData.CTBR)
>
> Code (6 Character)
> FirstTwoCharacter (2 Character)
> CTBROI (Primary Key, Number)
>
> Whenever I enter some data into the Field "Code", the trigger should fill
 in
> the first two Characters of this Code in the Field "FirstTwoCharacter".
> I did it with this Trigger:
>
> CREATE OR REPLACE TRIGGER "CUSTOMERDATA"."SETTWO"
> AFTER INSERT OR UPDATE OF "CODE" ON "CUSTOMERDATA"."CTBR"
> BEGIN
> UPDATE CUSTOMERDATA.CTBR SET FirstTwoCharacter=LEFT(CODE, 2) WHERE
> CTBROI = :new.CTBROI
> END;
>
> The Problem is, when I insert data with SQLPlus, it generates an error:
> INSERT INTO CUSTOMERDATA.CTBR (CODE, CTBROI) VALUES (998877, 9);
> ORA-04098: Trigger 'SETTWO' is invalid.
>
> Why can't I use the row :new directly? (just say, :new.FirstTwoCharacter =
> LEFT(:new.Code, 2) )
>
> Thanks in Advance,
> Sebastian
>
>
>
>
>
>
>
>
Received on Sun Dec 17 2000 - 15:33:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US