【MySQL 12】 Déclencheur

Trigger est un type spécial de procédure stockée, qui est différent des procédures stockées que nous avons présentées précédemment. Les déclencheurs sont principalement déclenchés par des événements et sont automatiquement appelés et exécutés. Les procédures stockées peuvent être appelées par le nom de la procédure stockée.
La fonction principale des déclencheurs est d'assurer l'intégrité et la cohérence des données entre deux ou plusieurs tables qui sont plus complexes que l'intégrité référentielle, garantissant ainsi que les modifications des données dans les tables sont conformes à la détermination des règles métier du concepteur de base de données.
Une procédure stockée spéciale qui est automatiquement exécutée lorsqu'un déclencheur insère, met à jour ou supprime dans une table. Les déclencheurs sont généralement utilisés sur des contraintes avec des contraintes de vérification plus complexes.
La différence entre les déclencheurs et les procédures stockées ordinaires est que les déclencheurs fonctionnent sur une certaine table. Lors d'opérations telles que la mise à jour, l'insertion et la suppression, le système appellera et exécutera automatiquement le déclencheur correspondant sur la table.
Les déclencheurs dans SQL Server 2005 peuvent être divisés en deux catégories : les déclencheurs DML et les déclencheurs DDL affectent diverses instructions du langage de définition de données et sont déclenchés. Ces instructions incluent les instructions de création, de modification et de suppression.

Les déclencheurs DML sont divisés en :

1. après le déclencheur (déclenché après)
a. déclencheur d'insertion
déclencheur de mise à jour
c. 2. Au lieu du déclencheur (déclenché avant)
3. Différence
Après le déclenchement : il est nécessaire que le déclencheur ne soit déclenché qu'après qu'une certaine opération d'insertion, de mise à jour, de suppression soit effectuée et ne puisse être définie que sur le tableau.
Au lieu de trigger : Indique qu'il n'exécute pas ses opérations définies (insertion, mise à jour, suppression) mais exécute uniquement le trigger lui-même. Au lieu de déclencheurs, vous pouvez les définir sur la table ou sur la vue.

table insérée et table supprimée

Lors de l'utilisation de déclencheurs, SQL Server créera deux tables temporaires spéciales pour chaque déclencheur, à savoir la table insérée et la table supprimée. Ces deux tables sont stockées en mémoire et ont la même structure que la table dans laquelle le déclencheur a été créé. Elles sont maintenues et gérées par le système et les utilisateurs ne sont pas autorisés à les modifier. Chaque déclencheur ne peut accéder qu'à sa propre table temporaire. Une fois le déclencheur exécuté, les deux tables seront automatiquement libérées.

(1) La table insérée est utilisée pour stocker des copies des lignes affectées par les instructions d'insertion ou de mise à jour. Lorsqu'une opération d'insertion ou de mise à jour est effectuée, de nouvelles lignes de données sont ajoutées à la fois à la table de base et à la table insérée qui activent le déclencheur.
(2) La table supprimée est utilisée pour stocker des copies des lignes affectées par les instructions delete ou update. Lorsqu'une opération de suppression ou de mise à jour est effectuée, la ligne de données d'origine spécifiée est supprimée de la table de base, puis transférée vers la table supprimée. De manière générale, les mêmes lignes de données n'existeront pas dans la table de base et dans la table supprimée.
Description :
L'opération de mise à jour est divisée en deux étapes : d'abord, transférer les lignes de données d'origine modifiées de la table de base vers la table supprimée, puis copier les nouvelles lignes de données modifiées de la table insérée vers la table de base. . Autrement dit, pour l'opération de mise à jour, la table supprimée stocke l'ancienne valeur avant modification, et la table insérée stocke la nouvelle valeur après modification.

Quatre éléments de la syntaxe de création de déclencheur :

1.监视地点(table) 
2.监视事件(insert/update/delete) 
3.触发时间(after/before) 
4.触发事件(insert/update/delete)

Syntaxe :

delimiter &&create trigger trigger
Nameafter/before insert/update/delete on 表名
for each row   #这句话在mysql是固定的
beginsql语句;
end&&

Tableau des produits

mysql> create table g(
    -> id int auto_increment primary key,    -> name varchar(10),    -> num int    -> );

