Comment utiliser les déclencheurs MySQL

Présentation

Les déclencheurs MySQL appliquent des restrictions aux tables lors de l'ajout, de la mise à jour ou de la suppression de lignes de table.

Les colonnes dans MySQL appliquent une légère limitation de valeur. Par exemple, définir un type de données de colonne sur tiny int et non nul nécessite une petite entrée de valeur numérique. Néanmoins, davantage de restrictions sont nécessaires pour maintenir l'intégrité des données.

Ce didacticiel vous montre comment utiliser les déclencheurs MySQL et fournit des exemples pour chaque type de déclencheur.

Prérequis

Un système exécutant MySQL sur un serveur de base de données
Compte utilisateur MySQL avec privilèges root
Connaissance des commandes MySQL de base (consultez notre aide-mémoire téléchargeable sur les commandes MySQL)

Qu'est-ce qu'un déclencheur dans MySQL ?

Un déclencheur est un objet MySQL nommé qui s'active lorsqu'un événement se produit dans une table. Les déclencheurs sont un type particulier de procédure stockée associée à une table spécifique.

Les déclencheurs permettent d'accéder aux valeurs du tableau à des fins de comparaison à l'aide de NEW et OLD . La disponibilité des modificateurs dépend de l'événement déclencheur que vous utilisez :

Événement déclencheur	ANCIEN	NOUVEAU
INSÉRER	Non	Oui
MISE À JOUR	Oui	Oui
SUPPRIMER	Oui	Non

Vérifier ou modifier une valeur lors de la tentative d'insertion de données rend le NEW.<column name> modificateur disponible. C'est parce qu'une table est mise à jour avec un nouveau contenu. En revanche, un OLD.<column name> la valeur n'existe pas pour une instruction d'insertion car aucune information n'existe à sa place au préalable.

Lors de la mise à jour d'une ligne de tableau, les deux modificateurs sont disponibles. Il y a OLD.<colum name> données que nous voulons mettre à jour vers NEW.<column name> données.

Enfin, lors de la suppression d'une ligne de données, le OLD.<column name> le modificateur accède à la valeur supprimée. Le NEW.<column name> n'existe pas car rien ne remplace l'ancienne valeur lors de la suppression.

Exemple de déclencheur MySQL

Comme exemple de déclencheur appliqué, insertion de nouvelles valeurs dans la table personne donne un résultat différent de l'entrée d'origine :

Notez que les noms insérés étaient initialement en minuscules. Lors de la sélection du tableau, la première lettre s'affiche en majuscule. Même s'il n'y a aucune indication de quelque chose de différent d'une instruction d'insertion normale, le déclencheur s'est déclenché avant l'instruction d'insertion pour mettre en majuscule la première lettre du nom.

Utilisation des déclencheurs MySQL

Chaque déclencheur associé à une table a un nom et une fonction uniques basés sur deux facteurs :

1. Heure . BEFORE ou AFTER un événement de ligne spécifique.

2. Événement . INSERT , UPDATE ou DELETE .

Les déclencheurs MySQL se déclenchent en fonction de l'heure d'activation et de l'événement pour un total de six combinaisons de déclencheurs uniques. Les instructions avant permettent de vérifier les données et d'apporter des modifications avant de prendre des engagements, tandis que les instructions après valident d'abord les données, puis exécutent les instructions.

L'exécution d'un ensemble d'actions se produit automatiquement, affectant toutes les lignes insérées, supprimées ou mises à jour dans l'instruction.

Créer des déclencheurs

Utilisez le CREATE TRIGGER syntaxe de l'instruction pour créer un nouveau déclencheur :

CREATE TRIGGER <trigger name> <trigger time > <trigger event>
ON <table name>
FOR EACH ROW
<trigger body>;

La meilleure pratique consiste à nommer le déclencheur avec les informations suivantes :

<trigger time>_<table name>_<trigger event>

Par exemple, si un déclencheur se déclenche avant l'insertion sur une table nommée employé , la meilleure convention est d'appeler le déclencheur :

before_employee_insert

Alternativement, une pratique courante consiste à utiliser le format suivant :

<table name>_<first letter of trigger time><first letter of trigger name>

Le avant insérer nom du trigger pour la table employee ressemble à ceci :

employee_bi

Le déclencheur s'exécute à un moment précis d'un événement sur une table définie par

pour chaque ligne affectée par la fonction.

Supprimer les déclencheurs

Pour supprimer un déclencheur, utilisez le DROP TRIGGER déclaration :

