Re: Problem with Trigger

Somebody named "Sebastian Scholz" <sebastian_scholz_at_tuwien.ac.at> wrote in 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
>

1st. You can use "new" and "old" _only_ for "For Each row" trigger 2nd You can't use "update" for the table in its trigger. 3rd. AFAIK, you can use "left" only for columns in SQL*Plus. Use Substr() here
So, IMHO, you need

 CREATE OR REPLACE TRIGGER "CUSTOMERDATA"."SETTWO"  AFTER INSERT OR UPDATE OF "CODE" ON "CUSTOMERDATA"."CTBR" FOR EACH ROW
BEGIN
  :new.FirstTwoCharacter=Substr(:new.CODE, 1,2) ;  END; BR,
  Eugene Received on Mon Dec 18 2000 - 04:59:27 CST