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
however when in the application it gives the following error:
CREATE OR REPLACE TRIGGER AXIUM."PTLOG"
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
INSERT INTO PTLOG_AUDIT VALUES
delete from ptlog where "Patient"=:new."Patient" and "Date"=:new."Date" and "User"=:new."User" and "StationId"=:new."StationId" and "ModuleID"=:new."ModuleID";
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.