Trigger

(Einführung in SQL: Trigger)

(Einführung in SQL: Trigger)

Ein Trigger ist so etwas wie eine Routine zur Ereignisbehandlung: Immer dann, wenn sich in der Datenbank eine bestimmte Situation ereignet, wird eine spezielle Prozedur automatisch ausgeführt.

Bitte haben Sie Nachsicht: Wegen der vielen Varianten bei den DBMS beschränkt sich dieses Kapitel bei den Hinweisen und Beispielen weitgehend auf Firebird. Zusammen mit den speziellen Hinweisen zur SQL-Programmierung sollten sie problemlos an andere DBMS-Regeln angepasst werden können. Da es sich um ein Wikibook handelt, dürfen Sie das Buch gerne um weitere Hinweise und andere Beispiele ergänzen.

Ein Überblick

Schon die kurze Einleitung weist darauf hin: Ein Trigger wird niemals vom Benutzer gezielt aufgerufen, sondern immer automatisch vom DBMS erledigt; darin unterscheidet er sich wesentlich von eigenen Funktionen oder Prozeduren.

Eine deutsche Übersetzung wäre eigentlich Auslöser; besser passen würde „etwas, das ausgelöst wird“. Allenfalls spricht man noch von Ereignisbehandlungsroutinen. Solche Formulierungen sind aber äußerst ungebräuchlich; der Begriff Trigger hat sich eingebürgert.

Ein Trigger ist in folgenden Situationen nützlich:

• Werte in einer Zeile einer Tabelle sollen festgelegt werden.
  • Dies wird vor allem benutzt, wenn es keine AutoInc-Spalte gibt, siehe das Beispiel mit der nächsten ID.
• Werte sollen vor dem Speichern auf ihre Plausibilität geprüft werden.
• Veränderungen in der Datenbank sollen automatisch protokolliert werden.
• Die Regeln der referenziellen Integrität sollen mit Hilfe der Fremdschlüssel-Beziehungen überwacht werden.

Trigger definieren

Trigger erstellen

Die Syntax für die Definition eines Triggers sieht grundsätzlich so aus:

CREATE OR ALTER TRIGGER <routine-name> FOR <Tabellenname>
     [ ACTIVE | INACTIVE ]
     { BEFORE | AFTER }          /* bei MS-SQL heißt es INSTEAD OF */
     { INSERT | UPDATE | DELETE }
     [ POSITION <zahl> ]
AS 
BEGIN
     [ <variablenliste> ]
     <routine body>
END

Auch dabei sind wieder die Besonderheiten des jeweiligen DBMS zu beachten. Aber die Ähnlichkeit zu den Routinen fällt auf.

Notwendig sind folgende Angaben:

• neben dem Befehlsnamen der Name des Triggers
• dazu die Tabelle, zu der er gehört
• mehrere Angaben, bei welchem Befehl und an welcher Stelle er wirksam sein soll; Oracle kennt neben den Varianten, die bei der Syntax genannt werden, auch eine WHEN-Bedingung.
• das Schlüsselwort AS als Zeichen für den Inhalt
• die Schlüsselwörter BEGIN und END als Begrenzer für den Inhalt

Hinzu kommen die folgenden Angaben:

• eine Liste von Variablen, die innerhalb der Routine verwendet werden; diese Liste steht je nach DBMS zwischen AS und BEGIN oder innerhalb des Rumpfes (also zwischen BEGIN und END)
• die Befehle, die innerhalb der Prozedur ausgeführt werden sollen

Eingabe- und Ausgabe-Parameter gibt es nicht, weil es sich um automatische Arbeitsabläufe handelt.

Die folgenden Variablen stehen immer zur Verfügung. MS-SQL benutzt andere Verfahren; bei Oracle ist die Dokumentation nicht eindeutig.

• OLD ist der Datensatz vor einer Änderung.
• NEW ist der Datensatz nach einer Änderung.

Mit diesen Variablen zusammen mit den Spaltennamen können die Werte „vorher“ und „nachher“ geprüft und bearbeitet werden (siehe die Beispiele).

