Manuel de référence MySQL 5.0

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

Un déclencheur est un objet de base de données associé à une table, qui s'active lorsqu'un événement particulier survient.

Le déclencheur est associé à la table appelée tbl_name. tbl_name qui doit faire référence à une table permanente. Vous ne pouvez pas associer un déclencheur à une table TEMPORARY ou une vue.

trigger_time est le moment d'action du déclencheur. Il peut être BEFORE (avant) ou AFTER (après), pour indiquer que le déclencheur s'active avant ou après la commande qui le déclenche.

trigger_event indique le type de commande qui active le déclencheur. Il peut valoir INSERT, UPDATE ou DELETE. Par exemple, un déclencheur BEFORE pour une commande INSERT peut être utilisé pour vérifier les valeurs avant leur insertion dans la table.

Il ne peut pas y avoir deux déclencheurs pour une même table avec les mêmes configurations de moment et de commande. Par exemple, vous ne pouvez pas avoir deux déclencheurs BEFORE UPDATE pour la même table. Mais vous pouvez avoir un déclencheur BEFORE UPDATE et un déclencheur BEFORE INSERT, ou un déclencheur BEFORE UPDATE et un déclencheur AFTER UPDATE.

trigger_stmt est la commande à exécuter lorsque le déclencheur s'active. Si vous voulez utiliser plusieurs commandes, utilisez les agrégateurs BEGIN ... END. Cela vous permet aussi d'utiliser les mêmes codes que ceux utilisés dans des procédures stockées. Voir Section 19.2.7 : « La commande composée BEGIN ... END »19.2.7. La commande composée BEGIN ... END.

Note : actuellement, les déclencheurs ont les mêmes limitations que les procédures stockées : ils ne peuvent pas contenir de références directes aux tables via leurs noms. Cette limitation sera levée dès que possible.

Cependant, dans la commande d'activation d'un déclencheur, vous pouvez faire référence aux colonnes dan la table associée au déclencheur en utilisant les mots OLD et NEW. OLD.col_name faire référence à une colonne d'une ligne existante avant sa modification ou son effacement. NEW.col_name fait référence à une colonne d'une ligne après insertion ou modification.

L'utilisation de SET NEW.col_name = value requiert le droit de UPDATE sur la colonne. L'utilisation de SET value = NEW.col_name requiert le droit de SELECT sur la colonne.

La commande CREATE TRIGGER requiert le droit de SUPER. Elle a été ajoutée en MySQL 5.0.2.

DROP TRIGGER tbl_name.trigger_name

Supprime un déclencheur. Le nom du déclencheur doit inclure le nom de la table, car chaque déclencheur est associé à une table particulière.

La commande DROP TRIGGER requiert le droit de SUPER. Il a été ajouté en MySQL 5.0.2.

Le support des déclencheurs (aussi appelés trigger) a commencé avec MySQL 5.0.2. Actuellement, le support des déclencheurs est rudimentaire, et il existe des limitations dans les fonctionnalités. Cette section présente comment utiliser les déclencheurs et quelles sont leurs limitations actuelles.

Un déclencheur est un objet de base de données qui est associé à une table, et qui s'active lorsqu'un événement spécifié survient dans la table. Il est possible d'utiliser les déclencheurs pour effectuer des vérifications de valeurs avant insertion, ou pour effectuer des calculs de macrodonnées après une modification d'une table.

Un déclencheur est associé à une table, et est défini pour s'activer lorsqu'une commande INSERT, DELETE ou UPDATE s'exécute sur la table. Un déclencheur peut être configuré pour s'activer avant ou après l'événement. Par exemple, déclencheur peut être appelé avant que la ligne soit effacée ou modifié dans la table.

Pour créer un déclencheur ou l'effacer, utilisez les commandes CREATE TRIGGER ou DROP TRIGGER. La syntaxe de ces commandes est décrite dans les sections Section 20.1 : « Syntaxe de CREATE TRIGGER »20.1. Syntaxe de CREATE TRIGGER et Section 20.2 : « Syntaxe de DROP TRIGGER »20.2. Syntaxe de DROP TRIGGER.

Voici un exemple simple qui associe un déclencheur à une table pour les commandes INSERT. Il sert d'accumulateur des sommes insérées dans une des colonnes de la table.

La commande suivante crée la table et le déclencheur :

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;

La commande CREATE TRIGGER crée un déclencheur appelé ins_sum qui est associé avec la table account. Il inclut aussi des clauses pour spécifier le moment d'activation, l'événement et l'action du déclencheur :

• le mot réservé BEFORE (avant, en anglais) indique le moment d'activation. Dans ce cas, le déclencheur sera activé avant l'insertion des lignes dans la table. L'autre mot réservé est AFTER (Après, en anglais) ;
• le mot réservé INSERT indique l'événement qui active le déclencheur. Dans l'exemple, le déclencheur s'active lors des commandes INSERT. Vous pouvez créer des déclencheur pour les commandes DELETE et UPDATE ;
• la commande qui suit le mot clé FOR EACH ROW définit la commande à exécuter à chaque fois que le déclencheur s'active, ce qui arrive dès qu'une ligne est insérée. Dans l'exemple, la commande du déclencheur est un simple SET qui accumule la somme des valeurs insérées dans les colonnes amount. La commande utilise la valeur de la colonne avec la syntaxe NEW.amount (en anglais, nouvelle.montant) ce qui signifie « la valeur de la colonne amount qui va être insérée ».

