MySQL explique en termes simples comment utiliser les déclencheurs

Cet article vous apporte des connaissances pertinentes sur mysql Les déclencheurs sont une méthode fournie par le serveur SQL aux programmeurs et aux analystes de données pour garantir l'intégrité des données. Il s'agit d'une procédure stockée spéciale liée aux événements de table. sont quelque peu similaires aux tâches planifiées du système d'exploitation, mais les tâches périodiques sont intégrées et exécutées sur le serveur MySQL. Examinons-les ensemble, j'espère que cela sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

Dans le développement réel, nous rencontrons souvent une telle situation : il existe 2 tables interdépendantes ou plus, telles que les informations sur le produit et les informations sur l'inventaire sont stockées dans 2 Dans différentes tables de données, lorsque nous ajoutons un nouvel enregistrement de produit, afin d'assurer l'intégrité des données, nous devons également ajouter un enregistrement d'inventaire à la table d'inventaire. De cette façon, nous devons écrire ces deux étapes d'opération associées dans le programme et les envelopper avec des transactions pour garantir que ces deux opérations deviennent une opération atomique, soit elles sont toutes exécutées, soit aucune d'entre elles n'est exécutée.

Si vous rencontrez des circonstances particulières, vous devrez peut-être conserver manuellement les données, il est donc facile d'oublier une étape, entraînant une perte de données. À ce stade, nous pouvons utiliser des déclencheurs. Vous pouvez créer un déclencheur pour que l'insertion de données d'informations sur le produit déclenche automatiquement l'insertion de données d'inventaire. De cette façon, vous n’avez pas à vous soucier des données manquantes dues à l’oubli d’ajouter des données d’inventaire.

Présentation des déclencheurs

MySQL prend en charge les déclencheurs à partir de la version 5. 0. 2. Les déclencheurs MySQL, comme les procédures stockées, sont des programmes intégrés au serveur MySQL. Un déclencheur est une opération déclenchée par un événement, notamment les événements INSERT, UPDATE et DELETE.

Le soi-disant événement fait référence à l'action de l'utilisateur ou au déclenchement d'un certain comportement. Si un programme déclencheur est défini, lorsque la base de données exécute ces instructions, cela équivaut à un événement survenant, et le déclencheur sera automatiquement déclenché pour effectuer l'opération correspondante. Lorsque des opérations d'insertion, de mise à jour et de suppression sont effectuées sur des données dans une table de données et qu'une certaine logique de base de données doit être exécutée automatiquement, des déclencheurs peuvent être utilisés pour y parvenir.

Création de déclencheurs

Créer une syntaxe de déclencheur

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 
触发器执行的语句块;

Description :

Nom de la table : Représente l'objet surveillé par le déclencheur.

AVANT | APRÈS : Indique l’heure du déclenchement. AVANT signifie déclencher avant l'événement ; APRÈS signifie déclencher après l'événement.

INSÉRER | MISE À JOUR | SUPPRIMER : Indique les événements déclenchés.

INSERT signifie le déclenchement lorsqu'un enregistrement est inséré ;

UPDATE signifie le déclenchement lorsqu'un enregistrement est mis à jour ;

DELETE signifie le déclenchement lorsqu'un enregistrement est supprimé ;

Exemple de code 1

Créez deux tables

CREATE TABLE test_trigge r (
id INT PRIMARY KEY AUTO_INCREMENT ,
t_note VARCHAR ( 30 )
) ;
CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT ,
t_log VARCHAR ( 30 )
) ;

Créez un déclencheur

DELIMITER / /
CREATE TRIGGER befo_re_insert
BEFORE INSERT ON test_trigger 
FOR EACH ROW
BEGIN
 INSERT INTO test_trigger_log ( t_log )
 VALUES ( ' befo re_inse rt ' ) ;
END / /
DELIMITER ;

Insérez des données dans la table de données test_trigger

INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');

Affichez les données dans la table de données test_trigger_log

SELECT * FROM test_trigger_log

Exemple de code 2

Créez un déclencheur

DELIMITER / /
CREATE TRIGGER after_insert
AFTER INSERT ON test_trigger
FOR EACH ROW
BEGIN
 INSERT INTO test_trigger_log ( t_log )
 VALUES ( ' after_insert ' ) ;
END / /
DELIMITER ;

Insérez des données dans la table de données test_trigger.

INSERT INTO test_trigger (t_note) VALUES ('测试 AFTER INSERT 触发器');

Affichez les données dans la table de données test_trigger_log

SELECT * FROM test_trigger_log

Exemple de code 3

Définissez le déclencheur "salary_check_trigger", basé sur l'événement INSERT de la table des employés "employés", avant d'INSÉRER vérifier si le salaire du le nouvel employé à ajouter est supérieur à Si le salaire de son leader est supérieur au salaire du leader, une erreur indiquant que sqlstate_value est 'HY000' sera signalée, provoquant l'échec de l'ajout.

DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
DECLARE mgrsalary DOUBLE;
SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
IF NEW.salary > mgrsalary THEN
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
END IF;
END //
DELIMITER ;

Le mot-clé NEW dans le processus de déclaration de déclencheur ci-dessus représente le nouvel enregistrement de l'instruction INSERT.

Afficher les déclencheurs de suppression

Méthode 1 : Afficher la définition de tous les déclencheurs dans la base de données actuelle

SHOW TRIGGERS

Méthode 2 : Afficher la définition d'un déclencheur dans la base de données actuelle

SHOW CREATE TRIGGER 触发器名

