ORA-04091: table is mutating, trigger/function may not see it error
I am trying to update a field in a table using a trigger that gets executed when 2 other fields in that same table get updated. Here is my situation. I have a table called PATIENT. it has a lot of fields. I am only interested in a few. There is a field called Office4 and it has a value of BAL. there are 2 other fileds I am interested in LastPmtDate and LastPaymentAmount. When those 2 fields get updated I want to update the Office4 field and remove the BAL value. After some trial and error Here is where I am at. I created a table called BALTMP. this is going to be a temp staging table. I have a trigger that inserts into that new table when the LastPmtDate and LastPaymentAmount fileds get updated. here is that trigger:
CREATE OR REPLACE TRIGGER AXIUM."PATIENTBALTRG"
before update of
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
INSERT INTO BALTMP VALUES (
this trigger works fine. when those fileds get updated the trigger fires and inserts the data into the BALTMP table. then I created another trigger that that fires when a row gets inserted into the BALTMP table to remove the BAL value from the PATIENT table, and that is where I get the table mutating error. Here is the second trigger:
CREATE OR REPLACE TRIGGER AXIUM."RMVPATLCKTRG"
REFERENCING NEW AS NEW
FOR EACH ROW
UPDATE PATIENT SET "Office4"=' ' where "Patient"= :new."PATIENT" and "Office4"='BAL';
what I don't understand is the trigger that gets executed on the PATIENT table only happens when the LastPmtDate, LastPaymentAmount fields get updated and my second trigger is only updating the Office4 field, so it shouldn't effect the trigger. Can anyone please help me out and guide me on how to accomplish my end result. thank you.