Dieser Abschnitt beschreibt die Verwendung von Triggern in MySQL 5.1 sowie einige Einschränkungen für die Nutzung von Triggern. Weitere Beschränkungen im Zusammenhang mit Triggern sind in Abschnitt I.1, „Beschränkungen bei gespeicherten Routinen und Triggern“, geschildert.

Ein Trigger ist ein benanntes Datenbankobjekt, das mit einer Tabelle verbunden ist und aktiviert wird, wenn für diese Tabelle ein bestimmtes Ereignis eintritt. Trigger werden beispielsweise verwendet, um Werte zu überprüfen, die in eine Tabelle eingesetzt werden sollen, oder um Berechnungen mit Werten auszuführen, die zu einem Update gehören.

Ein Trigger ist mit einer Tabelle verbunden und wird aktiviert, wenn eine INSERT-, DELETE- oder UPDATE-Anweisung für diese Tabelle ausgeführt wird. Ein Trigger kann so eingestellt werden, dass er entweder vor oder nach der auslösenden Anweisung aktiviert wird. So können Sie beispielsweise veranlassen, dass ein Trigger jeweils vor jeder Zeilenlöschung oder nach jeder Zeilenänderung aktiviert wird.

Ein Trigger wird mit CREATE TRIGGER erstellt und mit DROP TRIGGER gelöscht. Die Syntax dieser Anweisungen wird unter Abschnitt 20.1, „CREATE TRIGGER“, und Abschnitt 20.2, „DROP TRIGGER“, erklärt.

Das folgende einfache Beispiel verbindet einen Trigger mit einer Tabelle für den Fall von INSERT-Anweisungen. Er fungiert als Sammelbecken, um die Werte zu addieren, die in eine der Tabellenspalten eingefügt werden.

Die folgenden Anweisungen legen eine Tabelle und einen Trigger für sie an:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;

Die CREATE TRIGGER-Anweisung erzeugt einen Trigger namens ins_sum, der mit der account-Tabelle verbunden ist. Außerdem enthält sie Klauseln, um die Aktivierungszeit des Triggers, das Trigger-Ereignis und die eigentlichen Trigger-Aktionen festzulegen:

Um den Trigger verwenden zu können, setzen Sie die Variable, welche die Werte sammelt, auf null, führen eine INSERT-Anweisung aus und schauen, welchen Wert die Variable danach hat:

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48               |
+-----------------------+

In diesem Fall hat @sum nach Ausführung der INSERT-Anweisung den Wert 14.98 + 1937.50 - 100, also 1852.48.

Gelöscht wird der Trigger mit der DROP TRIGGER-Anweisung. Wenn der Trigger nicht im Standardschema definiert ist, müssen Sie außerdem den Schemanamen dazusetzen:

mysql> DROP TRIGGER test.ins_sum;

Da Trigger-Namen im Schema-Namensraum liegen, müssen alle Trigger innerhalb eines Schemas eindeutige Namen haben. Trigger in unterschiedlichen Schemata können dagegen gleich heißen.

Zusätzlich zu dem Erfordernis, dass Trigger-Namen in einem Schema eindeutig sein müssen, gibt es noch weitere Einschränkungen betreffend die Typen der Trigger. Insbesondere dürfen keine zwei Trigger einer Tabelle dieselbe Aktivierungszeit und dasselbe Aktivierungsereignis haben. Sie können also nicht zwei BEFORE INSERT-Trigger oder zwei AFTER UPDATE-Trigger für dieselbe Tabelle definieren. Normalerweise dürfte diese Beschränkung jedoch keine Rolle spielen, da ein Trigger auch so definiert werden kann, dass er mehrere Anweisungen in einem BEGIN ... END-Block als zusammengesetzte Anweisung hinter dem FOR EACH ROW ausführt. (Weiter unten in diesem Abschnitt finden Sie ein Beispiel dazu.)

Mit den Schlüsselwörtern OLD und NEW können Sie auf Spalten zugreifen, die von einem Trigger betroffen sind. (Die Groß- und Kleinschreibung spielt für OLD und NEW keine Rolle.) In einem INSERT-Trigger kann nur NEW.col_name benutzt werden, da keine alte Zeile vorhanden ist, und in einem DELETE-Trigger nur OLD.col_name, da keine neue Zeile vorhanden ist. In einem UPDATE-Trigger können sowohl OLD.col_name für den Verweis auf Tabellenzeilen vor der Änderung als auch NEW.col_name für einen Verweis auf die geänderten Zeilen verwendet werden.

Ein Spaltenname mit OLD ist schreibgeschützt. Sie können ihn benutzen (sofern Sie das SELECT-Recht für ihn haben), aber nicht ändern. Ein Spaltenname mit NEW kann mit dem entsprechenden SELECT-Recht angesprochen werden. In einem BEFORE-Trigger können Sie auch seinen Wert mit SET NEW.col_name = value ändern, sofern Sie das UPDATE-Recht dafür haben. Dies bedeutet, dass Sie einen Trigger einsetzen können, um die Werte zu ändern, die in eine neue Zeile eingefügt oder mit denen eine Zeile aktualisiert wird.

In einem BEFORE-Trigger ist der NEW-Wert für eine AUTO_INCREMENT-Spalte 0 und nicht die automatisch generierte laufende Nummer, die angelegt wird, wenn der neue Datensatz tatsächlich eingefügt wird.

OLD und NEW sind MySQL-Erweiterungen für Trigger.

Mit dem BEGIN ... END-Konstrukt können Sie einen Trigger definieren, der mehrere Anweisungen ausführt. Innerhalb des BEGIN-Blocks können Sie auch eine andere für gespeicherte Routinen zulässige Syntax verwenden, wie beispielsweise Bedingungsanweisungen und Schleifen. Doch wie für gespeicherte Routinen gilt auch für Trigger: Wenn Sie das mysql-Programm verwenden, um einen Trigger für mehrere Anweisungen zu definieren, muss das Trennzeichen für die mysql-Anweisung auf etwas anderes eingestellt werden, damit das Begrenzungszeichen ; für Anweisungen in der Trigger-Definition zur Verfügung steht. Dies wird im folgenden Beispiel deutlich, in dem ein UPDATE-Trigger definiert wird, der für jede Zeilenänderung den neuen Wert prüft und diesen so modifiziert, dass er im Bereich zwischen 0 und 100 liegt. Dieser Trigger muss ein BEFORE-Trigger sein, da der Wert überprüft werden muss, bevor er in die Zeile eingesetzt wird:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;

Unter Umständen ist es einfacher, eine gespeicherte Prozedur separat zu definieren und dann im Trigger mit einer einfachen CALL-Anweisung aufzurufen. Dies ist auch dann von Vorteil, wenn Sie dieselbe Routine in mehreren Triggern aufrufen möchten.

In Anweisungen, die ein aktivierter Trigger ausführt, ist nicht alles erlaubt:

Mit Fehlern bei der Ausführung eines Triggers geht MySQL folgendermaßen um: