Problem with Trigger

From: Sebastian Scholz <sebastian_scholz_at_tuwien.ac.at>
Date: Sun, 17 Dec 2000 21:48:37 +0100
Message-ID: <91j8ri$4fe$1@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 - 14:48:37 CST