Apprendre SQL : Déclencheurs (Triggers)

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

Un déclencheur (appelé « trigger » en anglais) en SQL est un bloc de code SQL qui s’exécute automatiquement en réponse à certaines actions ou événements, tels que :

• L’insertion de données
• La mise à jour de données
• La suppression de données

Les triggers sont utilisés pour automatiser des tâches ou appliquer des règles métier dans une base de données.

Par exemple, je souhaite déclencher un trigger :

• Après qu’un enregistrement soit ajouté dans la table X
• Avant qu’un enregistrement soit supprimé dans la table Y

Créer un trigger

Il est temps de se pencher sur la syntaxe permettant de créer ses propres triggers pour le SGBD MySQL.

1. Délimiteurs

Un trigger va contenir des scripts SQL, au même titre qu’une UDF ou procédure stockée, il est alors important de modifier temporairement le délimiteur ;.

DELIMITER //

-- Mon trigger ici...

DELIMITER ;

2. Déclaration du trigger

On crée un trigger avec :

1. La commande CREATE TRIGGER
2. Suivie de son nom

DELIMITER //

CREATE TRIGGER ACTUALIZE_DATE

//

DELIMITER ;

De la même manière que pour les fonctions, l’UPPERCASE est à privilégier.

3. Condition de déclenchement

Le déclenchement d’un trigger est conditionné par 3 éléments :

1. Si le trigger doit être déclenché avant ou après l’évènement : BEFORE ou AFTER
2. Le type d’évènement : INSERT, UPDATE ou DELETE
3. La table concernée précédée de ON

DELIMITER //

CREATE TRIGGER ACTUALIZE_DATE
BEFORE UPDATE ON articles

//

DELIMITER ;

Ici, le déclencheur doit être exécuté avant une modification sur un enregistrement de la table articles.

4. FOR EACH ROW

La clause FOR EACH ROW est obligatoire lorsque vous créez un déclencheur dans MySQL.

Elle indique que le déclencheur doit être exécuté pour chaque ligne affectée par l’événement défini (chaque ligne insérée, mise à jour ou supprimée) dans la table sur laquelle le déclencheur est défini.

DELIMITER //

CREATE TRIGGER ACTUALIZE_DATE
BEFORE UPDATE ON articles
FOR EACH ROW

//

DELIMITER ;

5. Logique du trigger

La logique du trigger va être contenue entre les mots-clés BEGIN et END. Vous pouvez y mettre n’importe quelle instruction SQL valide.

DELIMITER //

CREATE TRIGGER ACTUALIZE_DATE
BEFORE UPDATE ON articles
FOR EACH ROW
BEGIN
	SET NEW.updated_at = NOW();
END

//

DELIMITER ;

Ici, avant la mise à jour d’un enregistrement, je définis la date actuelle dans la colonne updated_at grâce à la fonction NOW().

Les mots-clés OLD et NEW sont des pseudo-variables utilisées dans les déclencheurs (triggers) MySQL pour respectivement accéder aux anciennes et nouvelles valeurs des colonnes lors d’opérations d’insertion, de mise à jour et de suppression dans une table.

Les pseudo-variables OLD et NEW sont essentielles pour personnaliser le comportement des déclencheurs.

Voici un tableau détaillant leurs caractéristiques :

DELIMITER //

CREATE TRIGGER LOGGER
AFTER DELETE ON posts
FOR EACH ROW
BEGIN
	DECLARE log_message VARCHAR(255);
	SET log_message = CONCAT("Suppression de l'enregistrement N°", OLD.id, " dans la table « posts »");
	INSERT INTO logs (message, created_at) VALUES (log_message, NOW());
END

//

DELIMITER ;