Oracle After Delete Trigger... How to avoid Mutating Table (ORA-04091)?

Question

Let's say we have the following table structures:

documents      docmentStatusHistory      status
+---------+   +--------------------+    +----------+
| docId   |   | docStatusHistoryId |    | statusId |
+---------+   +--------------------+    +----------+
| ...     |   | docId              |    | ...      |
+---------+   | statusId           |    +----------+
              | ...                |
              +--------------------+

It may be obvious, but it's worth mentioning, that the current status of a document is the last Status History entered.

The system was slowly but surely degrading in performance and I suggested changing the above structure to:

documents           docmentStatusHistory      status
+--------------+   +--------------------+    +----------+
| docId        |   | docStatusHistoryId |    | statusId |
+--------------+   +--------------------+    +----------+
| currStatusId |   | docId              |    | ...      |
| ...          |   | statusId           |    +----------+
+--------------+   | ...                |
                   +--------------------+

This way we'd have the current status of a document right where it should be.

Because the way the legacy applications were built I could not change the code on legacy applications to update the current status on the document table.

In this case I had to open an exception to my rule to avoid triggers at all costs, simply because I don't have access to the legacy applications code.

I created a trigger that updates the current status of a document every time a new status is added to the status history, and it works like a charm.

However, in an obscure and rarely used situation there is a need to DELETE the last status history, instead of simply adding a new one. So, I created the following trigger:

create or replace trigger trgD_History
 after delete on documentStatusHistory
 for each row
 currentStatusId number;
begin

  select statusId
    into currentStatusId
    from documentStatusHistory
   where docStatusHistoryId = (select max(docStatusHistoryId)
                                 from documentStatusHistory
                                where docId = :old.docId);

  update documentos
     set currStatusId = currentStatusId
   where docId = :old.docId;
end;

And thats where I got the infamous error ORA-04091.

I understand WHY I'm getting this error, even though I configured the trigger as an AFTER trigger.

The thing is that I can't see a way around this error. I have searched the net for a while and couldn't find anything helpful so far.

In time, we're using Oracle 9i.

Answer 1

The standard workaround to a mutating table error is to create

• A package with a collection of keys (i.e. docId's in this case). A temporary table would also work
• A before statement trigger that initializes the collection
• A row-level trigger that populates the collection with each docId that has changed
• An after statement trigger that iterates over the collection and does the actual UPDATE

So something like

CREATE OR REPLACE PACKAGE pkg_document_status
AS
  TYPE typ_changed_docids IS TABLE OF documentos.docId%type;
  changed_docids typ_changed_docids := new typ_changed_docids ();

  <<other methods>>
END;

CREATE OR REPLACE TRIGGER trg_init_collection
  BEFORE DELETE ON documentStatusHistory
BEGIN
  pkg_document_status.changed_docids.delete();
END;

CREATE OR REPLACE TRIGGER trg_populate_collection
  BEFORE DELETE ON documentStatusHistory
  FOR EACH ROW
BEGIN
  pkg_document_status.changed_docids.extend();
  pkg_document_status.changed_docids( pkg_document_status.changed_docids.count() ) := :old.docId;
END;

CREATE OR REPLACE TRIGGER trg_use_collection
  AFTER DELETE ON documentStatusHistory
BEGIN
  FOR i IN 1 .. pkg_document_status.changed_docids.count()
  LOOP
    <<fix the current status for pkg_document_status.changed_docids(i) >>
  END LOOP;
  pkg_document_status.changed_docids.delete();
END;

Answer 2

seems to be a duplicate of this question

check out Tom Kyte's take on that