Méthode 3 : Depuis la table TRIGGERS de la bibliothèque système information_schema Interroge les informations du déclencheur "salary_check_trigger".

SELECT * FROM information_schema.TRIGGERS;

Déclencheur de suppression

DROP TRIGGER IF EXISTS 触发器名称

Avantages des déclencheurs

1 Les déclencheurs peuvent garantir l'intégrité des données.

Supposons que nous utilisions le tableau d'en-tête du bon de commande (demo.importhead) pour enregistrer les informations globales du bon de commande, y compris le numéro de bon de commande, le numéro de fournisseur, le numéro d'entrepôt, la quantité totale d'achat, le montant total d'achat et la date d'acceptation.

Utilisez le tableau des détails du bon de commande (demo.importdetails) pour enregistrer les détails des marchandises achetées, y compris le numéro de bon de commande, le numéro de produit et la quantité achetée

La quantité, le prix d'achat et le montant de l'achat ne sont pas égaux à la quantité totale et au montant total indiqués dans les détails du bon de commande. Il s'agit d'une incohérence des données.

Afin de résoudre ce problème, nous pouvons utiliser des déclencheurs pour stipuler qu'à chaque fois qu'il y aura une insertion, une modification et une suppression de données dans le tableau des détails du bon de commande

, l'opération en deux étapes sera automatiquement déclenchée :

1) Recalculer le Tableau des détails du bon de commande La quantité totale et le montant total dans le tableau du bon de commande 

2) Utilisez la valeur calculée à la première étape pour mettre à jour la quantité totale et le montant total dans le tableau du bon de commande ;

De cette façon, la quantité totale et le montant total dans le tableau du bon de commande seront toujours les mêmes que la quantité totale et

montant total calculés dans le tableau détaillé du bon de commande. Les données seront cohérentes et n'entreront pas en conflit avec chacune. autre.

2. Les déclencheurs peuvent nous aider à enregistrer les journaux d'opérations.

À l'aide de déclencheurs, vous pouvez enregistrer spécifiquement ce qui s'est passé à quelle heure. Par exemple, l'enregistrement du déclencheur de modification du montant de la valeur stockée du membre est un très bon exemple. Cela nous est très utile pour restaurer le scénario spécifique lors de l'exécution de l'opération et mieux localiser la cause du problème.

3. Les déclencheurs peuvent également être utilisés pour vérifier la validité des données avant de les exploiter.

Par exemple, lorsqu'un supermarché achète des marchandises, le responsable de l'entrepôt doit saisir le prix d'achat. Cependant, il est facile de commettre des erreurs dans les opérations humaines. Par exemple, lors de la saisie de la quantité, vous scannez le code-barres lors de la saisie du montant, vous lisez le numéro de série et le prix saisi dépasse de loin le prix de vente, ce qui entraîne une somme énorme. perte sur les livres.....

Ceux-ci peuvent tous être vérifiés via des déclencheurs avant l'opération d'insertion ou de mise à jour proprement dite, provoquant des erreurs à temps pour empêcher

des données erronées d'entrer dans le système.

Inconvénients des déclencheurs

1. L'un des plus gros problèmes des déclencheurs est une mauvaise lisibilité.

Étant donné que les déclencheurs sont stockés dans la base de données et pilotés par des événements, cela signifie que les déclencheurs peuvent ne pas être contrôlés par la couche application. Ceci est très difficile pour la maintenance du système.

Par exemple, créez un déclencheur pour modifier les opérations de recharge des membres. S'il y a un problème avec l'opération dans le déclencheur, la mise à jour du montant de la valeur stockée du membre échouera. J'utilise le code suivant pour démontrer

Les résultats montrent que le système génère une erreur et que le champ "aa" n'existe pas.

En effet, l'opération d'insertion de données dans le déclencheur comporte un champ supplémentaire et le système génère une erreur. Cependant, si vous ne comprenez pas ce déclencheur, vous pouvez penser qu'il y a un problème avec l'instruction update elle-même ou qu'il y a un problème avec la structure de la table d'informations sur les membres. Peut-être ajouterez-vous également un champ appelé "aa" au tableau d'informations sur les membres pour tenter de résoudre ce problème, mais le résultat sera vain.

2. Les modifications des données associées peuvent provoquer des erreurs de déclenchement.

En particulier, les modifications dans la structure de la table de données peuvent provoquer des erreurs de déclenchement, affectant ainsi le fonctionnement normal des opérations de données. Ceux-ci affecteront l’efficacité du dépannage des erreurs dans les applications en raison de la dissimulation du déclencheur lui-même.

Remarques

Notez que si une contrainte de clé étrangère est définie dans la table enfant et que la clé étrangère spécifie la clause ON UPDATE/DELETE CASCADE/SET NULL, la modification de la valeur de clé référencée par la table parent ou la suppression de la table parent be Lorsque la ligne d'enregistrement référencée est référencée, cela entraînera également des opérations de modification et de suppression de la sous-table. À ce stade, les déclencheurs définis en fonction des instructions UPDATE et DELETE de la sous-table ne seront pas activés.

Par exemple : l'instruction DELETE basée sur la table enfant de la table des employés (t_employee) définit le déclencheur t1, et le champ numéro de département (did) de la table enfant définit une contrainte de clé étrangère qui fait référence au numéro de département de la colonne de clé primaire du table parent département (t_department). (did), et la clé étrangère ajoute la clause "ONDELETE SET NULL", alors si la table parent département (t_department) est supprimée à ce moment, la table enfant employé (t_employee)

Apprentissage recommandé :

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!