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