Les triggers sous MySQL


Objectif :

L’objectif de cet article est de voir ensemble comment créer, lister, exécuter et supprimer des triggers sous MySQL

Qu’est-ce qu’un trigger (ou déclencheur) ?

Un trigger est un objet d’une base de données lié à une table et défini pour s’activer lors d’un événement. Cet événément peut être une mise à jour d’une table, une suppression dans une table ou bien une insertion. Cet objet contiendra du code SQL qui s’exécutera lors de la survenue de l’événement. Ainsi, contrairement à une procédure stockée qui peut être appelée à tout moment, un trigger ne peut être appelé explicitement.

Base de test

Nous allons travailler sur la base de données world qui est une base de données récupérable à cette adresse :

https://dev.mysql.com/doc/index-other.html

Pour l’installer, il suffit de télécharger le fichier Zip ou Gzip, de décompresser le fichier et ensuite d’exécuter la requête suivante dans le dossier où à eu lieu la décompression :

mysql -utest -p < world.sql

Cette base de données est consistué de 3 tables dont voici un descriptif :

Afin de ne pas modifier les tables original, nous allons créer une nouvelle table capital et une copie de la table countrylanguage en modifiant le type de la colonne **IsOfficial :

CREATE TABLE capital
SELECT country.Name AS country, city.Name AS capital,
city.Population FROM country, city
WHERE country.Capital = city.ID;

CREATE TABLE countrylanguage2 select * from countrylanguage;
alter table countrylanguage2 modify IsOfficial char(10) DEFAULT '';

Suite à cette requête vous deviez avoir :

Pourquoi utiliser les triggers ?

Il y a plusieurs raison à l’utilisation des triggers :
1) Contraintes et vérifications de données : un trigger va pouvoir avant l’insertion ou la mise à jour de ligne vérifier et corriger les valeurs d’une colonne si elle ne satisfont pas à la liste des valeurs possibles. Au lieu de corriger, nous allons également pouvoir faire échouer la requête.
Si on veut par exemple avoir une colonne qui ne contient pas de valeurs négatives, nous pouvons à l’aide d’un trigger mettre la valeur à 0 à chaque fois que quelqu’un essaie d’insérer un chiffre négatif.
2) Historisation des actions : Certaines fois, il est important de savoir qui a modifié ou supprimé telle ligne. A l’aide d’un trigger cela devient trivial. A chaque suppression ou modification de ligne, un trigger se déclenchera et remplira une table contenant les informations nécessaire à l’historisation des actions.

Syntaxe de la création d’un trigger

Un trigger est un objet lié à une table mais contenu dans une base de données spécifique. Ainsi, au sein d’une même base de données, deux triggers ne peux avoir les mêmes noms.

Comme il a été dit précédemment, un trigger s’activera lors d’une insertion (INSERT), d’une mise à jour (UPDATE) ou d’une supprression (DELETE) dans une table. Plus exactement, un trigger s’activera soit avant (BEFORE) soit après (AFTER) l’événement.

Avant la version 5.7.2 de MySQL, il n’était possible de définir que 6 triggers pour une table (un trigger par couple événement / moment d’exécution). A partir de la version 5.7.2 , il est possible de définir plus que 6 triggers pour une table donnée.

Voici la syntaxe de la création d’un trigger :

Delimiter //
CREATE TRIGGER nom_trigger
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON nom_table
FOR EACH ROW
BEGIN
/* instruction sql
*/
END
//
Delimiter ;

Nous supposons que l’on créé le trigger au sein du client mysql, pour cela il faut changer le délimiteur par l’instruction :

    DELIMITER //

Pour signifier la fin du trigger, il suffit de saisir de nouveau le délimiteur :

//

Et ne pas oublier à la fin de remettre le délimiteur par défaut de MySQL par l’instruction :

    DELIMITER ;

Ceci est nécessaire car les instructions sql se termineront par ; , ce qui provoquera un arrêt de l’exécution lors du premier ; rencontré si on ne change pas le délimiteur.

En dehors de mysql cela n’est pas nécessaire de changer de délimiteur.

Le blog BEGIN…END n’est pas obligatoire si le trigger ne contient qu’une instruction sql.

FOR EACH ROW : signifie littéralement “pour chaque ligne”, sous-entendu “pour chaque ligne insérée/supprimée/modifiée” selon ce qui a déclenché le trigger.