DROP TRIGGER <trigger name>;

Vous pouvez également utiliser :

DROP TRIGGER IF EXISTS <trigger name>;

Le message d'erreur ne s'affiche pas car il n'y a pas de déclencheur, donc aucun avertissement ne s'imprime.

Créer un exemple de base de données

Créez une base de données pour les exemples de codes déclencheurs avec la structure suivante :

1. Créez une table appelée personne avec nom et âge pour les colonnes.

CREATE TABLE person (name varchar(45), age int);

Insérez des exemples de données dans le tableau :

INSERT INTO person VALUES ('Matthew', 25), ('Mark', 20);

Sélectionnez le tableau pour voir le résultat :

SELECT * FROM person;

2. Créez une table appelée average_age avec une colonne appelée moyenne :

CREATE TABLE average_age (average double);

Insérez la valeur d'âge moyen dans le tableau :

INSERT INTO average_age SELECT AVG(age) FROM person;

Sélectionnez le tableau pour voir le résultat :

SELECT * FROM average_age;

3. Créez une table appelée person_archive avec nom , âge , et heure colonnes :

CREATE TABLE person_archive (
name varchar(45),
age int,
time timestamp DEFAULT NOW());

Créer un déclencheur BEFORE INSERT

Pour créer un BEFORE INSERT déclencheur, utilisez :

CREATE TRIGGER <trigger name> BEFORE INSERT
ON <table name>
FOR EACH ROW
<trigger body>;

Le BEFORE INSERT le déclencheur donne le contrôle sur la modification des données avant de s'engager dans une table de base de données. Mettre les noms en majuscules pour des raisons de cohérence, vérifier la longueur d'une entrée ou détecter les entrées erronées avec BEFORE INSERT les déclencheurs fournissent en outre des limitations de valeur avant d'entrer de nouvelles données.

AVANT D'INSÉRER Exemple de déclencheur

Créer un BEFORE INSERT déclencheur pour vérifier la valeur d'âge avant d'insérer des données dans la personne tableau :

delimiter //
CREATE TRIGGER person_bi BEFORE INSERT
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;

L'insertion de données active le déclencheur et vérifie la valeur de age avant de valider les informations :

INSERT INTO person VALUES ('John', 14);

La console affiche le message d'erreur descriptif. Les données ne sont pas insérées dans la table en raison de l'échec de la vérification du déclencheur.

Créer un déclencheur AFTER INSERT

Créer un AFTER INSERT déclencher avec :

CREATE TRIGGER <trigger name> AFTER INSERT
ON <table name>
FOR EACH ROW
<trigger body>;

Le AFTER INSERT le déclencheur est utile lorsque la ligne saisie génère une valeur nécessaire pour mettre à jour une autre table.

Exemple de déclencheur AFTER INSERT

Insertion d'une nouvelle ligne dans la personne le tableau ne met pas automatiquement à jour la moyenne dans average_age table. Créer un AFTER INSERT déclencheur sur la personne tableau pour mettre à jour le average_age tableau après insertion :

delimiter //
CREATE TRIGGER person_ai AFTER INSERT
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(age) FROM person); //
delimiter ;

Insertion d'une nouvelle ligne dans la personne table active le déclencheur :

INSERT INTO person VALUES ('John', 19);

Les données sont validées avec succès pour la personne table et met à jour le average_age tableau avec la valeur moyenne correcte.

Créer un déclencheur AVANT LA MISE À JOUR

Faire un BEFORE UPDATE déclencher avec :

CREATE TRIGGER <trigger name> BEFORE UPDATE
ON <table name>
FOR EACH ROW
<trigger body>;

Le BEFORE UPDATE les déclencheurs vont de pair avec le BEFORE INSERT déclencheurs. S'il existe des restrictions avant l'insertion des données, les limites doivent également être présentes avant la mise à jour.

Exemple de déclencheur AVANT LA MISE À JOUR

S'il y a une limite d'âge pour la personne tableau avant d'insérer des données, la restriction d'âge doit également exister avant la mise à jour des informations. Sans le BEFORE UPDATE déclencheur, le déclencheur de vérification de l'âge est facile à éviter. Rien ne limite la modification à une valeur erronée.

Ajouter un BEFORE UPDATE déclencheur pour la personne table avec le même corps que BEFORE INSERT déclencheur :

delimiter //
CREATE TRIGGER person_bu BEFORE UPDATE
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50002' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;

La mise à jour d'une valeur existante active la vérification du déclencheur :

