Unterseite von Mutating-Table-Problem

Grundproblem beim Mutating-Table-Problem, wie es bei Oracle auftritt, ist, dass im Aktionsteil eines zeilenorientierten Triggers (ROW Trigger) mittels Anfragen oder INSERT-, UPDATE-, DELETE-Anweisung auf die Tabelle zugegriffen wird, für die der Trigger definiert ist. Wie die Aufgabenstellung des Beispiels zeigt, ist das eine durchaus naheliegendes Problem, das schnell mal auftreten kann.

Der Versuch, die Aufgabenstellung mit einem ROW-Trigger zu lösen, hat den Vorteil, man verfügt in den Transitionsvariablen über die Informationen, welche Datensätze betroffen sind von der feuernden Manipulation.

Der Versuch, die Aufgabenstellung mit einem STATEMENT-Trigger (befehlsorientierter Trigger) zu lösen funktioniert insofern, als die gewünschte Aktion im STATEMENT-Trigger ohne Laufzeitproblem ausführbar ist. Nur hat der STATEMENT-Trigger genau den Nachteil, in seinem Aktionsteil gibt es keine Transitionsvariablen mit den Informationen über die manipulierten Datensätze und muss mit für alle gespeicherten Datensätze die Aktion durchführen, was je nach Größe des Datenbestands äußerst ineffizient sein kann und entsprechend viel Zeit beansprucht.

Grundidee einer Lösung mit Oracle-Triggern:

• Im ROW-Trigger müssen die Informationen über die relevanten Datensätze "irgendwo" zwischengespeichert werden.
• Die STATEMENT-Trigger lesen die Informationen über die relevanten Datensätze aus dem "irgendwo" aus und führen die Aktion dann nur noch für die notwendigen Datensätze durch.
• Das "irgendwo" wird für den Informationsaustausch zwischen ROW- und STATEMENT-Triggern benötigt, der sonst nicht möglich ist.

Für das "irgendwo" bieten sich zwei Lösungen an:

• Tabellen haben, wenn sie im Vorfeld angelegt wurden, den Nachteil, dass ggf. mehrere Benutzer darauf zugreifen. Es muss dann sichergestellt werden, dass jeder Benutzer nur auf seine Daten zugreifen kann. Werden die Daten nicht mehr benötigt, so müssen sie explizit gelöscht werden. Werden sie erst zur Laufzeit als temporäre Tabelle angelegt, so ist das doch mit einem beträchtlichen Zeitaufwand verbunden für das Anlegen und spätere Löschen und das Problem der Mehrbenutzernutzung bleibt weiterhin bestehen.
• Öffentliche PL/SQL-Package-Objekte (public) haben genau den Vorteil. Sie werden für jede Session angelegt und am Ende der Session wieder automatisch gelöscht. Somit verfügt jeder Anwender, der diesen Trigger aktiviert, über seine eigenen öffentlichen Package-Variablen. Da es ggf. mehrere Datensätze sein können, die betroffen sind, ist es hier notwendig, die Variable als sog. PL/SQL-Tabelle anzulegen.

Lösung des Mutating-Table-Problems in vier Schritten mit Oracle-Konzepten:

1. Ein Package stellt öffentliche Package-Variablen zur Verfügung, einmal eine Zählervariable und zum anderen Variablen für die Informationen über die manipulierten Datensätze in Form sog. PL/SQL-Tabellen.
2. Ein BEFORE STATEMENT-Tigger initialisiert zu Beginn einer jeden Manipulationsanweisung diese Package–Variablen.
3. Ein AFTER ROW-Trigger speichert die Informationen über die manipulierten Datensätze aus den Transitionsvariablen in die Package-PL/SQL-Tabellen zwischen und inkrementiert die Zählervariable.
4. Ein AFTER STATEMENT-Trigger liest in einer Schleife die zwischengespeicherten Informationen über die manipulierten Datensätze aus den Package-PL/SQL-Tabellen aus und führt nur für diese relevanten Datensätze die erforderlichen Aktionen aus.

