5

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.

skaffman
  • 398,947
  • 96
  • 818
  • 769
Paulo Santos
  • 11,285
  • 4
  • 39
  • 65

2 Answers2

9

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;
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I had to make some tweaks to make it work but the principle was the same. I had to initialize the collection in the package, otherwise it would give an error in the `trg_init_collection`; remove the `+1` from the `.count()+1` in the `trg_populate_collection` because it gave me the `SUBSCRIPT_OUTSIDE_LIMIT` error. – Paulo Santos Apr 27 '11 at 15:34
  • The standard workaround predates Oracle's implementation of Global Temporary Tables; I remember using that workaround in 7.3. – Adam Musch Apr 27 '11 at 20:56
  • How would I have to proceed if I want to `UPDATE`, not `DELETE` and add a lock on specific rows in the table? – Radu Gheorghiu Oct 16 '15 at 12:54
  • @RaduGheorghiu - Presumably, your `after delete` statement-level trigger would iterate over the collection of elements that were updated and implement whatever business logic you want (I believe you want to expire the previous row if you're the person I'm thinking of). So you'd iterate over the collection, updating any rows that overlap with the new rows. I'm not sure what you mean by "add a lock on specific rows" in this context. – Justin Cave Oct 16 '15 at 13:02
  • @JustinCave Yes, I think [I am the person you're thinking of](http://stackoverflow.com/questions/33169116/creating-an-after-trigger-in-oracle-to-access-new-and-old-variables). I have made some small additions to the question you've closed, where I forgot to mention I was also interested in locking some rows. – Radu Gheorghiu Oct 16 '15 at 13:04
1

seems to be a duplicate of this question

check out Tom Kyte's take on that

Community
  • 1
  • 1
HAL 9000
  • 3,877
  • 1
  • 23
  • 29