Quel est le niveau de déclenchement de MySQL ?

Les déclencheurs MySQL sont au niveau de la ligne. Selon les standards SQL, les déclencheurs peuvent être divisés en deux types : 1. Les déclencheurs au niveau de la ligne, qui seront activés une fois pour chaque ligne de données modifiées. Si une instruction insère 100 lignes de données, le déclencheur sera appelé 100 fois ; . Déclencheurs au niveau de l'instruction Le déclencheur est activé une fois pour chaque instruction. Une instruction qui insère 100 lignes de données n'appellera le déclencheur qu'une seule fois. MySQL ne prend en charge que les déclencheurs au niveau des lignes, pas les déclencheurs au niveau des instructions préparées.

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

Présentation des déclencheurs

Le déclencheur MySQL est une procédure stockée associée à une table spécifiée et est automatiquement exécutée lorsque les données de la table changent (ajoutées, mises à jour, supprimées). Ces opérations qui modifient les lignes de données sont appelées événements déclencheurs. Par exemple, des instructions telles que INSERT ou LOAD DATA qui insèrent des données peuvent activer des déclencheurs d'insertion.

Selon les normes SQL, les déclencheurs peuvent être divisés en déclencheurs au niveau de la ligne et en déclencheurs au niveau de l'instruction.

• Le déclencheur au niveau de la ligne sera activé une fois pour chaque ligne de données modifiée. Si une instruction insère 100 lignes de données, le déclencheur sera appelé 100 fois

• Le déclencheur au niveau de l'instruction sera activé une fois ; pour chaque instruction. Une instruction qui insère 100 lignes de données n’appellera le déclencheur qu’une seule fois.

• MySQL ne prend en charge que les déclencheurs au niveau de la ligne, pas les déclencheurs préparés au niveau de l'instruction.

Différents événements peuvent activer différents types de déclencheurs. Le déclencheur d'événement INSERT est utilisé pour les opérations d'insertion de données, notamment les instructions INSERT, LOAD DATA, REPLACE, etc. ; le déclencheur d'événement UPDATE est utilisé pour les opérations de mise à jour, telles que les instructions UPDATE ; Instruction DELETE et REPLACE, etc., DROP TABLE et L'instruction TRUNCATE TABLE n'active pas les déclencheurs de suppression.

De plus, les déclencheurs MySQL peuvent être exécutés avant ou après l'événement déclencheur, qui sont respectivement appelés déclencheurs AVANT et APRÈS. Ces deux timings de déclenchement peuvent être combinés avec différents événements de déclenchement, tels que le déclencheur BEFORE INSERT ou le déclencheur AFTER UPDATE.

Les avantages des déclencheurs MySQL incluent :

• Enregistrer et auditer les opérations de modification des utilisateurs sur les données de la table pour implémenter des fonctions d'audit ; -Opération de données de temps commercial ;

• Mettre en œuvre certaines logiques métier, comme la mise à jour automatique du nombre de personnes dans un service lors de l'ajout ou de la suppression d'employés 

• Copier de manière synchrone les données dans le tableau en temps réel ;

• Bien que les déclencheurs soient puissants, ils présentent également quelques inconvénients :

Les déclencheurs augmenteront la complexité de la structure de la base de données, et les déclencheurs sont invisibles pour l'application et difficiles à déboguer

• déclencheur Le déclencheur ; doit occuper plus de ressources du serveur de base de données et essayer d'utiliser les contraintes de vérification non nulles, uniques, etc. fournies par la base de données

• Les déclencheurs ne peuvent pas recevoir de paramètres et ne peuvent fonctionner qu'en fonction de l'objet déclencheur actuel ;

• L'utilisation de déclencheurs pour des scénarios spéciaux peut apporter une certaine commodité ; mais ne comptez pas trop sur les déclencheurs pour éviter la dégradation des performances de la base de données et les difficultés de maintenance. Nous introduisons ensuite les opérations de gestion des déclencheurs.

Trois déclencheurs pris en charge par MySQL

En utilisation réelle, MySQL prend en charge trois déclencheurs : le déclencheur INSERT, le déclencheur UPDATE et le déclencheur DELETE.

1) Déclencheur INSERT

Un déclencheur qui répond avant ou après l'exécution de l'instruction INSERT. Vous devez faire attention aux points suivants lors de l'utilisation des déclencheurs INSERT :

Dans le code du déclencheur INSERT, vous pouvez référencer une table virtuelle nommée NEW (insensible à la casse) pour accéder à la ligne insérée.