Tableau des commandes

mysql> create table o(
    -> idd int auto_increment primary key,    -> gid int,    -> much int    -> );

Insérer un produit :

mysql> insert into g (name,num) value ('juzi',20);mysql> select * from g;
+----+------+------+| id | name | num  |
+----+------+------+|  3 | juzi |   20 |
+----+------+------+

Si nous n'avons pas utilisé le déclencheur : Supposons que nous vendions 3 produits 1 maintenant, nous devons faire deux choses

1. enregistrer dans le tableau des commandes

insert into o(gid,much) values(1,3);

2. Mettre à jour la quantité restante du produit 1 dans le tableau des produits

update g set num=num-3 where id=1;

Créer un déclencheur :

delimiter &
mysql> create trigger trg1
    -> after insert on o    -> for each row    -> begin    -> update g set num = num -3 where id = 1;    -> end&

Exécution :

insert into o(gid,much) values(1,3)$

Résultat :

Vous constaterez que la quantité de produit 1 est passée à 7, ce qui signifie que lorsque nous insérons une commande, le déclencheur effectue automatiquement l'opération de mise à jour pour nous.

Mais maintenant, il y a un problème, car le numéro et l'identifiant de notre déclencheur sont codés en dur, donc quel que soit le produit que nous achetons, la quantité du produit 1 sera finalement mise à jour. Par exemple : nous insérons un autre enregistrement dans la table de commande : insert into o(gid,beaucoup) valeurs(2,3) Après exécution, nous constaterons que la quantité de produit 1 est passée à 4, mais la quantité de produit 2). n’a pas changé. Ce n’est évidemment pas le cas des résultats que nous souhaitons. Nous devons modifier le déclencheur que nous avons créé plus tôt.

Comment référencer la valeur de la ligne dans le déclencheur, c'est-à-dire que nous devons obtenir la valeur de gid ou much dans notre enregistrement de commande nouvellement inséré.

Pour l'insertion, la ligne nouvellement insérée est représentée par new et la valeur de chaque colonne de la ligne est représentée par new.column name.

Alors maintenant, nous pouvons changer notre déclencheur comme ceci

create trigger tg2 
after insert on o 
for each row 
begin 
update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同) 
end$

Le deuxième déclencheur est créé, supprimons d'abord le premier déclencheur

drop trigger tg1$

Testez-le à nouveau et insérez un enregistrement de commande : insérez dans o(gid,much) valeurs(2,3)$

Après exécution, il s'avère que la quantité du produit 2 est passée à 7. D'accord, c'est maintenant.

Il existe encore deux situations :

1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？

2.当用户修改一个订单的数量时，我们触发器修改怎么写?

我们先分析一下第一种情况：

监视地点：o表

监视事件：delete

触发时间：after

触发事件：update

对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示，old.列名可以引用被删除的行的值。

那我们的触发器就该这样写：

create trigger tg3
after delete on o
for each row
begin
update g set num = num + old.much where id = old.gid;(注意这边的变化)
end$

创建完毕。

再执行

delete from o where oid = 2$

会发现商品2的数量又变为10了。

第二种情况：

监视地点：o表

监视事件：update

触发时间：after

触发事件：update

对于update而言：被修改的行，修改前的数据，用old来表示，old.列名引用被修改之前行中的值；

修改的后的数据，用new来表示，new.列名引用被修改之后行中的值。

那我们的触发器就该这样写：

create trigger tg4
after update on o
for each row
begin
update g set num = num+old.much-new.much where id = old/new.gid;
end$

先把旧的数量恢复再减去新的数量就是修改后的数量了。

我们来测试下：先把商品表和订单表的数据都清掉，易于测试。

假设我们往商品表插入三个商品，数量都是10，

买3个商品1：

insert into o(gid,much) values(1,3)$

这时候商品1的数量变为7；

我们再修改插入的订单记录:

update o set much = 5 where oid = 1$

我们变为买5个商品1，这时候再查询商品表就会发现商品1的数量只剩5了，说明我们的触发器发挥作用了。

以上就是 【MySQL 12】触发器的内容，更多相关内容请关注PHP中文网（www.php.cn）！