ORA-04098: trigger "string.string"
is invalid and failed re-validation
- 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.
Many users find that when using Oracle Human Resources version
11.5.9, ORA-04098 is thrown in conjunction with Frm-40735.
MOSC has found that this ORA-04098 is characterized by receiving FRM-40735 and ORA-04098
after trying to save data on an organization.
To resolve ORA-04098, try to first figure out which triggers are
invalid by running the query. For example:
select object_name
from dba_objects
where object_type = 'TRIGGER'
and status = 'INVALID';
For the example above, we can see that there appears to be two
invalid triggers:
- CAIBR_HR_ALL_ORG_T1
- CAIBR_HR_ALL_ORG_T2
Then, you can disable the triggers to fix ORA-04098.
Here is another example of ORA-04098:
Many users fin themselves encountering
ORA-04098 when they are trying to execute a trigger using a mixed
case with failed naming.
MOSC also offers great notes on this issue with ORA-04098 in
which ORA-04098 is thrown with a trigger when it is being executed
after being recompiled. For example:
update emp set ename =
'miller1' where empno = 7934
*
ERROR at line 1:
ORA-04098: trigger 'test' is invalid and failed re-validation
What has happened is ORA-04098 is
thrown after the customer has tried a "triggername" so that they can
have both an upper and lower case version of the trigger. The
lowercase, however, become invalid because the the lowercase trigger
isn't terminated with a ; in a trigger test. This being, the
uppercase trigger test is not throwing the ORA-04098 error, but the
lower is.
To resolve ORA-04098, you should
try to only keep one trigger and not the other. You can drop
the unecesary trigger by adding double quotes. Her is an
example of this method of resolveing ORA-04098:
Run as scott/tiger.
SQL> create or replace trigger "SCOTT"."TEST"
2 after update of "ENAME"
3 on emp
4 begin
5 update emp set job='job2';
6 end;
7 /
Trigger created.
SQL> create or replace trigger "SCOTT"."test"
2 after update of "ENAME"
3 on emp
4 begin
5 update emp set job='job2'
6 end;
7 /
Warning: Trigger created with compilation errors.
SQL> update emp set ename = 'miller1' where empno = 7934;
update emp set ename = 'miller1' where empno = 7934
*
ERROR at line 1:
ORA-04098: trigger 'test' is invalid and failed re-validation
SQL> drop trigger "test";
Trigger dropped.
SQL> update emp set ename = 'miller1' where empno = 7934;
1 row updated.