Beispiel:

Jede Abteilung verfügt über ein Gehaltsbudget (Spalte budget). Die Summe der Gehälter aller Mitarbeiter (Spalte gehalt) einer Abteilung darf dieses Budget nicht überschreiten.

Das zweite Mutating-Table-Problem-Beispiel läßt sich nun wie folgt ohne dieses Laufzeitproblem lösen:

   -- 1. öffentliche Package-Variablen
   CREATE OR REPLACE PACKAGE gehaltsbudget_pck AS 
      TYPE abtnr_tabletyp    IS TABLE OF  mitarbeiter.abtnr%TYPE NOT NULL
                             INDEX BY BINARY_INTEGER;
      v_abtnr_tabelle        abtnr_tabletyp;
      v_zaehler              BINARY_INTEGER  DEFAULT 1;
   END gehaltsbudget_pck;

   -- 2. BEFORE STATEMENT zum Initialisieren
   CREATE OR REPLACE TRIGGER init_gehaltsbudget_pck_trg
   BEFORE INSERT OR UPDATE OF gehalt, abtnr ON mitarbeiter
   BEGIN		
	gehaltsbudget_pck.v_zaehler := 1;
   END;

   -- 3. AFTER ROW für das Zwischenspeichern in den öffentlichen Package-Variablen
   CREATE OR REPLACE TRIGGER gehaltsbudget_aiur_trg
   AFTER INSERT OR UPDATE OF gehalt, abtnr  ON mitarbeiter
   FOR EACH ROW
   BEGIN		
      gehaltsbudget_pck.v_abtnr_tabelle(gehaltsbudget_pck.v_zaehler) := :NEW.abtnr;
      gehaltsbudget_pck.v_zaehler := gehaltsbudget_pck.v_zaehler + 1;
   END;

   -- 4. AFTER STATEMENT für die eigentlichen Aktionen, hier Tests
   CREATE OR REPLACE TRIGGER gehaltsbudget_aius_trg
   AFTER INSERT OR UPDATE OF gehalt, abtnr  ON mitarbeiter
   DECLARE
      v_summe      NUMBER(20);	
      v_budget     abteilung.budget%TYPE;
   BEGIN		
      FOR i IN 1..gehaltsbudget_pck.zaehler-1 LOOP       
          SELECT  SUM(gehalt)  INTO  v_summe
            FROM  mitarbeiter      
            WHERE abtnr = gehaltsbudget_pck.abtnr_tabelle(i);
          SELECT  budget       INTO   v_budget
            FROM  abteilung         
            WHERE abtnr = gehaltsbudget_pck.abtnr_tabelle(i);
          IF v_summe > v_budget THEN
             RAISE_APPLICATION_ERROR(-20003,'Summe der Gehälter übersteigt das Abteilungsbudget: ' 
                                            || gehaltsbudget_pck.abtnr_tabelle(i));
          END IF;
      END LOOP;
   END;

   -- 5. AFTER ROW-Trigger auf Abteilung, weil auch eine Verringerung des Budgets ein feuerndes Ereignis ist
   -- Dieser Trigger der Vollständigkeit der Lösung wegen, mit dem Mutating-Table-Problem hat er nichts zu tun
   CREATE OR REPLACE TRIGGER gehaltsbudget_abt_trg
   AFTER UPDATE OF budget, abtnr  ON abteilung
   FOR EACH ROW
   WHEN (NEW.budget < OLD.budget)
   DECLARE
      v_summe     number(20);
   BEGIN 
      SELECT  SUM(gehalt) INTO v_summe
        FROM  mitarbeiter 
        WHERE abtnr = :NEW.abtnr;
      IF v_summe > :NEW.budget THEN 
         RAISE_APPLICATION_ERROR(-20003,'Summe der Gehälter übersteigt das Abteilungsbudget');
      END IF;
   END;

Kategorie: Aktive Datenbanken, Oracle-PL-SQL, SQL, M