Re: Problem with Trigger

Hi,
The :new variables are read-only in an after trigger. The best way to do it would be in a before insert or update trigger.

CREATE OR REPLACE TRIGGER "CUSTOMERDATA"."SETTWO" BEFORE INSERT OR UPDATE OF "CODE" ON "CUSTOMERDATA"."CTBR" FOR EACH ROW
BEGIN
    :new.FirstTwoCharacter := SUBSTR(:new.CODE, 1,2); END; HTH
-- Ranga Chakravarthi

"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 Jan 28 2001 - 21:47:40 CST