Les triggers de base de données MySQL

« Un trigger est un objet de base de données nommé, qui est associé à une table et qui s’active lorsqu’un événement particulier survient dans une table »

Un Trigger est un bloc d’instructions SQL :

• Il est rattaché à une seule table
• Il réagit à 3 événements : INSERT, UPDATE, DELETE
• Il s’exécute soit avant soit après un des ces événements
• L’accession aux données modifiées via les événements est possible avec les mots-clés New.nom_colonne ou OLD.nom_colonne

Une table peut avoir 6 triggers Maximum

• Log des utilisateurs/changements
• Exécuter des règles métier
• Renforcer les contraintes d’intégrité
• Dupliquer des données
• Améliorer les performances et les changements

• Chaque trigger est associé à une table et a un nom et une fonction uniques basés sur deux facteurs :
1. Temps . BEFORE ou AFTER un événement de ligne spécifique.
2. Événement . INSERT, UPDATE ou DELETE.

         CREATE [OR REPALCE] TRIGGER nomtrigger
          {BEFORE [ AFTER] INSERT OR UPDATE OR DELETE}
          ON Nomdetable [FOR EACH ROW] [WHEN condition]
         BLOC PL/SQL 

Le nom du trigger doit être unique dans la base de données.

L’option BEFORE /AFTER indique le moment du déclenchement du trigger.

Les instructions SQL INSERT OR UPDATE OR DELETE peuvent être toutes présentes comme on peut en avoir juste une.

Pour un UPDATE, on peut spécifier une liste de colonnes. Dans ce cas, le trigger ne se déclenchera que s’il porte sur l’une des colonnes précisées dans la liste.

DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;

• DELIMITER // : le délimiteur MySQL par défaut est ; — il est nécessaire de le changer en autre chose afin que MySQL traite les lignes suivantes comme une seule commande jusqu’à ce qu’il atteigne votre délimiteur personnalisé. Dans cet exemple, le délimiteur est changé en // et ensuite le délimiteur ; est redéfini à la fin.
• [TRIGGER_NAME] : Un trigger doit avoir un nom et c’est là que vous incluez la valeur.
• [TRIGGER TIME] : Un trigger peut être invoqué à différents moments. MySQL vous permet de définir si le trigger démarrera avant ou après une opération de base de données.
• [TRIGGER EVENT]: Les triggers ne sont appelés que par les opérations INSERT, UPDATE et DELETE. Vous pouvez utiliser n’importe quelle valeur ici en fonction de ce que vous voulez réaliser.
• [TABLE]: Tout trigger que vous créez sur votre base de données MySQL doit être associé à une table.
• FOR EACH ROW : Cette instruction ordonne à MySQL d’exécuter le code du trigger pour chaque ligne que ce dernier affecte.
• [TRIGGER BODY] : le code qui est exécuté lorsque le trigger est invoqué est appelé_ trigger body​​_​. Ce peut être une seule instruction SQL ou plusieurs commandes. Notez que si vous exécutez plusieurs instructions SQL sur le trigger body, vous devez les inclure dans un bloc BEGIN… END.

Le CREATE OR REPLACE n’est pas supporté par MySQL

DELIMITER $$
DROP TRIGGER IF EXISTS myTrigger;
$$
CREATE TRIGGER myTrigger
-- ...

$$
DELIMITER ;

• vous perdrez l’ancien déclencheur si le CREATE échoue
• sur une charge lourde, d’autres opérations peuvent se LOCK TABLES myTable WRITE; entre le DROP et le CREATE , utilisez un LOCK TABLES myTable WRITE; tout d’abord pour éviter les incohérences de données et UNLOCK TABLES; après le CREATE pour libérer la table

• OLD : représente les valeurs des colonnes de la ligne traitée avant qu’elle ne soit modifiée par l’événement déclencheur. Ces valeurs peuvent être lues, mais pas modifiées.
• NEW : représente les valeurs des colonnes de la ligne traitée après qu’elle a été modifiée par l’événement déclencheur. Ces valeurs peuvent être lues et modifiées.

• Pour inserer une ligne. Exécuter la commande suivante :

INSERT INTO Adoption (client_id, animal_id, date_reservation, prix, paye) VALUES (12, 15, NOW(), 200.00, FALSE);

• Pendant le traitement de cette ligne par le trigger correspondant,

— NEW.client_id vaudra 12 ;
— NEW.animal_id vaudra 15 ;
— NEW.date_reservation vaudra NOW() ;
— NEW.date_adoption vaudra NULL ;
— NEW.prix vaudra 200.00 ;
— NEW.paye vaudra FALSE (0).

• Les valeurs de OLD ne seront pas définies. Dans le cas d’une suppression, on aura exactement l’inverse.

UPDATE Adoption SET paye = TRUE WHERE client_id = 12 AND animal_id = 15;

— NEW.paye vaudra TRUE, tandis que OLD.paye vaudra FALSE.
— Par contre les valeurs respectives de NEW.animal_id, NEW.client_id, NEW.date_reservation, NEW.date_adoption et NEW.prix seront les mêmes que OLD.animal_id, OLD.client_id, OLD.date_reservation, OLD.date_adoption et OLD.prix, puisque ces colonnes ne sont pas modifiées par la requête.

• Un trigger au niveau de la ligne est activé pour chaque ligne insérée, mise à jour ou supprimée. Par exemple, si une table a 100 lignes insérées, mises à jour ou supprimées, le trigger est automatiquement invoqué 100 fois pour les 100 lignes affectées.
• Un trigger au niveau de l’instruction est exécuté une fois pour chaque transaction, quel que soit le nombre de lignes insérées, mises à jour ou supprimées.

Un trigger se compose donc de trois partie :

1. l’événement déclencheur (after/before insert/update/delete)
2. la condition supplémentaire (when)
3. l’action à mener (le bloc begin/end)

• Le fichier tablename.TRG associe le trigger à la table correspondante
• Le fichier triggername.TRN contient la définition du trigger qui sera exécuté une fois qu’un événement se produit

• Les triggers offrent un autre moyen de vérifier l’intégrité des données.
• Les triggers gèrent les erreurs de la couche de base de données.
• Les triggers offrent un autre moyen d’ exécuter des tâches planifiées . En utilisant des triggers, vous n’avez pas à attendre que les événements planifiés s’exécutent car les triggers sont appelés automatiquement avant ou après qu’une modification est apportée aux données d’une table.
• Les triggers peuvent être utiles pour auditer les modifications de données dans les tables.

• Les triggers ne peuvent fournir que des validations étendues, pas toutes les validations. Pour des validations simples, vous pouvez utiliser les contraintes NOT NULL, UNIQUE, CHECK et FOREIGN KEY.
• Les triggers peuvent être difficiles à dépanner car ils s’exécutent automatiquement dans la base de données, ce qui peut ne pas être invisible pour les applications clientes.
• Les triggers peuvent augmenter la surcharge du serveur MySQL.