martinbulinski.de

Trigger sind spezielle PL/SQL Konstrukte ähnlich den Prozeduren. Während eine Prozedur allerdings explizit durch einen anderen Block über einen Prozeduraufruf gestartet wird, wird ein Trigger automatisch ausgeführt, wenn ein “triggerndes” Ereignis eintritt.

Diese Ereignisse können DML-Statements sein, aber auch DDL-Anweisungen und eine Anzahl von Systemereignissen.
Der Trigger kann vor oder nach dem auslösenden Ereignis ausgeführt werden. Ein Trigger kann ein Row-Level oder Statement-Trigger sein. Ersterer feuert (wird ausgeführt) für jede einzelne Zeile, die durch eine DML-Anweisung verändert wird, letzterer feuert nur einmal für das triggernde Kommando.

Hier sehen Sie die Syntax zum Erzeugen eines Triggers (eine vereinfachte Form):

CREATE [OR REPLACE] TRIGGER <trigger_name>
    {BEFORE|AFTER} {INSTEAD OF} {INSERT|DELETE|UPDATE {OF col}} ON <table_name>
    [FOR EACH ROW [WHEN (<trigger_condition>)]]
    <trigger_body>

Wichtig ist, daß

• BEFORE und AFTER Trigger nur für Tabellen angelegt werden können. INSTEAD OF Trigger funktionieren nur auf Views. Typischerweise werden sie genutzt, um View Updates zu realisieren, die sonst ja nur unter bestimmten Bedingungen möglich sind.

• Ereignisse, die einen Trigger auslösen, mit OR kombiniert werden können. Mehr noch, bei UPDATE Triggern können Trigger auch nur bei der Veränderung bestimmter Spalten ausgelöst werden (das Schlüsselwort UPDATE wird dann gefolgt von OF und der Liste der Spaltenattribute).

Hier sind einige Beispiele:

... INSERT ON tab ...
... INSERT OR DELETE OR UPDATE ON tab ...
... UPDATE OF col1, col2 OR INSERT ON tab ... 

Ist das Schlüsselwort FOR EACH ROW spezifiert, ist der Trigger ein ROW-LEVEL Trigger, sonst ein Statement-Level Trigger.

Bei ROW-LEVEL Triggern kann eine WHEN Bedingung formuliert werden, die in runden Klammern eingeschlossen sein muß. Die Bedingung muß erfüllt sein, damit der Trigger feuert. Diese Bedingung muß eine PL/SQL Bedingung sein, sie darf kein Subselect enthalten. Vorteil der WHEN-Klausel ist, dass der SQL-Interpreter die Bedingung prüft und, – falls diese nicht erfüllt ist -, die Kontrolle gar nicht erst an den PL/SQL-Interpreter übergibt. Folge ist ein Performancegewinn.

Der trigger_body ist ein PL/SQL-Block, keine Aneinanderreihung von SQL Statements!
ORACLE kennt einige Beschränkungen, was man in Triggern tun darf. Damit sollen Situationen vermieden werden, in denen ein Trigger Aktionen vornimmt, die wiederum einen weiteren Trigger auslösen und so weiter, so dass bspw. in der Regel keine Endlosschleifen entstehen. Diese Restriktionen sind:

• In einem Row-Level-Trigger darf nicht das gleiche Objekt modifiziert oder gelesen werden, dessen Modifikation den Trigger ausgelöst hat.
• In einem Statement-Trigger darf keine Tabelle modifiziert oder gelesen werden, die mit der Tabelle, auf der der Trigger liegt, durch ein Fremdschlüsselconstraint verbunden ist und der Trigger durch ein CASCADE ausgelöst wurde.

Um zu zeigen, wie Trigger funktionieren, nutzen wir die folgenden Tabellen:

CREATE TABLE T4 (a INTEGER, b CHAR(10));
CREATE TABLE T5 (c CHAR(10), d INTEGER);

Wir erzeugen einen Trigger, der einen Datensatz in Tabelle T5 einfügt, wenn ein Datensatz in die Tabelle T4 eingefügt wird und dabei der Wert von Spalte a <= 10 ist.

CREATE TRIGGER trig1
    AFTER INSERT ON T4
    FOR EACH ROW
    WHEN (NEW.a <= 10)
    BEGIN
        INSERT INTO T5 VALUES(:NEW.b, :NEW.a);
    END trig1;
/

Die speziellen Variablen NEW und OLD werden benutzt, um die Werte des neuen, bzw. alten Datensatzes zu referenzieren. Bitte beachten Sie, dass im Trigger-Body den Schlüsselworten NEW und OLD ein Doppelpunkt vorangestellt werden muß. In der WHEN Bedingung des Triggers steht kein Doppelpunkt! Wir erzeugen den Trigger mit dem CREATE TRIGGER Statement, durch das Erzeugen wird der jedoch nicht auch ausgeführt.

