Oracle Trigger ORA-04098: trigger is invalid and failed re-validation

Question

I am trying to create a simple trigger in an oracle 10g database. This script to Create the trigger runs clean.

CREATE OR REPLACE TRIGGER newAlert AFTER INSERT OR UPDATE ON Alerts   BEGIN         INSERT INTO Users (userID, firstName, lastName, password) VALUES ('how', 'im', 'testing', 'this trigger')   END;            / 

But when I run:

INSERT INTO Alerts(observationID, dateSent, message, dateViewed) VALUES (3, CURRENT_TIMESTAMP, 'Alert: You have exceeded the Max Threshold', NULL); 

to activate the trigger, I get this error message:

ORA-04098: trigger 'JMD.NEWALERT' is invalid and failed re-validation (0 rows affected)

I don't understand whats causes this error. Do you know what causes this error? Or why this is happening?

Thank you in advance!

-David

Answer 1

Oracle will try to recompile invalid objects as they are referred to. Here the trigger is invalid, and every time you try to insert a row it will try to recompile the trigger, and fail, which leads to the ORA-04098 error.

You can select * from user_errors where type = 'TRIGGER' and name = 'NEWALERT' to see what error(s) the trigger actually gets and why it won't compile. In this case it appears you're missing a semicolon at the end of the insert line:

INSERT INTO Users (userID, firstName, lastName, password) VALUES ('how', 'im', 'testing', 'this trigger') 

So make it:

CREATE OR REPLACE TRIGGER newAlert AFTER INSERT OR UPDATE ON Alerts   BEGIN         INSERT INTO Users (userID, firstName, lastName, password)         VALUES ('how', 'im', 'testing', 'this trigger');   END;            / 

If you get a compilation warning when you do that you can do show errors if you're in SQL*Plus or SQL Developer, or query user_errors again.

Of course, this assumes your Users tables does have those column names, and they are all varchar2... but presumably you'll be doing something more interesting with the trigger really.

Answer 2

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: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.

Syntax

ALTER TRIGGER trigger_Name DISABLE;  ALTER TRIGGER trigger_Name ENABLE;