Bei den Befehlen innerhalb des Triggers handelt es sich (eher selten) um „normale“ SQL-Befehle und überwiegend um Bestandteile der SQL-Programmiersprache. Zum Semikolon, das den Abschluss des CREATE-Befehls darstellen sollte, aber innerhalb des Inhalts bereits für jede einzelne Anweisung benutzt wird, beachten Sie bitte auch die Hinweise zur SQL-Programmierung unter Anweisungen begrenzen.

Trigger deaktivieren

Natürlich kann ein Trigger gelöscht werden:

DROP TRIGGER <name>;

Wenn das DBMS es ermöglicht, kann er auch vorübergehend deaktiviert werden.

ALTER TRIGGER <name> INACTIVE;

Bei ALTER sind nur die Angaben erforderlich, die sich ändern.

Einen Trigger vorübergehend abzuschalten, ist vor allem während eines längeren Arbeitsablaufs in zwei Situationen hilfreich:

• Ein Trigger bremst sehr stark; dann kann z. B. auf eine Protokollierung jeder kleinen Änderung verzichtet werden.
• Wenn Widersprüche in den Daten auftreten könnten, die sich nach vollständiger Erledigung „von selbst auflösen“, können sie durch die Deaktivierung vermieden werden.

Beispiele

Lege die nächste ID fest

Wenn ein DBMS keinen automatischen Zähler, also keine AUTOINCREMENT-Spalte ermöglicht, kann eine ID vom Anwender vergeben werden. Viel besser ist aber, wenn sich das DBMS selbst darum kümmert. Dies wird in der Beispieldatenbank bei Firebird und Oracle benutzt. Zum einen wird eine SEQUENCE definiert; der Trigger holt den nächsten Wert:

Oracle-Version

CREATE OR REPLACE TRIGGER Abteilung_BI
  BEFORE INSERT ON Abteilung
  FOR each row
  WHEN (new.ID IS NULL)
BEGIN
  SELECT Abteilung_ID.NEXTVAL INTO :new.ID FROM DUAL;
END

Der Trigger wird vor einem INSERT-Befehl aufgerufen. Wenn für den neuen Datensatz – repräsentiert durch die Variable new – keine ID angegeben ist, dann soll der NEXTVAL gemäß der SEQUENCE Abteilung_ID geholt werden.

Der Name des Triggers Abteilung_BI soll andeuten, dass er zur Tabelle Abteilung gehört und wie folgt aktiv ist: B (= Before) I (= Insert). Bei Firebird fügen wir noch eine '0' an zur Festlegung, dass er an Position null auszuführen ist.

Protokolliere Zeit und Benutzer bei Änderungen

In vielen Situationen ist es wichtig, dass Änderungen in einer Datenbank kontrolliert werden können. (Das DBMS macht es für sich sowieso, aber oft soll es auch „nach außen hin“ sichtbar sein.) Dafür gibt es in Tabellen Spalten wie folgt (in der Beispieldatenbank verzichten wir darauf):

CREATE TABLE /* usw. bis */
  Last_User     VARCHAR(30),
  Last_Change   TIMESTAMP,
  /* usw. */

Wenn solche Spalten bei der Tabelle Mitarbeiter vorhanden wären, gäbe es einen passenden Trigger. Ohne dass sich der Anwender darum kümmern müsste, werden Nutzer (laut Anmeldung an der Datenbank) und aktuelle Zeit immer gespeichert.

Firebird-Version

CREATE OR ALTER TRIGGER Mitarbeiter_BU1 FOR Mitarbeiter
  ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
BEGIN
  new.Last_User = CURRENT_USER;
  new.Last_Change = CURRENT_TIMESTAMP;
END

Aktualisiere abhängige Daten

In vielen Fällen gibt es Abhängigkeiten zwischen verschiedenen Tabellen. Durch Fremdschlüssel-Beziehungen können diese Verbindungen bei Änderungen automatisch berücksichtigt werden. Solche Anpassungen sind auch durch Trigger möglich.

Mit einer Prozedur (siehe dort) hatten wir ein INSERT in mehrere Tabellen ausgeführt. Ähnlich kann ein Trigger abhängige Datensätze löschen:

