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.