Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with Trigger
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