Nur ein auslösendes Ereignis, wie hier das Einfügen eines Datensatzes in T4, würde den Trigger-Body zur Ausführung bringen.

Um Informationen über Trigger zu erhalten, nutzen Sie:

select trigger_type, table_name, triggering_event from user_triggers 
where trigger_name = '<trigger_name>';

Um einen Trigger zu löschen:

drop trigger <trigger_name>;

Trigger lassen sich auch deaktivieren, ohne sie löschen zu müssen:

alter trigger <trigger_name> {disable|enable};

Bedenken Sie, dass durch das Löschen der Tabelle alle mit dieser Tabelle verbundenen Trigger mit gelöscht werden.

Der Before- und After-Zeitpunkt

Wozu genau sind nun die Zeitpunkt Before und After gedacht?? Bei ersten Versuchen scheint es völlig unerheblich, ob ein Before- oder After-Trigger genutzt wird. Z.B. findet immer ein Rollback der kompletten Anweisung statt, wenn ein Trigger mit einem Fehler terminiert.
Im Falle eines Row-Triggers ist der Zeitpunkt BEFORE aber auffällig. Denn nur bei einem BEFORE-ROW-Trigger hat dieser die Möglichkeit, die geänderten Werte z.B. im Zuge eines Updates noch zu verändern.

Folgendes Beispiel verhindert, dass das Gehalt eines Mitarbeiters auf mehr als 5000 geändert werden kann. (ACHTUNG, es handelt sich um einen UPDATE-Trigger, im Falle eines Inserts sind also Gehälter größer 5000 zulässig):

CREATE TRIGGER bu_mitarbeiter
    BEFORE UPDATE ON mitarbeiter
    FOR EACH ROW
    BEGIN
        IF :new.gehalt > 5000 then
            :new.gehalt = 5000;
        END IF;
    END trig1;

Obiges Beispiel ist kein gutes, denn das gleiche hätte man mit einem CHECK-Constraint erreichen können, was weitaus performanter ist, aber man sieht, dass ein Trigger die Werte eines Datensatzes nicht nur untersuchen, sondern auch ändern kann. Wie gesagt, der ROW-Trigger kann dies nur im Falle eines BEFORE-Triggers, also bevor die eigentlich DML-Anweisung ausgeführt wird.

Hat man nun mehrere Trigger, die bei einer DML-Anweisung auf einer Tabelle reagieren, ist die Ausführungsreihenfolge wie folgt:

• BEFORE-Statement-Trigger wird einmal ausgeführt
• Für jeden manipulierten Datensatz wird der BEFORE-ROW-Trigger ausgelöst.
• Datensatz wird durch die DML-Operation geändert
• Für jeden manipulierten Datensatz wird der AFTER-ROW-Trigger ausgelöst.
• AFTER Statement-Trigger wird einmal ausgeführt

Und wozu können nun diese verschiedenen Zeitpunkte verwendet werden? Z.B. um Trigger miteinander kommunizieren zu lassen!
Nehmen wir folgende Problemstellung an: Es darf maximal die Hälfte der Datensätze, die in einer Tabelle stehen, in einem Delete-Statement gelöscht werden.
Wir müssen also die Anzahl Datensätze vor und nach der Delete-Operation vergleichen, um festzustellen, wieviel nun tatsächlich gelöscht worden sind.
Ein Before-Delete-Trigger soll die Anzahl Datensätze zählen und sich den Wert in einer Package merken. Ein After-Delete-Trigger vergleicht den Wert mit der nun vorhandenen Anzahl Datensätze. Wenn mehr als die Hälfte gelöscht wurde, soll ein Fehler ausgelöst werden.

create package gedaechtnis is
          anzahl_vor_delete number;
     end;
     /

create trigger bd_tab before delete on tab
     begin
        select count(*) into gedaechtnis.anzahl_vor_delete from tab;
     end;
     /

create trigger ad_tab after delete on tab
         anzahl_nach_delete number;
     begin
         select count(*) into anzahl_nach_delete from tab;
         if anzahl_nach_delete < anzahl_vor_delete / 2 then
           raise_application_error(-20001,'Mehr als die Hälfte löschen ist verboten');
         end if;
      end;
      /

Ausserdem gibt es in ORACLE nicht nur Trigger, die auf DML-Ereignisse reagieren, sondern auch solche, die auf DDL- oder gar Systemereignisse (SHUTDOWN, LOGON o.ä.) reagieren. Damit werden Trigger schnell sehr mächtig, man macht sich aber das Debuggen u.U. schwer, da man über die Aktivitäten von Triggern schnell den Überblick verliert. Also setzen Sie Trigger wohlüberlegt und sparsam ein.
Wer mehr wissen will, hinterläßt einen Kommentar, dann erweitere ich den Bereich.