• Der Anwender ruft ein DELETE für einen Versicherungsvertrag auf.
• Der Trigger löscht den dazugehörigen Versicherungsnehmer und das Fahrzeug.
• Ein Trigger zur Tabelle Fahrzeug löscht alle registrierten Schadensfälle usw.

Aber das ist so komplex und hat Auswirkungen auf weitere Tabellen, dass wir darauf verzichten.

In einer „echten“ Firma müssten solche Informationen sowieso dauerhaft gespeichert werden – zumindest so lange wie die Buchhaltungsdaten. Ein Vertrag wird also im normalen Betrieb niemals gelöscht, sondern als „nicht mehr aktiv“ markiert.

Aber auch in unserer Firma gibt es sinnvolle Maßnahmen:

Aufgabe

Beim Ausscheiden eines Mitarbeiters wird (soweit vorhanden) ein persönlicher Dienstwagen gestrichen.

Firebird-Version

create or alter trigger Mitarbeiter_BD1 for Mitarbeiter
  ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
  update Dienstwagen
     set Mitarbeiter_ID = null
   where Mitarbeiter_ID = old.ID;
END

MySQL-Version

create trigger Mitarbeiter_BD1
  BEFORE DELETE on mitarbeiter
  FOR EACH ROW
BEGIN
  update Dienstwagen
     set Mitarbeiter_ID = null
   where Mitarbeiter_ID = old.ID;
END

Bevor ein Mitarbeiter gelöscht wird, wird geprüft, ob ihm ein persönlicher Dienstwagen zugewiesen ist. Dieser Vermerk wird (sofern vorhanden) auf NULL gesetzt; die WHERE-Klausel greift dabei auf die ID des bisherigen Datensatzes der Tabelle Mitarbeiter zu.

Zusammenfassung

Dieses Kapitel erläuterte Trigger als automatisch ausgeführte Prozeduren:

• Ein Trigger wird vor oder nach einem Speichern-Befehl (Insert, Update, Delete) ausgeführt.
• Er dient zur automatischen Prüfung oder Vervollständigung von Werten.
• Damit vereinfacht er die Arbeit für den Anwender und
• sorgt für die Einhaltung von Sicherheitsregeln in der Datenbank.

Hinweis: Bedenken Sie, welche konkreten Situationen in den letzten Kapiteln und den Übungen behandelt werden. Diese Zusammenhänge zwischen den Tabellen müssen bei der Planung einer Datenbank mit ihren Fremdschlüsseln, Prozeduren und Triggern für „gleichzeitiges“ Einfügen, Ändern oder Löschen in mehreren Tabellen und notwendigen Maßnahmen in anderen Tabellen berücksichtigt werden. Auch in dieser Hinsicht bietet unsere Beispieldatenbank nur einen kleinen Einblick in das, was mit SQL möglich ist.

Übungen

Übung 1

Trigger definieren

Zur Lösung

Welche der folgenden Aussagen sind wahr, welche sind falsch?

1. Ein Trigger wird durch eine Prozedur aufgerufen.
2. Ein Trigger hat keine Eingabe-Parameter.
3. Ein Trigger hat keine (lokalen) Variablen.
4. Ein Trigger kann nur die Tabelle bearbeiten, der er zugeordnet ist.
5. Ein Trigger kann sowohl „normale“ SQL-Befehle als auch Elemente der SQL-Programmierung enthalten.

Übung 2

Trigger verwenden

Zur Lösung

In welchen der folgenden Situationen ist ein Trigger sinnvoll, in welchen nicht bzw. nicht möglich? Gehen Sie davon aus, dass alle benötigten Informationen in der Firmen-Datenbank zur Speicherung vorgesehen sind.

1. Wenn ein Versicherungsvertrag neu aufgenommen wird, sollen auch alle Datensätze in zugeordneten Tabellen neu aufgenommen werden.
2. Vorgabewerte für einzelne Spalten können eingetragen werden (wie bei einem DEFAULT-Wert).
3. Wenn eine Abteilung gelöscht (d. h. geschlossen) wird, sollen auch alle ihre Mitarbeiter gelöscht werden (weil die Abteilung ausgelagert wird).
4. Wenn ein Versicherungsvertrag gelöscht wird, sollen auch alle Datensätze in zugeordneten Tabellen bearbeitet werden: Datensätze, die noch in anderem Zusammenhang benutzt werden (z. B. Mitarbeiter), bleiben unverändert; Datensätze, die sonst nicht mehr benutzt werden (z. B. Fahrzeug), werden gelöscht.