Il existe une convention quant à la manière de nommer ses triggers, que je vous encourage à suivre : nom_trigger = moment_evenement_table. Donc le trigger BEFORE UPDATE ON capital aura pour nom : before_update_capital.

Cas pratique

Premier cas

Le trigger suivant, vérifie la valeur de la population avant l’insertion.
Si la valeur que l’on veut insérer est négative, alors cette valeur est mise à 0 ou sinon cette valeur est arrondi au multiple de 1000 le plus près.

CREATE TRIGGER before_insert_capital
BEFORE INSERT
ON capital
FOR EACH ROW
SET NEW.Population =
IF(NEW.Population < 0, 0, TRUNCATE(NEW.Population,-3));

Testons ce trigger avec la requête d’insertion suivante :

INSERT INTO capital VALUES
('CountryA','CityA',-39),
('CountryB','CityB',123456);

A présent, vérifions que notre trigger à bien fonctionné :

SELECT * FROM capital WHERE Country IN ('CountryA','CountryB');

Nous avons bien 0 pour le pays CountryA et 123000 pour le pays CountryB , ainsi le trigger a bien fonctionné.

Deuxième cas

Dans le deuxième cas, nous allons faire en sorte de n’avoir que pour valeurs ‘T’, ‘F’ et NULL dans la colonne IsOfficial de la table countrylanguage2 lors de mise à jour. Pour cela définissons trigger suivant :

DELIMITER //
CREATE TRIGGER before_update_countrylanguage2 BEFORE UPDATE
ON countrylanguage2 FOR EACH ROW
BEGIN
    IF NEW.IsOfficial IS NOT NULL
    AND NEW.IsOfficial != 'T'
    AND NEW.IsOfficial != 'F' 
      THEN
        SET NEW.IsOfficial = NULL;
    END IF;
END //
DELIMITER ;

Nous pouvons tester notre trigger en effectuant une mise à jour avec une valeur différente de ‘T’, ‘F’ et NULL :

UPDATE countrylanguage2
SET IsOfficial = 'A'
WHERE CountryCode = 'ZWE';

Normalement le trigger s’est déclenché et nous devrions avoir des NULL pour la colonne IsOfficial pour les lignes done le countrycode est égal à ‘ZWE’

SELECT * FROM countrylanguage2;

Si nous voulons réaliser un trigger qui se déclenche avant insertion et qui génère une erreur si la colonne IsOfficial ne contient pas à la bonne valeur (‘T’ ou ‘F’ ou NULL), nous pouvons procéder ainsi :

-- Création de la table Erreur
CREATE TABLE Erreur (
    id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    erreur VARCHAR(255) UNIQUE);

-- Insertion de l'erreur qui nous intéresse
INSERT INTO Erreur (erreur) VALUES ('Erreur : la colonne IsOfficial doit valoir "T", "F" ou NULL.');

-- Création du trigger
DELIMITER //
CREATE TRIGGER before_insert_countrylanguage2 BEFORE INSERT
ON countrylanguage2 FOR EACH ROW
BEGIN
    IF NEW.IsOfficial IS NOT NULL   -- IsOfficial n'est ni NULL
    AND NEW.IsOfficial != 'T'       -- ni "T"
    AND NEW.IsOfficial != 'F'       -- ni "F"
      THEN
        INSERT INTO Erreur (erreur) VALUES ('Erreur : la colonne IsOfficial doit valoir "T", "F" ou NULL.');
    END IF;
END //
DELIMITER ;

Testons à présent notre trigger

INSERT INTO countrylanguage2 (CountryCode, Language, IsOfficial, Percentage)
VALUES ('ZWE', 'English', 'A', 3.1);

Nous voyons bien notre erreur s’afficher, donc le trigger fonctionne, la ligne n’est pas inséré si IsOfficial ne vaut pas ‘T’, ‘F’ ou NULL.

Troisième cas : Historisation basique

On va utiliser cette historisation pour la table countrylanguage2 que l’on va modifier pour rajouter les informations d’historisation.

On ajoute donc quatre colonnes à la table. Ces colonnes seront toujours remplies automatiquement par les triggers.

-- On modifie la table countrylanguage2
ALTER TABLE countrylanguage2 
    ADD COLUMN date_insertion DATETIME, -- date d'insertion
    ADD COLUMN utilisateur_insertion VARCHAR(20), -- utilisateur ayant inséré la ligne
    ADD COLUMN date_modification DATETIME, -- date de dernière modification
    ADD COLUMN utilisateur_modification VARCHAR(20); -- utilisateur ayant fait la dernière modification

