Re: ORA-04098: trigger name is invalid and failed re-validation

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/07/04
Message-ID: <8jst77$fmb$1@nnrp1.deja.com>#1/1

In article <39618b3e.88772327_at_news.dia.dk>, claus_at_js-edb.dk (Claus Nielsen) wrote:
> I am unable to use any kind of trigger I make. Not that I have ever
> succeded at getting any trigger to work, as I am new at this. But I
> have created the following trigger - just as an example:
>
> CREATE TRIGGER TRIGGER1
> BEFORE INSERT
> ON TABLE1
> FOR EACH ROW
> BEGIN
> NEW.ID := 3;
> END;
>
> The real trigger will eventually call a sequence which will return a
> uniqe number. But even with this simple trigger, every attempt at
> inserting a record in my table, results in the following error:
>
> ORA-04098: trigger name is invalid and failed re-validation
>
> The Oracle Documentation gives this explanation:
>
> Cause: A trigger was attempted to be retrieved for execution and was
> found to be invalid. This also means that compilation/authorization
> failed for the trigger.
>
> Action: The options are to resolve the compilation/authorization
> errors, disable the trigger, or drop the trigger.
>
> Unfortunately I can't figure where to go from here? What should I do?
> Any solutions/suggestions are welcome! Thanks.
>
> Regards, Claus Nielsen
>

problem is you need to use :new.id, not new.id

Here is how to find the errors:

ops$tkyte_at_8i> create table table1 ( id int );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> CREATE or replace TRIGGER TRIGGER1   2 BEFORE INSERT
  3 ON TABLE1
  4 FOR EACH ROW
  5 BEGIN
  6 NEW.ID := 3;
  7 END;
  8 /

Warning: Trigger created with compilation errors.

ops$tkyte_at_8i> show errors trigger trigger1 Errors for TRIGGER TRIGGER1:

LINE/COL ERROR

-------- ---------------------------------------------------------------
--
2/3      PLS-00201: identifier 'NEW.ID' must be declared
2/3      PL/SQL: Statement ignored

ops$tkyte_at_8i>
ops$tkyte_at_8i> CREATE or replace TRIGGER TRIGGER1   2 BEFORE INSERT
  3 ON TABLE1
  4 FOR EACH ROW
  5 BEGIN
  6 :NEW.ID := 3;
  7 END;
  8 /

Trigger created.

ops$tkyte_at_8i>

So, it told us the error was around new.id...

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Jul 04 2000 - 00:00:00 CDT