DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ORA-04091: table is mutating, trigger/function may not see it error

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  3. #3
    Join Date
    Mar 2005
    Posts
    143
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  5. #5
    Join Date
    Mar 2005
    Posts
    143
    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
    Last edited by jayjabour; 02-16-2010 at 04:59 PM.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  7. #7
    Join Date
    Mar 2005
    Posts
    143
    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.

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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");

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width