Re: Trigger / Table mutating

From: Dr. Jan Dieckmann <JDieckmann_at_psi.de>
Date: Wed, 04 Nov 1998 17:16:22 +0100
Message-ID: <36407DD6.D4568E6A@psi.de>

Hi Stefan,

the problem:
The Row Trigger is unable to do what the trigger should do because of the "mutating error message". A moment later it would be possible.

Another solution:
Let the Row Trigger only store the RowId's of the rows for which the trigger should fire in a package. Define a Statement Trigger which uses the stored rowid's to do what the Row Trigger could not do.

Here is an example. I hope it helps. (If you are looking for a good environment to create PL/SQL packages and Trigger try our tool Hora 3. See ftp:\\keeptool.com for a free trial version of Hora

/*

---

*/
/*
Triggers
*/
/*

---

*/

CREATE OR REPLACE TRIGGER MyTable_RTR_A AFTER INSERT OR UPDATE ON MyTable
FOR EACH ROW
BEGIN
  TrMutate.SaveMyTable (:new.ROWID);
END;
/

CREATE OR REPLACE TRIGGER MyTable_STR_A AFTER INSERT OR UPDATE ON MyTable
BEGIN
  TrMutate.MyTable_STR_A;
END;
/

/*

---

*/
/*
Packages
*/
/*

---

*/

CREATE OR REPLACE
PACKAGE TrMutate AS

PROCEDURE SaveMyTable (Pi_RowId IN ROWID); PROCEDURE MyTable_STR_A;

END TrMutate;

CREATE OR REPLACE
PACKAGE BODY TrMutate AS

TYPE typTabRowId IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

tMyTableTab typTabRowId;

PROCEDURE SaveMyTable (Pi_RowId IN ROWID) IS
  vIdx BINARY_INTEGER;
BEGIN
  vIdx := NVL (tMyTableTab.LAST, 0) + 1;   tMyTableTab (vIdx) := Pi_RowId;
END; PROCEDURE MyTable_STR_A
IS
  vIdx BINARY_INTEGER;
  vRowId ROWID;
BEGIN
  vIdx := tMyTableTab.FIRST;
  WHILE vIdx IS NOT NULL LOOP
    vRowId := tMyTableTab (vIdx);

• ... Action of your row trigger END LOOP;

  tMyTableTab.DELETE;
END; END TrMutate;

Stefan Kenel wrote:

> I created two tables, a parent and a child. The parent should at least
> have one child. So if I delete the last child, the parent has also to be
> deleted. For this I use a trigger:
>
> CREATE TRIGGER child_delete
> AFTER DELETE ON child
> FOR EACH ROW
> DECLARE
> NUMBER x;
> BEGIN
> SELECT Count(*) INTO x
> FROM child
> WHERE child.parent_id = :old.parent_id;
> IF x = 0 THEN
> DELETE FROM parent
> WHERE parent.parent_id = :old.parent_id;
> END IF;
> END;
>
> If I try to delete a row in the child-table, the following error message
> occurs:
>
> ORA-04091: table SCOTT.child is mutating, trigger/function may not see
> it
>
> The only solution in the docs is to use a copy of the child-table and to
> alter this by the row-trigger.
> After the execution of the delete-statement, a table-trigger has to
> write the child-copy into the child-original.
>
> But I can't believe that this is the best solution!
>
> Any suggestions or explanations are welcome.
>
> Thanks
>
> Stefan
Received on Wed Nov 04 1998 - 10:16:22 CST