Pour utiliser le déclencheur, initialisez l'accumulateur à zéro, puis exécutez une commande INSERT et voyez la valeur finale de l'accumulateur :

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               |
+-----------------------+

Dans ce cas, la valeur de @sum après la commande INSERT est 14.98 + 1937.50 - 100 soit 1852.48.

Pour détruire un déclencheur, utilisez la commande DROP TRIGGER. Le nom du déclencheur doit inclure le nom de la table :

mysql> DROP TRIGGER account.ins_sum;

Comme le déclencheur est associé à une table, vous ne pouvez pas avoir plusieurs déclencheurs sur une même table qui portent le même nom. Soyez aussi conscients que l'espace de noms des déclencheurs risque de changer à l'avenir. C'est-à-dire que l'unicité des noms de déclencheurs par table risque d'être étendue à l'unicité de déclencheurs au niveau du serveur. Pour faciliter la compatibilité ascendante, essayez d'utiliser des noms de déclencheurs qui soient uniques dans toute la base.

En plus du fait que les noms de déclencheurs doivent être uniques pour une table, il y a d'autres limitations sur le type de déclencheurs que vous pouvez mettre en place. En particulier, vous ne pouvez pas avoir deux déclencheurs qui ont le même moment d'activation et le même événement d'activation. Par exemple, vous ne pouvez pas définir deux déclencheurs BEFORE INSERT et deux déclencheurs AFTER UPDATE pour la même table. Ce n'est probablement pas une limitation importante, car il est possible de définir un déclencheur qui exécute plusieurs commandes en utilisant une commande complexe, encadrée par les mots BEGIN ... END, après le mot clé FOR EACH ROW. Un exemple vous est présenté ultérieurement dans cette section.

Il y a aussi des limitations dans ce qui peut apparaître dans la commande que le déclencheur peut exécuter lorsqu'il est activé :

• le déclencheur ne peut pas faire référence directe aux tables par leur nom, y compris la table à laquelle il est associé. Par contre, vous pouvez utiliser les mots clés OLD (ancien en anglais) et NEW (nouveau en anglais). OLD fait référence à la ligne existante avant la modification ou l'effacement. NEW faire référence à la nouvelle ligne insérée ou à la ligne modifiée ;
• le déclencheur ne peut pas exécuter de procédures avec la commande CALL. Cela signifie que vous ne pouvez pas contourner le problèmes des noms de tables en appelant une procédure stockée qui utilise les noms de tables ;
• le déclencheur ne peut pas utiliser de commande qui ouvre ou ferme une transaction avec START TRANSACTION, COMMIT ou ROLLBACK.

Les mots clé OLD et NEW vous permette d'accéder aux colonnes dans les lignes affectées par le déclencheur. OLD et NEW ne sont pas sensibles à la casse. Dans un déclencheur INSERT, seul NEW.col_name peut être utilisée : il n'y a pas d'ancienne ligne. Dans un déclencheur DELETE, seule la valeur OLD.col_name peut être utilisée : il n'y a pas de nouvelle ligne. Dans un déclencheur UPDATE, vous pouvez utiliser OLD.col_name pour faire référence aux colonnes dans leur état avant la modification, et NEW.col_name pour faire référence à la valeur après la modification.

Une colonne identifiée par OLD est en lecture seule. Vous pouvez lire sa valeur, mais vous ne pouvez pas la modifier. Une colonne identifiée avec la valeur NEW peut être lue si vous avez les droits de SELECT dessus. Dans un déclencheur BEFORE, vous pouvez aussi changer la valeur avec la commande SET NEW.col_name = value si vous avez les droits de UPDATE. Cela signifie que vous pouvez utiliser un déclencheur pour modifier les valeurs insérées dans une nouvelle ligne ou les valeurs modifiées.

Dans un déclencheur BEFORE, la valeur NEW d'une colonne AUTO_INCREMENT vaut 0, et non pas le nombre séquentiel automatiquement généré, car ce nombre sera généré lorsque la ligne sera réellement insérée.

OLD et NEW sont des extensions de MySQL aux déclencheurs.

En utilisant la syntaxe BEGIN ... END, vous pouvez définir un déclencheur qui exécute plusieurs commandes. À l'intérieur d'un bloc BEGIN, vous pouvez aussi utiliser les autres syntaxes autorisées dans les routines stockées, telles que les conditions et les boucles. Cependant, tout comme pour les procédures stockées, lorsque vous définissez un déclencheur qui s'exécute sur plusieurs commandes, il est nécessaire de redéfinir le délimiteur de commandes si vous saisissez le déclencheur en ligne de commande tel que mysql pour que vous puissiez utiliser le caractère ';' à l'intérieur de la définition. L'exemple ci-dessous illustre ces points. Il définit un déclencheur UPDATE qui vérifie la valeur d'une ligne avant sa modification, et s'arrange pour que les valeurs soient dans l'intervalle de 0 à 100. Cela doit être fait avant (BEFORE) la modification, pour que la valeur soit vérifiée avant d'être utilisée :

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 ;

Il vous viendra sûrement à l'esprit qu'il serait plus facile de définir une procédure stockée séparément, pour l'invoquer depuis le déclencheur grâce à un simple appel à CALL. Cela serait sûrement avantageux si vous voulez appeler la même routine depuis plusieurs déclencheurs. Cependant, les déclencheurs ne peuvent pas utiliser la commande CALL. Vous devez absolument réécrire les commandes composées de chaque commande CREATE TRIGGER que vous voulez utiliser.