CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

Diese Anweisung erzeugt einen neuen Trigger. Ein Trigger ist ein benanntes Datenbankobjekt, das mit einer Tabelle verbunden ist und aktiviert wird, wenn für diese Tabelle ein bestimmtes Ereignis eintritt. Gegenwärtig ist zur Ausführung von CREATE TRIGGER das TRIGGER-Recht für die Tabelle erforderlich, zu welcher der Trigger gehört. (Vor MySQL 5.1.6 war für diese Anweisung das SUPER-Recht notwendig.)

Der Trigger wird mit der Tabelle tbl_name verbunden, die eine permanante Tabelle sein muss. Mit einer als TEMPORARY definierten Tabelle oder View lässt sich kein Trigger verbinden.

Wenn der Trigger aktiviert wird, legt die DEFINER-Klausel fest, welche Rechte angewendet werden. Genaueres erfahren Sie weiter unten in diesem Kapitel.

Die trigger_time ist der Zeitpunkt der Trigger-Aktion. Sie kann BEFORE oder AFTER sein, je nachdem, ob der Trigger sich vor oder nach der Anweisung einschaltet, die ihn aktivierte.

Das trigger_event gibt an, welche Art von Anweisung den Trigger aktiviert. Das trigger_event kann eines der folgenden Ereignisse sein:

Es ist wichtig, zu verstehen, dass das trigger_event weniger eine Art von SQL-Anweisung ist, die den Trigger aktiviert, als vielmehr eine Art von Tabellenoperation. So wird beispielsweise ein INSERT-Trigger nicht nur von INSERT-Anweisungen, sondern auch von LOAD DATA-Anweisungen aktiviert, weil beide Anweisungen Zeilen in eine Tabelle einfügen.

Ein potenziell verwirrendes Beispiel dafür ist die Syntax von INSERT INTO ... ON DUPLICATE KEY UPDATE ...: Für jede Zeile wird ein BEFORE INSERT-Trigger aktiviert, gefolgt entweder von einem AFTER INSERT-Trigger oder von dem Triggerpaar aus BEFORE UPDATE und AFTER UPDATE, je nachdem, ob ein doppelter Schlüssel für die Zeile vorlag oder nicht.

Es dürfen keine zwei Trigger einer Tabelle dieselbe Aktionszeit und dasselbe Trigger-Ereignis haben. Zum Beispiel können Sie keine zwei BEFORE UPDATE-Trigger für eine Tabelle definieren. Sie können jedoch einen BEFORE UPDATE- und einen BEFORE INSERT-Trigger oder einen BEFORE UPDATE- und einen AFTER UPDATE-Trigger definieren.

trigger_stmt ist die Anweisung, die ausgeführt wird, wenn der Trigger in Aktion tritt. Wenn Sie mehrere Anweisungen ausführen möchten, verwenden Sie das Konstrukt BEGIN ... END für zusammengesetzte Anweisungen. So können Sie auch dieselben Anweisungen wie in gespeicherten Routinen verwenden. Siehe auch Abschnitt 19.2.5, „BEGIN ... END-Syntax für komplexe Anweisungen“.

Hinweis: Zurzeit werden Trigger nicht von kaskadierenden Fremdschlüsselaktionen aktiviert. Dieser Mangel wird jedoch so bald wie möglich behoben.

In MySQL 5.1 können Sie Trigger schreiben, die Direktverweise auf Tabellennamen enthalten, wie der Trigger testref im folgenden Beispiel:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

DELIMITER ;

INSERT INTO test3 (a3) VALUES 
  (NULL), (NULL), (NULL), (NULL), (NULL), 
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES 
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Angenommen, Sie setzen die folgenden Werte in die Tabelle test1 ein, wie hier gezeigt:

mysql> INSERT INTO test1 VALUES 
    -> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

Die Daten in den vier Tabellen stellen sich dann folgendermaßen dar:

mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)

Die Spalten der Subjekttabelle (der Tabelle, zu welcher der Trigger gehört) können Sie mit den Aliasnamen OLD und NEW ansprechen. OLD.col_name bezieht sich auf eine Spalte mit einer vorhandenen Zeile, bevor diese geändert oder gelöscht wurde, und NEW.col_name auf die Spalte mit einer neu eingefügten oder geänderten Zeile.

Die DEFINER-Klausel gibt an, welches MySQL-Konto zur Prüfung der Zugriffsberechtigungen bei Aktivierung des Triggers herangezogen wird. Wenn ein user-Wert angegeben ist, dann als MySQL-Konto im Format 'user_name'@'host_name' (dasselbe Format wird auch in der GRANT-Anweisung verwendet). Die Werte user_name und host_name sind beide obligatorisch. CURRENT_USER kann auch als CURRENT_USER() angegeben werden. Der Standardwert für DEFINER ist der Benutzer, der die CREATE TRIGGER-Anweisung ausführt. (Dies ist dasselbe wie DEFINER = CURRENT_USER.)

Wenn Sie die DEFINER-Klausel angeben, dürfen Sie den Wert auf kein anderes als Ihr eigenes Konto einstellen, wenn Sie nicht über das SUPER-Recht verfügen. Die zulässigen Werte für den DEFINER-Benutzer richten sich nach folgenden Regeln:

Hinweis: In älteren Versionen als MySQL 5.1.6 wird das SUPER-Recht für die Benutzung von CREATE TRIGGER verlangt, sodass für diese älteren Releases nur die zweite der oben genannten Regeln gilt. Seit der Version 5.1.6 ist SUPER nur noch erforderlich, wenn der DEFINER auf etwas anderes als das eigene Konto eingestellt werden soll.

MySQL prüft Trigger-Berechtigungen folgendermaßen:

DROP TRIGGER [schema_name.]trigger_name

Diese Anweisung löscht einen Trigger. Der Schemaname (Datenbank) ist optional. Wird kein Schema angegeben, wird der Trigger aus dem Standardschema gelöscht. DROP TRIGGER wurde in MySQL 5.0.2 hinzugefügt und erfordert das TRIGGER-Recht für die Tabelle, zu welcher der Trigger gehört. (Vor MySQL 5.1.6 war für diese Anweisung das SUPER-Recht erforderlich.)

Hinweis: Wenn Sie von einer älteren MySQL-Version als MySQL 5.0.10 auf 5.0.10 oder neuer aufrüsten (einschließlich aller MySQL 5.1-Releases), müssen Sie alle Trigger vor dem Upgrade löschen und danach wieder neu erstellen. DROP TRIGGER funktioniert nach dem Upgrade nicht mehr. Unter Abschnitt 2.10.1, „Upgrade von MySQL 5.0“, wird eine empfehlenswerte Upgrade-Prozedur beschrieben.

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: