Einführung in SQL: Trigger

Aus Wikibooks

Inhaltsverzeichnis: Erweiterungen Zurück zu: Prozeduren Weiter zu: Änderung der Datenbankstruktur

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

[Bearbeiten] 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 übrigens Auslöser; besser passen würde "etwas, das ausgelöst wird". Beide 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 überwacht werden, siehe auch Fremdschlüssel-Beziehungen.

Hoch zum Seitenanfang

[Bearbeiten] Trigger definieren

[Bearbeiten] Trigger erstellen

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

CREATE OR ALTER TRIGGER <routine-name> FOR <Tabellenname>
     [ ACTIVE | INACTIVE ]
     { BEFORE | AFTER }
     { INSERT | UPDATE | DELETE }
     [ POSITION <zahl> ]
AS 
     [ <variablenliste> ]
BEGIN
     <routine body>
END

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
• 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 Prozedur verwendet werden
• 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:

• 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 unter Anweisungen begrenzen.

Hoch zum Seitenanfang

[Bearbeiten] 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.

Hoch zum Seitenanfang

[Bearbeiten] Beispiele

[Bearbeiten] Lege die nächste ID fest

Wenn ein DBMS keinen automatischen Zähler, also keine AUTOINC-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:

CREATE OR ALTER TRIGGER Abteilung_BI0 FOR Abteilung
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF ((new.ID IS NULL) OR (new.ID = 0))
  THEN new.ID = NEXT VALUE FOR Abteilung_ID;
END

Der Trigger wird vor einem INSERT-Befehl als erstes (Position 0) aufgerufen. Wenn für den neuen Datensatz – repräsentiert durch die Variable new – keine ID angegeben oder der Wert '0' (null) ist, dann soll der NEXT VALUE gemäß der SEQUENCE Abteilung_ID geholt werden.

Der Name des Triggers Abteilung_BI0 soll andeuten: er gehört zur Tabelle Abteilung und ist wie folgt aktiv: B (= Before) I (= Insert) 0 (= Position null).

Hoch zum Seitenanfang

[Bearbeiten] 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 Trigger wie folgt:

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

Ohne dass sich der Anwender darum kümmern müsste, werden Nutzer (laut Anmeldung an der Datenbank) und aktuelle Zeit immer gespeichert.

Hoch zum Seitenanfang

[Bearbeiten] 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 haben 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 dazu 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 aufbewahrt werden müssen (teilweise bis zu zehn Jahren). Ein Vertrag wird also im normalen Betrieb niemals gelöscht, sondern nur als "nicht mehr aktiv" markiert.

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

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

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

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.

Hoch zum Seitenanfang

[Bearbeiten] Zusammenfassung

In diesem Kapitel lernten wir Trigger als automatisch ausgeführte Prozeduren kennen:

• 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.

Hoch zum Seitenanfang

[Bearbeiten] Übungen

Hoch zum Seitenanfang

Inhaltsverzeichnis: Erweiterungen Zurück zu: Prozeduren Weiter zu: Änderung der Datenbankstruktur