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

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

I got “ORA-04098: trigger is invalid and failed re-validation ” error in Oracle database.

Details of error are as follows.

1 2 3 4 5 6 7

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.

trigger “string.string” is invalid and failed re-validation

This ORA-04098 errors are related with the trigger was attempted to be retrieved for execution and was found to be invalid.

This also means that compilation/authorization failed for the trigger.Options are to resolve the compilation/authorization errors.

To solve this error, disable the trigger, or drop the trigger.

There two alternatives to resolve the compilation/authorization errors other than fixing the actual problem

* disable the trigger

* drop the trigger.

The following are individual options that may resolve the problem and do not need to be tried in sequence.

1) Check the syntax of the trigger. Verify the (;) are where necessary and all quotes and parentheses are matched.

2) If the syntax is correct, ensure necessary table privileges have been granted.

To check for system level privileges, do the following:

As a dba:

1	select * from dba_sys_privs where grantee = 'username';

As user:

1	select * from user_sys_privs;

To check for table level privileges, do the following:

As a dba:

1	select * from dba_tab_privs where grantee = 'username' and table_name = 'tablename';

As user:

1	select * from user_tab_privs where table_name = 'tablename';

3) Recompile the trigger.

1	alter trigger <trigger name> compile;

If there are errors:

1	show errors trigger <trigger name>;

If the trigger or table is owned by another user, or they are creating a trigger in another users’s schema, show errors will not show the errors.

Instead, do the following:

1	select * from DBA_ERRORS where owner = 'owner of the trigger';

4) Verify that the trigger is enabled.

As a dba:

1	select trigger_name, status from dba_triggers where owner = 'owner name' and trigger_name = 'trigger name'

As owner of the trigger:

1	select trigger_name, status from users_triggers where trigger_name = 'trigger name':

You can run the SHOW ERRORS command to see the errors.

1	SHOW ERRORS TRIGGER triggername;