Pour tester, nous donc à présent remplir ces 4 colonnes avec quelques données :

-- On remplit les colonnes
UPDATE countrylanguage2 
SET date_insertion = NOW() - INTERVAL 1 DAY, 
    utilisateur_insertion = 'Test', 
    date_modification = NOW()- INTERVAL 1 DAY, 
    utilisateur_modification = 'Test';

Définissons les triggers (un avant insertion et un avant mis à jour) :

DROP TRIGGER before_insert_countrylanguage2;
DELIMITER //
CREATE TRIGGER before_insert_countrylanguage2 BEFORE INSERT
ON countrylanguage2 FOR EACH ROW
BEGIN
    SET NEW.date_insertion = NOW();
    SET NEW.utilisateur_insertion = CURRENT_USER();
    SET NEW.date_modification = NOW();
    SET NEW.utilisateur_modification = CURRENT_USER();
END //

DROP TRIGGER before_update_countrylanguage2;
CREATE TRIGGER before_update_countrylanguage2 BEFORE UPDATE
ON countrylanguage2 FOR EACH ROW
BEGIN
    SET NEW.date_modification = NOW();
    SET NEW.utilisateur_modification = CURRENT_USER();
END //
DELIMITER ;

Les triggers mettent simplement à jour les colonnes servant à identifier quand et qui à créé une ligne ou modifié une ligne.

Testons nos deux triggers :

INSERT INTO countrylanguage2 (CountryCode, Language, IsOfficial, Percentage)
VALUES ('ZWE', 'Francais', 'T', 5.3);

UPDATE countrylanguage2 
SET percentage = 5.2 
WHERE CountryCode = 'ZWE' AND Language = 'Francais';

Nous voyons que suite à une insertion, nous avons bien le remplissage automatique des 4 colonnes :

De même que pour une mise à jour nous avons bien une modification de la colonne date_modification .

Restrictions sur les triggers

Au sein des triggers, certains restrictions existent :
1) Nous ne pouvons pas utiliser le mot clé CALL , ce mot-clé est utilisé pour appeler une procédure stockée. Ainsi, nous ne pouvons pas appeler de procédure stockée au sein d’un trigger.
2) Nous ne pouvons créer de trigger pour des tables temporaires
3) Nous ne pouvons pas au sein de la définition d’un trigger démarrer une transaction SQL. Ainsi les mots-clés suivants sont interdit : START TRANSACTION, COMMIT ou ROLLBACK
4) il est impossible de modifier les données d’une table utilisée par la requête ayant déclenché le trigger à l’intérieur de celui-ci.
5) Une suppression ou modification de données déclenchée par une clé étrangère ne provoquera pas l’exécution du trigger correspondant.

Référence à OLD et NEW

Dans le corps du trigger, MySQL met à disposition deux mots-clés : OLD et NEW.

1) 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.
2) 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.

Il n’y a que dans le cas d’un trigger UPDATE que OLD et NEW coexistent.
Lors d’une insertion, OLD n’existe pas, puisque la ligne n’existe pas avant l’événement déclencheur.
Dans le cas d’une suppression, c’est NEW qui n’existe pas, puisque la ligne n’existera plus après l’événement déclencheur.

Suppression des triggers

Pour détruire un trigger, il suffit d’exécuter la requête suivante :

DROP TRIGGER nom_base.nom_trigger;

nom_base n’est pas obligatoire si la base de données par défaut est celle qui contient le trigger à détruire.

Ainsi pour détruire le trigger capital_bi, voici comment procéder :

DROP TRIGGER world.before_insert_capital;

Tout comme pour les procédures stockées, il n’est pas possible de modifier un trigger. Il faut le supprimer puis le recréer différemment.

Par ailleurs, si l’on supprime une table, on supprime également tous les triggers qui y sont attachés.

Lister les triggers existant sous MySQL :

Voici la requête pour avoir la liste des triggers. Cette requête utilise la table triggers de la base de données information_schema :

SELECT trigger_schema,trigger_name,event_object_table,event_manipulation,action_timing,action_statement from information_schema.triggers G

Voici un échantillon de ce que j’ai en local :

© 2016 - 2017 réalisé par Benjamin LOMBARD