• Dans le déclencheur BEFORE INSERT, la valeur dans NEW peut également être mise à jour, ce qui permet de modifier la valeur insérée (à condition qu'elle dispose des autorisations d'opération correspondantes).

• Pour la colonne AUTO_INCREMENT, NEW contient la valeur 0 avant l'exécution de l'INSERT, et contiendra la nouvelle valeur générée automatiquement après l'exécution de l'INSERT.

• 2) Déclencheur UPDATE

Un déclencheur qui répond avant ou après l'exécution de l'instruction UPDATE. Vous devez faire attention aux points suivants lorsque vous utilisez les déclencheurs UPDATE :

Dans le code du déclencheur UPDATE, vous pouvez référencer une table virtuelle nommée NEW (insensible à la casse) pour accéder à la valeur mise à jour.

• Dans le code du déclencheur UPDATE, une table virtuelle nommée OLD (insensible à la casse) peut être référencée pour accéder à la valeur avant l'exécution de l'instruction UPDATE.

• Dans le déclencheur BEFORE UPDATE, la valeur dans NEW peut également être mise à jour, ce qui permet de modifier la valeur à utiliser dans l'instruction UPDATE (à condition que vous disposiez des autorisations d'opération correspondantes).

  Toutes les valeurs de

• OLD sont en lecture seule et ne peuvent pas être mises à jour.

Remarque : Lorsque le déclencheur est conçu pour déclencher l'opération de mise à jour de la table elle-même, seuls les déclencheurs de type AVANT peuvent être utilisés, et les déclencheurs de type APRÈS ne seront pas autorisés.

3) Déclencheur DELETE

Un déclencheur qui répond avant ou après l'exécution de l'instruction DELETE.

Vous devez faire attention aux points suivants lorsque vous utilisez les déclencheurs DELETE :

• Dans le code du déclencheur DELETE, vous pouvez référencer une table virtuelle nommée OLD (insensible à la casse) pour accéder aux lignes supprimées. Toutes les valeurs dans

• OLD sont en lecture seule et ne peuvent pas être mises à jour.

De manière générale, lors de l'utilisation des déclencheurs, MySQL gérera les erreurs des manières suivantes.

Pour les tables transactionnelles, si le déclencheur échoue et que l'instruction entière échoue en conséquence, toutes les modifications effectuées par l'instruction seront annulées ; pour les tables non transactionnelles, une telle annulation ne peut pas être effectuée, même si l'instruction échoue, quel que soit le cas. les modifications apportées avant l’échec sont toujours valables.

Si le déclencheur BEFORE échoue, MySQL n'effectuera pas l'opération sur la ligne correspondante.

Si une erreur se produit lors de l'exécution du programme déclencheur AVANT ou APRES, cela entraînera l'échec de l'intégralité de l'instruction appelant le programme déclencheur.

MySQL exécutera le déclencheur AFTER uniquement si le déclencheur BEFORE et l'opération sur la ligne ont été exécutés avec succès.

Créer un déclencheur

MySQL utilise l'instruction CREATE TRIGGRT pour créer un déclencheur. La syntaxe de base est la suivante :

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    trigger_body;

Parmi eux, trigger_name est le nom du déclencheur BEFORE et AFTER sont utilisés pour spécifier ; l'heure de déclenchement du déclencheur ; INSERT, UPDATE et DELETE sont utilisés pour définir le type d'événement déclencheur ; table_name est le nom de la table associée au déclencheur, qui ne peut pas être une table ou une vue temporaire FOR EACH ROW indique qu'il s'agit d'une table ou d'une vue temporaire. un déclencheur au niveau de la ligne trigger_body est l'instruction spécifique exécutée par le déclencheur.

Par exemple, puisque les salaires des employés sont des informations importantes, l’historique des modifications salariales doit être enregistré. Tout d'abord, nous créons une table d'audit :

CREATE TABLE emp_salary_audit (
    audit_id    INTEGER NOT NULL AUTO_INCREMENT
    emp_id      INTEGER NOT NULL,
    old_salary  NUMERIC(8,2) NULL,
    new_salary  NUMERIC(8,2) NULL,
    change_date TIMESTAMP NOT NULL,
    change_by   VARCHAR(50) NOT NULL,
    CONSTRAINT pk_emp_salary_audit PRIMARY KEY (audit_id)
);

Parmi eux, audit_id est la clé primaire d'auto-incrémentation ; emp_id est le numéro d'employé ; old_salary et new_salary sont utilisés pour stocker le salaire mensuel avant et après modification respectivement ; ; change_by enregistre la modification d'exécution L'utilisateur opérateur.

Créez ensuite un déclencheur tri_audit_salary pour enregistrer l'enregistrement de modification du salaire mensuel de l'employé :

