Removal of mutating trigger

Question

I am trying to create a trigger which counts the date of a service and the ID of a branch. The objective of the trigger is to count the number of branch ID as well as the date of service. If the count is greater than 3 then the trigger should come in effect. The issue that I am having however is that oracle has identified that it is a mutating trigger and does not allow the insertion of the data with the error message:

ORA-04091: table user1.SERVICEIH is mutating, trigger/function may not see it
ORA-06512: at "user1.SERVICE_CHECKIH", line 5
ORA-04088: error during execution of trigger 'user1.SERVICE_CHECKIH'

Answer 1

You are misusing the trigger. I mean wrong trigger used.

1. you insert / update a row in table A
2. a trigger on table A (for each row) executes a query on table A

Oracle throws an ORA-04091 which is an expected and normal behavior, Oracle wants to protect you from yourself since it guarantees that each statement is atomic (i.e will either fail or succeed completely) and also that each statement sees a consistent view of the data

You would expect the query (2) not to see the row inserted on (1). This would be in contradiction

Solution: -- use before instead of after

CREATE OR REPLACE TRIGGER SERVICE_CHECKih
BEFORE INSERT OR UPDATE ON SERVICEih