Übung 3

Trigger-Definition kontrollieren

Zur Lösung

Nennen Sie in der folgenden Definition Punkte, die unabhängig vom SQL-Dialekt falsch sind. Je nach DBMS sind noch andere Punkte falsch, danach wird aber nicht gefragt. Der Trigger soll folgende Aufgabe erledigen:

• Wenn ein neuer Fahrzeugtyp aufgenommen werden soll, ist der Hersteller zu überprüfen.
• Ist der angegebene Hersteller (d. h. die Hersteller_ID) in der Tabelle Fahrzeughersteller gespeichert? Wenn ja, dann ist nichts weiter zu erledigen.
• Wenn nein, dann ist ein Fahrzeughersteller mit dem Namen „unbekannt“ zu suchen.
  • Wenn dieser vorhanden ist, ist dessen ID als Hersteller_ID zu übernehmen.
  • Andernfalls ist er mit der angegebenen Hersteller_ID neu als „unbekannt“ zu registrieren.

create Fahrzeugtyp_Check_Hersteller as trigger
    to Fahrzeugtyp
    for INSERT 
as
  DECLARE VARIABLE INTEGER fh_id
BEGIN
  --  Initialisierung
  fh_id = null;
  --  prüfen, ob ID vorhanden ist
  select ID
    from Fahrzeughersteller
   where ID = new.Hersteller_ID
  --  wenn sie nicht gefunden wurde
  if (fh_id is null) THEN
    --  suche stattdessen den Hersteller 'unbekannt'
    select ID
      from Fahrzeughersteller
     where Name = 'unbekannt'
    --  wenn auch dazu die ID nicht gefunden wurde
    if (fh_id is null) THEN
      fh_id = new.Hersteller_ID;
      insert into Fahrzeughersteller
         values ( :fh_id, 'unbekannt', 'unbekannt' )
    END
  END
  new.Hersteller_ID = :fh_id
END

Übung 4

Trigger-Definition kontrollieren

Zur Lösung

Berichtigen Sie den Code der vorigen Aufgabe.

Übung 5

Trigger Mitarbeiter_On_Delete erstellen

Zur Lösung

Skizzieren Sie den Inhalt eines Triggers Mitarbeiter_On_Delete für die Tabelle Mitarbeiter, der folgende Aufgaben ausführen soll:

• Zu behandeln ist die Situation, dass ein Mitarbeiter aus der Firma ausscheidet, also zu löschen ist.
• Suche die ID des zugehörigen Abteilungsleiters.
• In allen Tabellen und Datensätzen, in denen der Mitarbeiter als Sachbearbeiter registriert ist, ist stattdessen der Abteilungsleiter als „zuständig“ einzutragen.

Ignorieren Sie die Situation, dass der Abteilungsleiter selbst ausscheidet.

Übung 6

Trigger Mitarbeiter_On_Delete erstellen

Zur Lösung

Erstellen Sie den Trigger Mitarbeiter_On_Delete aus der vorigen Übung.

Lösungen

Lösung zu Übung 1

Trigger definieren

Zur Übung

Die Aussagen 2, 5 sind wahr. Die Aussagen 1, 3, 4 sind falsch.

Lösung zu Übung 2

Trigger verwenden

Zur Übung

1. nicht möglich, weil die Werte für die Datensätze in den zugeordneten Tabellen nicht bekannt sind
2. sinnvoll, aber die Festlegung per DEFAULT ist vorzuziehen
3. zur Not sinnvoll, wenn die Fremdschlüssel nicht durch ON DELETE geeignet gesteuert werden können; aber dies ist riskannt wegen der „Nebenwirkungen“, siehe Übung 5/6
4. zur Not möglich, aber eher weniger sinnvoll wegen der vielen zugeordneten Tabellen und vieler „Nebenwirkungen“

Lösung zu Übung 3

