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

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

  1. #1
    Join Date
    Mar 2005
    Posts
    143

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

    We have a table ptlog with the following fields Patient, Date, User, StationId, and ModuleID. It is a supplied table for the EHR application we use. It is ment to monitor who accesses a patient's chart. However the flaw in the applications logic is that once it writes a row in the table for the day it doesn't insert to the table again for the rest of the day if the same user accesses the chart from the same station and goes to the same module unless that row was deleted. So if a user looks at a patient's medical history 10 times in a day the log only shows they looked at it once. I need to have better auditing. I created a table called ptlog_audit. It has the same fields plus a sysdate field. I want to have a trigger write to the ptlog_audit table everytime it inserts into the ptlog table and delete the row from the ptlog table so if the user looks at the chart again it doesn't have a record of it and tries to insert it again. I wrote this trigger
    Code:
    CREATE OR REPLACE TRIGGER AXIUM."PTLOG" 
      AFTER INSERT 
       ON AXIUM.PTLOG
       REFERENCING OLD AS OLD NEW AS NEW
        FOR EACH ROW
    BEGIN
              INSERT INTO PTLOG_AUDIT VALUES
         (:new."Patient",
         :new."Date",
          :new."User",
          :new."StationId",
          :new."ModuleID",
          sysdate);
          delete from ptlog where "Patient"=:new."Patient" and "Date"=:new."Date" and "User"=:new."User" and "StationId"=:new."StationId" and "ModuleID"=:new."ModuleID";
    END;
    /
    however when in the application it gives the following error:
    Error - SQL execution error, ORA-04091: table AXIUM.PTLOG is mutating, trigger/function may not see it
    ORA-06512: at "AXIUM.PTLOG", line 9
    ORA-04088: error during execution of trigger 'AXIUM.PTLOG'

    I changed the trigger to fire before insert and then it just hangs when inserting into ptlog.

    Can someone help me in trying to accomplish this. Any help would be greatly appreciated.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Oracle is right firing the error - the table is mutating e.g. application inserts a row and trigger deletes it immediatelly. I have to say I never seen before such a logic.

    If for some reason this is really needed I would replace PTLOG table for a temporary table and take out of the trigger the delete statement. This way PTLOG will be created on-the-fly for every transaction and just dissapear in thin air when the transaction completes.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2005
    Posts
    143
    Pavb,
    thank you for the reply. I hadn't thought about making ptlog a temp table, my concern with that is it is a table used by the application and I am not sure the ramifications of changing it to a temp table. I came up with two options that will be presented to my boss and she can decide what to do.
    option 1
    is the trigger writing to the ptlog_audit table and a script running every minute on the server deleting from the ptlog table. the thing I don't like about this option is it is relying on a script so it is sloppy and the script running every minute has the potential that an audit could be missed. Here is the code for the trigger
    Code:
    CREATE OR REPLACE TRIGGER AXIUM."PTLOG" 
      AFTER INSERT 
       ON AXIUM.PTLOG
       REFERENCING OLD AS OLD NEW AS NEW
        FOR EACH ROW
    BEGIN
              INSERT INTO PTLOG_AUDIT VALUES
         (:new."Patient",
         :new."Date",
          :new."User",
          :new."StationId",
          :new."ModuleID",
          sysdate);
          
    END;
    /
    option 2 is a trigger that manipulates the data of the "Date" field in the ptlog table putting sysdate in rather than just the date. I like this option because it is cleaner (not relying on a script) and you won't miss anything. my concern with this option is that I don't know if the application will have a problem with sysdate value being in the field. I will have to reach out to the application developers if this is the way my boss wants to go.
    Code:
    CREATE OR REPLACE TRIGGER AXIUM."PTLOG" 
      BEFORE INSERT 
       ON AXIUM.PTLOG
       REFERENCING OLD AS OLD NEW AS NEW
        FOR EACH ROW
    BEGIN
             :new."Date":=sysdate;
          
    END;
    /
    I feel option 2 is the best bet.

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