« Moderne Ansätze zur Datenvisualisierung  
  3D Scanner zum selber bauen »

Trigger und das Mutating Table Error Problem

Eines vorweg. Ein “Mutating Table Error” (Error ORA-04091 bei einer Oracle Datenbank) ist immer ein Hinweis auf schlecht geschriebenen Code. Jeder der an einem Trigger für seine Oracle Datenbank arbeitet, sollte sich vorher gründlich überlegen, ob er nicht genau diese Fehlermeldung durch unüberlegten Code generiert.

Soweit zum ermahnen …. und jetzt …. ich habe den Fehler natürlich prompt ebenfalls produziert. Das kommt davon, wenn man sich nicht vorher überlegt was man da tut.

Punkt 1, warum passiert mir das?

  • Generell wird das Problem durch ROW-LEVEL Trigger ausgelöst.
  • Weil ich meinen Trigger auf die Tabelle selektieren/ändern lasse, welche den Trigger ausgelöst hat.
  • Die andere Möglichkeit wäre, dass ich einen Foreign Key mit der “on delete cascade” Option gesetzt habe und nun eine Löschaktion ausführe.

Punkt 2, was kann ich jetzt dagegen machen?

  • Ich kann die Statements im Trigger mit dem PRAGMA AUTONOMOUS TRANSACTION ausführen und mit COMMIT abschliessen. Klingt erstmal gut, ist aber Schrott. Zwar habe ich so meinen Code ausführbar gemacht. Da die autonome Transaktion aber nun noch auf den Zustand der Tabelle sieht, so wie sie vor der Veränderung durch die aufrufende Aktion war (die aufrufende Aktion, sprich Transaktion, ist ja noch nicht vorbei), kann ich mir Inkonsistenzen einhandeln.1
  • Ich teile den Trigger auf, speichere die notwendigen Informationen zuerst in einer temporären Tabelle, sobald der “FOR EACH ROW” Trigger feuert und lasse danach einen STATEMENT Trigger die notwendigen Änderungen durchführen. 2 3

Anbei ein kleines Beispiel zur Demonstration des Problems und wie es behoben werden kann. Bitte keine Kommentare bezüglich Tabellenaufbau und Triggerprogrammierung. Es handelt sich schlicht um ein Beispiel, bei dem bewusst auf zusätzlichen Code verzichtet wurde, damit der Leser sich auf die Lösung des eigentlichen Problems konzentrieren kann. Es ist kein Muster, das so in der Praxis eingesetzt wird oder werden sollte.

Zuerst erstellen wir eine einfache Tabelle mit ID-Spalte und fügen einen Datensatz ein:

CREATE TABLE “KOSTEN”
( “ID” NUMBER(5,0) NOT NULL ENABLE,
“TYP” VARCHAR2(20) NOT NULL ENABLE,
“MENGE” NUMBER NOT NULL ENABLE,
“ART” VARCHAR2(200),
CONSTRAINT “KOSTEN_PK” PRIMARY KEY (”ID”) ENABLE
)
/
CREATE OR REPLACE TRIGGER “BI_KOSTEN”
before insert on “KOSTEN”
for each row
begin
select “KOSTEN_SEQ”.nextval into :NEW.ID from dual;
end;
/
ALTER TRIGGER “BI_KOSTEN” ENABLE
/

INSERT INTO “KOSTEN”
(”TYP”, “MENGE”, “ART”)
VALUES
(’PLAN’, ‘400′, ‘Haushalt’)
/

Danach erstellen wir einen Trigger, der die Menge beim Datensatz mit TYP = ‘PLAN’ reduzieren soll, sobald ein Datensatz mit TYP = ‘REAL’ eingefügt wird.

CREATE OR REPLACE TRIGGER “TR_DECREASE_PLAN”
after insert on “KOSTEN”
for each row
declare
v_menge number;
v_neue_menge number;
begin
select “MENGE” into v_menge from “KOSTEN” where “TYP” = ‘PLAN’;

