I’m getting an error “ORA-04098: trigger Schema.Trigger is invalid”.

Scenario is: In my DB, there are 3 schemas, S0, S1 and S2

Tables, Indexes, Sequences etc. are created in schema S0 and packages, views, Triggers etc. are created in S1 and S2.

S1 and S2 have rights to perform DML on S0’s tables.

On update of Employees details trigger “Emp_Log_Change” will fire and the same trigger available in both the schema S1 and S2.

When both the triggers are in valid state then Oracle is allowing to perform DML(Update) on Employees and fire the trigger of same schema from where DML initiated.(e.g. If DML initiated by S1 then trigger of S1 gets fired.)

When one of the schema’s trigger is invalid (say trigger of S2 is invalid) and I’m performing DML using S1 then it’s throwing the error as mentioned above.

It’s a valid case that Oracle is validating both the triggers because they are created on S0.Employees

So, My question here is: Is there any way that we can instruct Oracle not to validate all the triggers on tables and stick with the validation of same schema trigger from where DML initiated?

Performed below steps in DB as below:

Logged in using S0:
Create table Employees(Id Number(10), Name Varchar2(30), DeptId Number(10));

Grant Delete Update Insert On S0.Employees to S1,S2 With Grant Option;

Logged in using S1:
Create Or Replace Trigger Emp_Log_Change After Update On S0.Employees
For Each Row
Begin Dbms_Output.Put_Line(Sys_Context(‘USERENV’, ‘CURRENT_SCHEMA’) || ‘ fired’);
End;

Logged in using S2:
Create Or Replace Trigger Emp_Log_Change After Update On S0.Employees
For Each Row
Begin Dbms_Output.Put_Line(Sys_Context(‘USERENV’, ‘CURRENT_SCHEMA’) || ‘ fired’);
End;

Executed below query using S1:
Insert Into Employees Values(1, ‘ABC’, 10);

Commit;

Update Employees Set DeptId = 20 Where Id = 1;

Output: S1 fired.

Commit;

Recompile trigger of S2 without semicolon (Just to make it invalid): Removed ; from DBMS… statement.

Executed below query using S1 again:
Update Employees Set DeptId = 10 Where Id = 1;

Getting error this time because trigger of S2 is in invalid state.

Thanks in advance 🙂
Thank you!