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
"LastPmtDate",
"LastPaymentAmount"
on
"PATIENT"
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO BALTMP VALUES (
:new."Patient",
:new."LastPmtDate",
:new."LastPaymentAmount");
END;
/

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"
AFTER INSERT
ON AXIUM.BALTMP
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
UPDATE PATIENT SET "Office4"=' ' where "Patient"= :new."PATIENT" and "Office4"='BAL';
END;
/

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.

If you are using a temporary table to get around the mutating table error, then you need the row level trigger to insert rows into the temp table, and you need a statement level trigger to update the other row in the table. You have two row level triggers which each fire independently and in no particular order. Even though they fire on different columns.

Gandolf989,
thanks for the responce. I got it to do what I wanted and with just one trigger and no other tables. I am using the following trigger

CREATE OR REPLACE TRIGGER AXIUM."RMVBALTRG"
BEFORE UPDATE OF
"LastPaymentAmount"
ON "PATIENT"
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
IF (:NEW."LastPaymentAmount" > 0) THEN
:NEW."Office4" := ' ';
END IF;
END;
/

Now when someone makes a payment in the system thus updating the LastPaymentAmount field in the PATIENT table the trigger fires and applies the new payment as well as removes the BAL from the Office4 field. I am not sure what kind of trigger this is called, I thought it is a row level trigger, and I am not sure why it works and doesn't give me the mutating trigger error, maybe because it is manipulating the data before it applies it? at least it works though. That buys me time to figure out why it works.

It works because you are updating the row that is changing and not some other row in the table. I did not realize at first that you were updating a column based on another column in the same row.

I had to make an adjustment to the trigger. they don't want the trigger to update the Office4 field if a payment was made, they want it to update the field if the entire balance was paid. The balance value is not stored in the Patient table. here is my new trigger:
CREATE OR REPLACE TRIGGER AXIUM."RMVBALTRG"
BEFORE UPDATE OF
"LastPaymentAmount"
ON "PATIENT"
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
v_bal NUMBER(10);
BEGIN
SELECT "AmountDue" into v_bal FROM PTBAL where "Patient"=:new."Patient" and "Batch"=(SELECT MAX("Batch") FROM ptbalbat);
IF (:NEW."LastPaymentAmount" = v_bal) THEN
:NEW."Office4" := ' ';
END IF;
END;
/

the trigger builds fine. if I run this statement through toad it executes the trigger and does what I want.
update PATIENT set "LastPmtDate"='10-FEB-2010' , "LastPaymentAmount"=735 where "Patient"=112826
this patient has a $735 balance so it updared the office4 field. however when I go through the application and apply a payment i get this error:

Error - SQL execution error, ORA-01403: no data found
ORA-06512: at "AXIUM.RMVBALTRG", line 4
ORA-04088: error during execution of trigger 'AXIUM.RMVBALTRG'

Last SQL: UPDATE PATIENT SET "LastPmtDate" = :1 , "LastPaymentAmount" = :2 WHERE PATIENT."...

Can anyone help me out here? Please

So if a select into returns no records you get the no data found error. You can put that piece of sql into its own function then catch the no data found error and when it gets thrown return a default value.

Gandolf,
thanks for the quick reply, i appreciate it.
when i run the
SELECT "AmountDue" into v_bal FROM PTBAL where "Patient"=:new."Patient" and "Batch"=(SELECT MAX("Batch") FROM ptbalbat);
by itself taking out the into and putting teh actual patient value in it returns a value of 735. so I am not sure why through the app it is not finding data.

I think you are missing part of the where clause.

Code:

---

SELECT "AmountDue"
  into v_bal
  FROM PTBAL
 where "Patient"=:new."Patient"
  and "Batch"=( SELECT MAX("Batch")
                  FROM ptbalbat
                  where "Patient"=:new."Patient");

---