v_neue_menge := v_menge - :NEW.”MENGE”;

UPDATE “KOSTEN” set “MENGE” = v_neue_menge where “TYP” = ‘PLAN’;

end;
/

Wird jetzt ein REAL Datensatz eingefügt, so kommt es zu der Fehlermeldung Mutating Table:

INSERT INTO “KOSTEN”
(”TYP”, “MENGE”, “ART”)
VALUES
(’REAL’, ‘20′, ‘Haushalt’)

/

ORA-04091: Tabelle SYSTEM.KOSTEN wird gerade geändert, Trigger/Funktion sieht dies möglicherweise nicht
ORA-06512: in “SYSTEM.TR_DECREASE_PLAN”, Zeile 5
ORA-04088: Fehler bei der Ausführung von Trigger ‘SYSTEM.TR_DECREASE_PLAN’

Um das zu umgehen, erstellen wir erst eine temporäre Tabelle:

CREATE GLOBAL TEMPORARY TABLE “KOSTEN_TEMP”
( “ID” NUMBER(5,0) NOT NULL ENABLE,
“MENGE” NUMBER NOT NULL ENABLE,
“ART” VARCHAR2(200)
) ON COMMIT DELETE ROWS;
/

Jetzt wird ein Trigger programmiert, der beim Einfügen von Datensätzen mit dem TYP = ‘REAL’ diese in die temporäre Tabelle kopiert:

CREATE OR REPLACE TRIGGER “TR_COPY_REAL”
after insert on “KOSTEN”
for each row
begin
INSERT INTO “KOSTEN_TEMP”
(”ID”, “MENGE”, “ART”)
VALUES
(:NEW.”ID”, :NEW.”MENGE”, :NEW.”ART”);
end;
/

Der Trigger “TR_DECREASE_PLAN” wird zu einem Statement Trigger umgeschrieben (indem FOR EACH ROW entfernt wird). Er feuert somit einmal nach jeder INSERT Transaktion, holt sich den passenden Datensatz aus der temporären Tabelle und berechnet die neue Planmenge. (Beispiel funktioniert so natürlich nicht, wenn mit einem Insert mehrere Datensätze eingefügt werden).

CREATE OR REPLACE TRIGGER “TR_DECREASE_PLAN”
after insert on “KOSTEN”
declare
v_menge_real number;
v_menge_plan number;
v_neue_menge number;
begin
select “MENGE” into v_menge_real from “KOSTEN_TEMP”;
select “MENGE” into v_menge_plan from “KOSTEN” where “TYP” = ‘PLAN’;

v_neue_menge := v_menge_plan - v_menge_real;

UPDATE “KOSTEN” set “MENGE” = v_neue_menge where “TYP” = ‘PLAN’;
end;
/

Wird nun ein neuer Datensatz mit TYP = ‘REAL’ eingefügt, tritt der Mutating Table Fehler nicht mehr auf.

Die Referenzen zu den jeweiligen Artikeln in den Fussnoten sollten das Ganze noch näher erklären. :-) Zu guter Letzt …. hören wir auf die 4 Tips wie man das Mutating Table Trigger Problem lösen kann von Burleson Consulting.

  1. http://articles.techrepublic.com.com/5100-10878_11-5034684.html [↩]
  2. http://www.databasejournal.com/features/oracle/article.php/3329121 [↩]
  3. http://www.oracle-base.com/articles/9i/MutatingTableExceptions.php [↩]

August 4th, 2007 | Tags: , , , , , | Category: HowTo

1 comment to Trigger und das Mutating Table Error Problem

  • Aness
    Oktober 13th, 2009 at 15:43

    Hallo Joachim,

    müsste der zweite Trigger nicht gefeuert werden nach einem Insert auf KOSTEN_TEMP. Falls ja, dann bliebe die Fehlermeldung ‘wird gerade geändert, Trigger/Funktion sieht dies möglicherweise nicht’ doch bestehen, oder? Hab ich einen Denkfehler?

    Viele Grüße
    Aness

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>