DELIMITER $$
CREATE TRIGGER tri_audit_salary
  AFTER UPDATE ON employee
  FOR EACH ROW
BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END$$
DELIMITER ;

Parmi eux, DELIMITER est utilisé pour modifier le caractère de fin de l'instruction SQL, que l'on connaît déjà lors de l'introduction de la procédure stockée AFTER signifie modifier le ; data Ensuite, exécutez le déclencheur ; UPDATE signifie que les modifications de données sont enregistrées uniquement pour les opérations de mise à jour ; NEW et OLD dans le corps du déclencheur sont des variables spéciales dans les déclencheurs MySQL, y compris les enregistrements modifiés et pré-modifiés. Il n'y a pas de OLD pour les variables de déclencheur INSERT. il n'y a pas de variable NEW pour les déclencheurs DELETE ; CURRENT_TIMESTAMP et USER() sont des fonctions système MySQL qui renvoient l'heure actuelle et l'utilisateur connecté.

Après avoir créé le déclencheur, nous effectuons quelques opérations de modification des données pour vérifier l'effet du déclencheur :

UPDATE employee
SET email = 'sunqian@shuguo.net'
WHERE emp_name = '孙乾';

UPDATE employee
SET salary = salary * 1.1
WHERE emp_name = '孙乾';

SELECT *
FROM salary_audit;
audit_id|emp_id|old_salary|new_salary|change_date        |change_by|
--------|------|----------|----------|-------------------|---------|
       1|    25|      4700|      5170|2019-10-18 10:16:36|TONY     |

La première instruction UPDATE n'a modifié que l'adresse email de "Sun Qian", donc tri_audit_salary ne sera pas déclenché la seconde ; L'instruction UPDATE modifie son salaire mensuel et déclenche tri_audit_salary. Par conséquent, la table d'audit salaire_audit contient une donnée qui enregistre la situation avant et après le changement de salaire mensuel.

Si vous souhaitez auditer les opérations d'ajout et de suppression d'employés en même temps, vous pouvez créer un déclencheur INSERT et un déclencheur DELETE.

De plus, MySQL prend en charge la définition de plusieurs déclencheurs pour le même moment de déclenchement et le même événement, et la spécification de leur ordre d'exécution :

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    { FOLLOWS | PRECEDES } other_trigger
    trigger_body;

Parmi eux, FOLLOWS signifie que le déclencheur est exécuté après le déclencheur other_trigger PRECEDES signifie que ce déclencheur est exécuté ; avant other_trigger ; si aucune option n'est spécifiée, la valeur par défaut est d'exécuter dans l'ordre dans lequel les déclencheurs sont créés.

Afficher les déclencheurs

Utilisez l'instruction SHOW TRIGGERS pour afficher la liste des déclencheurs dans la base de données :

SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Parmi eux, db_name est utilisé pour afficher les déclencheurs dans la base de données spécifiée, et la valeur par défaut est la base de données actuelle ; LIKE est utilisé pour faire correspondre le stockage Le nom du processus, WHERE peut spécifier plus de conditions de filtre. Par exemple, l'instruction suivante renvoie les déclencheurs dans la base de données actuelle :

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tri_audit_salary
               Event: UPDATE
               Table: employee
           Statement: BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
              Timing: AFTER
             Created: 2020-10-06 21:50:02.47
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

De plus, la table système MySQL INFORMATION_SCHEMA.TRIGGERS contient des informations de déclenchement plus détaillées.

Si vous souhaitez obtenir l'instruction DDL pour créer un déclencheur, vous pouvez afficher l'instruction SHOW CREATE TRIGGER. Par exemple :

mysql> SHOW CREATE TRIGGER tri_audit_salary\G
*************************** 1. row ***************************
               Trigger: tri_audit_salary
              sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tri_audit_salary` AFTER UPDATE ON `employee` FOR EACH ROW BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
  character_set_client: utf8mb4
  collation_connection: utf8mb4_0900_ai_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2020-10-06 21:50:02.47
1 row in set (0.00 sec)

Suppression d'un déclencheur

MySQL ne fournit pas d'instruction pour modifier un déclencheur. Vous pouvez uniquement supprimer et créer à nouveau un déclencheur via l'instruction DROP TRIGGER. Par exemple, l'instruction suivante peut être utilisée pour supprimer le déclencheur tri_audit_salary :

DROP TRIGGER IF EXISTS tri_audit_salary;

IF EXISTS pour éviter une erreur si le déclencheur tri_audit_salary n'existe pas.

【Recommandation associée : tutoriel vidéo mysql】

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!