Trigger-Definition kontrollieren

Zur Übung

• Zeile 1: Der Befehl lautet: CREATE TRIGGER <name>.
• Zeile 2: Die Tabelle steht je nach DBMS bei ON oder FOR, nicht bei TO.
• Zeile 3: Die Aktivierung BEFORE oder AFTER (bei MS-SQL INSTEAD OF) fehlt.
• Zeile 5: Zuerst kommt der Name der Variablen, dann der Datentyp; das abschließende Semikolon fehlt.
• Zeile 10/12 und 16/18: Es fehlt die Übergabe der ID, die durch SELECT gefunden wurde, an die Variable fh_id. Je nach DBMS erfolgt dies unterschiedlich, aber es muss gemacht werden, damit das Ergebnis der Abfrage in der Variablen gespeichert und danach verwendet werden kann.
• Zeile 12, 18, 23, 26: Es fehlt jeweils das abschließende Semikolon.
• Zeile 14 und 20: Mehrere Anweisungen sind in BEGIN...END einzuschließen.

Lösung zu Übung 4

Trigger-Definition kontrollieren

Zur Übung

--  Firebird-Version
create or alter trigger Fahrzeugtyp_Check_Hersteller
    for Fahrzeugtyp
    ACTIVE BEFORE INSERT POSITION 10
as
  DECLARE VARIABLE fh_id INTEGER;
BEGIN
  fh_id = null;
  select ID
    from Fahrzeughersteller
   where id = new.Hersteller_ID
    into :fh_id;
  if (fh_id is null) THEN
  BEGIN
    select Id
      from Fahrzeughersteller
     where Name = 'unbekannt'
      into :fh_id;
    if (fh_id is null) THEN
    BEGIN
      fh_id = new.Hersteller_ID;
      insert into Fahrzeughersteller
         values ( :fh_id, 'unbekannt', 'unbekannt' );
    END
  END
  new.Hersteller_ID = :fh_id;
END

Lösung zu Übung 5

Trigger Mitarbeiter_On_Delete erstellen

Zur Übung

• Wir brauchen einen Trigger BEFORE DELETE zur Tabelle Mitarbeiter.
• Wir brauchen eine Variable für die ID des Abteilungsleiters.
• Durch einen SELECT auf die Abteilung_ID des ausscheidenden Mitarbeiters bekommen wir die ID des Abteilungsleiters; die wird in der Variablen gespeichert.
• Für alle betroffenen Tabellen ist ein UPDATE zu machen, durch das die Mitarbeiter_ID durch die ID des Abteilungsleiters ersetzt wird. Dies betrifft die Tabellen Schadensfall, Versicherungsvertrag.
• Die Tabelle Dienstwagen kann unberücksichtigt bleiben wegen des o. g. Beispiels.

Lösung zu Übung 6

Trigger Mitarbeiter_On_Delete erstellen

Zur Übung

--  Firebird-Version
create or alter trigger Mitarbeiter_On_Delete
    for Mitarbeiter
    ACTIVE BEFORE DELETE POSITION 10
as
  DECLARE VARIABLE ltr_id INTEGER;
BEGIN
  ltr_id = null;
  --  hole die ID des Abteilungsleiters in die Variable
  select ID
    from Mitarbeiter
   where Abteilung_id = old.Abteilung_ID
     and Ist_Leiter = 'J'
    into :ltr_id;
  --  ändere die Mitarbeiter_ID für die Schadensfälle
  update Schadensfall
     set Mitarbeiter_ID = :ltr_id
   where Mitarbeiter_ID = old.ID;
  --  ändere die Mitarbeiter_ID für die Verträge
  update Versicherungsvertrag
     set Mitarbeiter_ID = :ltr_id
   where Mitarbeiter_ID = old.ID;
END

Siehe auch

Verschiedene Einzelheiten stehen in folgenden Kapiteln:

• Fremdschlüssel-Beziehungen
• SQL-Programmiersprache
• Prozeduren

Über Wikipedia sind grundlegende Informationen zu erhalten:

• Datenbanktrigger
• Ereignisse in der Programmierung, Behandlung von Ereignissen
• Referentielle Integrität

Hinweis