UPDATE person SET age = 17 WHERE name = 'John';

Mise à jour de l'âge à une valeur inférieure à 18 affiche le message d'erreur et les informations ne sont pas mises à jour.

Créer un déclencheur APRÈS MISE À JOUR

Utilisez le bloc de code suivant pour créer un AFTER UPDATE déclencheur :

CREATE TRIGGER <trigger name> AFTER UPDATE
ON <table name>
FOR EACH ROW
<trigger body>;

Le AFTER UPDATE Le déclencheur permet de garder une trace des modifications validées apportées aux données. Le plus souvent, toute modification après l'insertion d'informations se produit également après la mise à jour des données.

APRÈS LA MISE À JOUR Exemple de déclencheur

Toute mise à jour réussie de l'âge données dans le tableau personne doit également mettre à jour la valeur moyenne intermédiaire calculée dans le average_age tableau.

Créer un AFTER UPDATE déclencheur pour mettre à jour le average_age table après mise à jour d'une ligne dans la personne tableau :

delimiter //
CREATE TRIGGER person_au AFTER UPDATE
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(age) FROM person); //
delimiter ;

La mise à jour des données existantes modifie la valeur dans la personne tableau :

UPDATE person SET age = 21 WHERE name = 'John';

Mise à jour de la table personne met également à jour la moyenne dans average_age tableau.

Créer un déclencheur AVANT SUPPRESSION

Pour créer un BEFORE DELETE déclencheur, utilisez :

CREATE TRIGGER <trigger name> BEFORE DELETE
ON <table name>
FOR EACH ROW
<trigger body>;

Le BEFORE DELETE déclencheur est essentiel pour des raisons de sécurité. Si une table parent a des enfants attachés, le déclencheur aide à bloquer la suppression et empêche les tables orphelines. Le déclencheur permet également d'archiver les données avant leur suppression.

AVANT DE SUPPRIMER Exemple de déclencheur

Archivez les données supprimées en créant un BEFORE DELETE déclencheur sur la table personne et insérez les valeurs dans person_archive tableau :

delimiter //
CREATE TRIGGER person_bd BEFORE DELETE
ON person
FOR EACH ROW
INSERT INTO person_archive (name, age)
VALUES (OLD.name, OLD.age); //
delimiter ;

Suppression de données de la table personne archive les données dans person_archive tableau avant de supprimer :

DELETE FROM person WHERE name = 'John';

Réinsérer la valeur dans la personne table conserve le journal des données supprimées dans person_archive tableau :

INSERT INTO person VALUES ('John', 21);

Le BEFORE DELETE le déclencheur est utile pour consigner toutes les tentatives de changement de table.

Créer un déclencheur APRÈS SUPPRESSION

Faire un AFTER DELETE déclencher avec :

CREATE TRIGGER <trigger name> AFTER DELETE
ON <table name>
FOR EACH ROW
<trigger body>;

Le AFTER DELETE les déclencheurs maintiennent les mises à jour des informations qui nécessitent la disparition de la ligne de données avant d'effectuer les mises à jour.

Exemple de déclencheur APRÈS SUPPRESSION

Créer un AFTER DELETE déclencheur sur la table personne pour mettre à jour le age_moyen tableau avec les nouvelles informations :

delimiter //
CREATE TRIGGER person_ad AFTER DELETE
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(person.age) FROM person); //
delimiter ;

Supprimer un enregistrement de la table personne met à jour le age_moyen tableau avec la nouvelle moyenne :

Sans le AFTER DELETE déclencheur, les informations ne sont pas mises à jour automatiquement.

Créer plusieurs déclencheurs

MySQL ne prend pas en charge le déclenchement simultané de plusieurs déclencheurs. Cependant, l'ajout de plusieurs opérations logiques au même déclencheur est possible. Utilisez le BEGIN et END délimiteurs pour indiquer le corps du déclencheur :

CREATE TRIGGER <trigger name> <trigger time > <trigger event>
ON <table name>
FOR EACH ROW
BEGIN
<trigger body>;
END;

Assurez-vous de modifier le délimiteur par défaut avant de créer un déclencheur avec plusieurs opérations.

Afficher les déclencheurs

Lister tous les déclencheurs d'une base de données avec :

SHOW triggers;

La sortie affiche une liste de tous les déclencheurs, y compris le nom et le contenu de l'instruction :

D'autres informations s'affichent également, telles que l'heure de création et l'utilisateur qui a créé le déclencheur.