Das Oracle Mutating Table Problem tritt auf, wenn ein Datenbanktrigger eine Datensatz ändert, der bereits geändert wurde.
Hier ist ein Beispiel, das zeigt, wie elegant Hora’s Mutating Wizard das Problem des Mutating Table Error löst. Der Mutating Table Error entsteht z.B., wenn man versucht, ein Update auf die Aggregation in einer Mastertabelle durchzuführen, das auf DML-Befehlen in einer Detailtabelle basiert.
In unserem Beispiel fügen wir in der Tabelle HR.EMPLOYEES die Spalte AVERAGE_SALARY hinzu. AVERAGE_SALARY soll auf den Werten in „salaries“ in HR.EMPLOYEES basieren.
Wenn wir versuchen, einen FOR EACH ROW DELETE Trigger wie den folgenden zu schreiben, wird der Versuch, Zeilen aus der Tabelle EMPLOYEES zu löschen, scheitern:
Die Meldung sagt im Wesentlichen, dass es im Trigger AFTER DELETE…FOR EACH ROW schwierig ist, den neuen Durchschnittsverdienst (avarage salary) der Abteilung festzustellen.
Ursache: Ein Trigger (bzw. eine durch den Anwender definierte PL/SQL-Funktion, die in dem Befehl referenziert wird) versucht eine Table anzuschauen oder zu verändern, die während der Abarbeitung des Befehls selbst verändert wird.
Was zu tun ist: Den Trigger bzw. die Funktion so verändern, dass die Tabelle nicht gelesen wird.
Mit Horas Mutating Trigger Wizard können Sie das Problem für die Aktionen DELETE, UPDATE und INSERT auf einen Schlag lösen. Der Wizard wird über das Kontextmenu gestartet:
Wir haben hier nur ganz wenige der vom Wizard vorgegebenen Default-Werte angepasst: EMPLOYEES_CM wurde geändert in UPDATE_AVERAGE_SALARY_CM, die Felder „Aggregate Function“ und „Column to aggregate“ wurden gefüllt. EMPLOYEES hat mehrere Mastertabellen, deshalb müssen wir sicher sein, dass die richtige ausgewählt ist. Zum Schluss wird die „Aggregate Column“ angegeben.
Es ist voreingestellt, dass der Triggercode sowohl Updates, Inserts und Deletes bearbeitet.
Nach der Bestätigung mit OK wird – bei eingeschaltetem SQL Preview – der Code angezeigt.
Entdecken Sie die Möglichkeiten von KeepTool.
The compound trigger consists of three parts: the declaration, the BEFORE EACH ROW section and the AFTER STATEMENT section.
Before each DML operation, the affected DEPARTMENT_ID is stored in the nested table “key_tab.”
Der Compound Trigger besteht aus drei Teilen: der Deklaration, dem Abschnitt „BEFORE EACH ROW“ und dem Abschnitt AFTER STATEMENT.
Vor jeder DML-Operation wird die betroffene DEPARTMENT_ID in der Nested Table key_tab gespeichert.
Nachdem die einzelnen Zeilen in EMPLYEES aktualisiert wurden, werden im Abschnitt AFTER STATEMENT die Zeilen aus EMPLOYEES, die das geänderte DEPARTMENT betreffen, in die Variable „agg“ eingelesen. Diese Variable wird dazu verwendet, die Tabelle DEPARTMENT zu aktualisieren. Das beseitigt tatsächlich das Problem des Mutating Table, da der der Code erst dann ausgeführt wird, nachdem alle Zeilen aktualisiert worden sind.
Natürlich ist es auch möglich, den Code anzupassen, z.B. indem man die folgende DBMS_OUTPUT.PUT_LINE ergänzt:
Wenn wir nun noch einmal versuchen, den DELETE-Befehl auszuführen, werden die Zeilen gelöscht und der Trigger gibt eine Meldung aus.
Mit dem Compound Trigger Wizard bietet ein sehr komfortables Werkzeug, mit dem mächtigen Oracle-Feature der Compound Trigger umzugehen und dabei die möglichen Fehler zu vermeiden.