Skip to Main Content
 
Titelbild Muniqsoft Training

Search Results

Oracle Tipp Text

Mutating Table Problem umgehen mittels zwei Trigger und einer Collection

Bereich:PL/SQL, Version: ab RDBMS 10.x, Letzte Überarbeitung: 29.06.2018

Keywords:Mutating Table Problem, Trigger, Collection

In unseren Schulungen klagen die Teilnehmer häufig über das Mutating Table Problem.
Man möchte ja nur mal in der Tabelle etwas nachsehen, auf der der Trigger liegt.

Nur leider quittiert der Trigger einen DML Aufruf mit der folgenden Fehlermeldung:

ORA-04091: table XXXX is mutating, trigger/function may not see it.

Lassen Sie uns als Beispiel folgende Problemstellung lösen:

In der klassischen EMP Tabelle wird eine Spalte hinzugefügt mit dem Namen job_chef. In die soll, wenn die Spalte mgr gefüllt wurde, auch automatisch der Beruf des Vorgesetzten eingetragen werden. (Für alle, die die Tabelle nicht so gut kennen, sei gesagt, dass in der Spalte mgr die Nummer des Vorgesetzten steht)
Man müsste nun eigentlich nur mal kurz nachsehen, was für einen Beruf der Chef hat, aber da kommt uns das Mutating Table Problem in die Quere.

Man kann das Problem jetzt über 2 Lösungsansätze angehen.

Variante 1:

Wir legen uns erstmal eine Spalte mit Namen job_chef an:

ALTER TABLE emp ADD (job_chef VARCHAR2(30));

Dann erstellen wir uns ein Package, dass die komplette! Tabelle in ein Array schreibt. (Vorsicht, bei grossen Tabellen, kann der Hauptspeicher zur neige gehen)

CREATE OR REPLACE PACKAGE plsql_tab IS
TYPE tab_type IS TABLE OF scott.emp%ROWTYPE
INDEX BY BINARY_INTEGER;
p_tab tab_type;
PROCEDURE getptab;
END;
/
CREATE OR REPLACE PACKAGE BODY plsql_tab IS
PROCEDURE getptab  IS
BEGIN
   SELECT * BULK COLLECT INTO p_tab FROM scott.emp;
END getptab;
END plsql_tab;
/

Dann brauchen wir einen Statement Trigger, denn der hat das Mutating Table Problem nicht. Der lädt die Daten in eine Collection in den Speicher.

CREATE OR REPLACE TRIGGER emp_trig
BEFORE INSERT OR UPDATE ON emp
DECLARE
BEGIN
plsql_tab.getptab;
END;
/

Und nun kommt der eigentliche Trigger ins Spiel. Der braucht jetzt nicht die eigene Tabelle befragen, sondern holt sich die Daten aus dem Speicher.

CREATE OR REPLACE TRIGGER emp_trg_row
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
DECLARE
BEGIN
FOR i IN 1 .. plsql_tab.p_tab.count LOOP
    IF plsql_tab.p_tab(i).empno=:new.mgr THEN
        :new.job_chef:=plsql_tab.p_tab(i).job;
    END IF;
END LOOP;
END;
/

 

Test:
update emp set mgr=7902 where empno=7369;
SELECT ename,mgr,job_chef from emp where empno=7369;
SMITH    7902    ANALYST

 

Variante 2:

Das ist jetzt ein bisschen gefährlich, denn wir tricksen das Mutating Table Problem, mit dem Compiler Hinweiß:
PRAGMA AUTONOMOUS_TRANSACTION aus. Hier wird für den Trigger eine eigene Session geöffnet, die dann das
Mutating Table Problem nicht hat. Wenn sich aber die Jobbezeichnung des Chefs während des Updates auch ändert, erwischt man u.U. den falschen Wert.

CREATE OR REPLACE TRIGGER emp_trg
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT job INTO :new.job_chef
FROM emp
where empno=:new.mgr;
END;
/

 

Weitere Tipps und Tricks erhalten Sie z.B. in unserem PL/SQL Kurs oder PL/SQL II Kurs



Weitere Interessente Artikel zum Thema:



Empfohlene Schulungen zum Thema:
2026.02.18