Es kommt immer wieder vor, dass bei einem DML-Trigger für Überprüfungen oder Protokollierungen Daten aus der zu ändernden Tabelle benötigt werden. Ein normaler Row Trigger scheidet dann in der Regel aus, weil man sonst in das Problem mit den Mutating Tables läuft.
Ein kleines Beispiel dazu: Stellen Sie sich vor, eine Geschäftsregel besagt, dass niemand innerhalb einer Berufsgruppe mehr als 50% über dem Durchschnittsgehalt verdienen darf. Der einfachste Ansatz dazu sähe folgendermaßen aus:
CREATE OR REPLACE TRIGGER check_sal
AFTER UPDATE of sal ON emp
FOR EACH ROW
DECLARE
v_avgsal NUMBER;
BEGIN
SELECT avg(sal)
INTO v_avgsal
FROM EMP
WHERE job = :NEW.job;
IF :NEW.sal > 1.5 * v_avgsal THEN
RAISE_APPLICATION_ERROR(-20000, 'Zu weit über Durchschnitt');
END IF;
END;
/
Das geht aber leider schief, sobald Sie ein Update ausführen:
UPDATE emp
SET sal = 1500
WHERE empno = 7934
Error at line 3
ORA-04091: Tabelle SCOTT.EMP wird gerade geändert, Trigger/Funktion sieht dies möglicherweise nicht
ORA-06512: in "SCOTT.CHECK_SAL", Zeile 4
ORA-04088: Fehler bei der Ausführung von Trigger 'SCOTT.CHECK_SAL'
Das Problem mit den Mutating Tables hat zugeschlagen! Ein Workaround dazu könnte so aussehen, dass Sie über einen BEFORE STATEMENT Trigger zuerst den Durchschnitt aller Berufsgruppen einlesen und beispielsweise in einem Package oder in einer Tabelle zwischenspeichern. Der Row Trigger kann dann auf diese Werte zugreifen, ohne die zu ändernde Tabelle lesen zu müssen. Das ist machbar, aber aufwendig.
Ab Version 11g kann die Lösung viel einfacher aussehen: Compound Trigger haben Abschnitte für bisher unterschiedliche Triggertypen und bieten die Möglichkeit, Werte für die Dauer der Durchführung des DML-Befehls zu speichern. Obiges Beispiel könnte mit einem Compound Trigger so aussehen:
CREATE OR REPLACE TRIGGER check_sal
FOR UPDATE OF sal ON SCOTT.EMP
COMPOUND TRIGGER
TYPE t_number IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE t_varchar IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
TYPE t_avgsal IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
v_avgsal t_number;
v_job t_varchar;
v_avgJob t_avgsal;
v_index VARCHAR2(30);
BEFORE STATEMENT IS
BEGIN
SELECT AVG(sal), job
BULK COLLECT INTO v_avgsal, v_job
FROM EMP e
GROUP BY e.job;
FOR i IN 1..v_avgsal.COUNT
LOOP
v_avgJob(v_job(i)) := v_avgsal(i);
END LOOP;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF v_avgJob.EXISTS(:NEW.job)
THEN
IF :NEW.sal > 1.5 * v_avgJob(:NEW.job)
THEN^
RAISE_APPLICATION_ERROR(-20000,
'Verdienst ist zu weit über Durchschnitt');
END IF;
END IF;
END AFTER EACH ROW;
END;
/
Erlaubter Update:
UPDATE emp
SET sal = 1500
WHERE empno = 7934
1 row updated.
Verbotener Update:
UPDATE emp
SET sal = 1800
WHERE empno = 7934
ORA-20000: Zu weit über Durchschnitt
ORA-06512: in "SCOTT.CHECK_SAL", Zeile 29
ORA-04088: Fehler bei der Ausführung von Trigger 'SCOTT.CHECK_SAL'
In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den
Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.
