[Q] Database Trigger mutating error...

I have what seems to be a simple problem:

  I would like to create a database trigger which insert a record to a historical table and delete the current record. It goes something like this:

    CREATE OR REPLACE TRIGGER CLOSE_WORK_ORDER     AFTER UPDATE OF STATUS ON WORK_RECORDS     FOR EACH ROW WHEN (NEW.STATUS = 'C')     BEGIN

     /* Make sure a timestamp is present. */
     INSERT INTO WORK_HISTORY
     VALUES(:new.workOrderNo, :new.workOrderId, :new.status,
     ...

   
     DELETE FROM WORK_RECORDS WHERE WORKORDERNO = :NEW.WORKORDERNO;

     END;

This however causes a mutating error (ORA-04091). This seems like a common thing to do (move a record from working to history). Do I need to do this programmatically or is there a way to handle this in a database trigger?

Thanks in advance,
Jacquie Received on Tue Aug 01 1995 - 00:00:00 CEST