MySQL-触发器_mysql8.0命令行创建触发器

在实际开发中，我们经常会遇到这样的情况：有 2 个或者多个相互关联的表，如 商品信息 和 库存信息 分别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。

这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用 事务 包裹起来，确保这两个操作成为一个 原子操作 ，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手动维护，这样就很 容易忘记其中的一步 ，导致数据缺失。

这个时候，咱们可以使用触发器。你可以创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了。

一、触发器概述

MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。

触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会 自动 激发触发器执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

---

二、触发器的创建

• 创建触发器的语法结构

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

说明：

• 表名 ：表示触发器监控的对象。

• BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。

• INSERT|UPDATE|DELETE ：表示触发的事件。

  • INSERT 表示插入记录时触发；
  • UPDATE 表示更新记录时触发；
  • DELETE 表示删除记录时触发。

• 触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。

举例

mysql> # 举例1：
mysql> # ① 创建数据表
mysql> CREATE TABLE test_trigger (
    ->  	id INT PRIMARY KEY AUTO_INCREMENT,
    ->  	t_note VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.87 sec)

mysql> CREATE TABLE test_trigger_log (
    ->  	id INT PRIMARY KEY AUTO_INCREMENT,
    ->  	t_log VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> # ② 查看表数据
mysql> SELECT * FROM test_trigger;
Empty set (0.00 sec)

mysql> SELECT * FROM test_trigger_log;
Empty set (0.00 sec)

mysql> # ③ 创建触发器
mysql> /*
   /*>  创建名称为before_insert_test_tri的触发器，向test_trigger数据表插入数据之前，
   /*>  向test_trigger_log数据表中插入before_insert的日志信息。
   /*> */
mysql> DELIMITER //
mysql> CREATE TRIGGER before_insert_test_tri
    -> BEFORE INSERT ON test_trigger
    -> FOR EACH ROW
    -> BEGIN
    ->  	INSERT INTO test_trigger_log(t_log)
    ->  	VALUES('before insert...');
    -> END //
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;

mysql> # ④ 测试
mysql> INSERT INTO test_trigger(t_note)
    -> VALUES('Tom...');
Query OK, 1 row affected (0.02 sec)

mysql> # 再次查询
mysql> SELECT * FROM test_trigger;
+----+--------+
| id | t_note |
+----+--------+
|  1 | Tom... |
+----+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_trigger_log;
+----+------------------+
| id | t_log            |
+----+------------------+
|  1 | before insert... |
+----+------------------+
1 row in set (0.00 sec)

----------------------------------------------------

mysql> # 举例2：
mysql> /*
   /*>  创建名称为after_insert_test_tri的触发器，向test_trigger数据表插入数据之后，
   /*>  向test_trigger_log数据表中插入after_insert的日志信息。
   /*> */
mysql> DELIMITER $
mysql> CREATE TRIGGER after_insert_test_tri
    -> AFTER INSERT ON test_trigger
    -> FOR EACH ROW
    -> BEGIN
    ->  	INSERT INTO test_trigger_log(t_log)
    ->  	VALUES('after insert...');
    -> END $
Query OK, 0 rows affected (2.20 sec)

mysql> DELIMITER ;
mysql> # 测试
mysql> INSERT INTO test_trigger(t_note)
    -> VALUES('Jerry2...');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test_trigger;
+----+-----------+
| id | t_note    |
+----+-----------+
|  1 | Tom...    |
|  2 | Jerry2... |
+----+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_trigger_log;
+----+------------------+
| id | t_log            |
+----+------------------+
|  1 | before insert... |
|  2 | before insert... |
|  3 | after insert...  |
+----+------------------+
3 rows in set (0.00 sec)

---------------------------------------------------------------

mysql> #举例3：
mysql> /*
   /*>  定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，
   /*>  在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，
   /*>  则报sqlstate_value为'HY000'的错误，从而使得添加失败。
   /*> */
mysql>
mysql> # 查看表结构
mysql> DESC employees;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id    | int(6)      | NO   |     | 0       |       |
| first_name     | varchar(20) | YES  |     | NULL    |       |
| last_name      | varchar(25) | NO   |     | NULL    |       |
| email          | varchar(25) | NO   |     | NULL    |       |
| phone_number   | varchar(20) | YES  |     | NULL    |       |
| hire_date      | date        | NO   |     | NULL    |       |
| job_id         | varchar(10) | NO   |     | NULL    |       |
| salary         | double(8,2) | YES  |     | NULL    |       |
| commission_pct | double(2,2) | YES  |     | NULL    |       |
| manager_id     | int(6)      | YES  |     | NULL    |       |
| department_id  | int(4)      | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> DESC departments;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| department_id   | int(4)      | NO   |     | 0       |       |
| department_name | varchar(30) | NO   |     | NULL    |       |
| manager_id      | int(6)      | YES  |     | NULL    |       |
| location_id     | int(4)      | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> # 创建触发器
mysql> DELIMITER //
mysql> CREATE TRIGGER salary_check_trigger
    -> BEFORE INSERT ON employees
    -> FOR EACH ROW
    -> BEGIN
    ->  # 查询到要添加的数据的manager的薪资
    ->  DECLARE mgr_sal DOUBLE;
    ->
    ->  SELECT salary INTO mgr_sal FROM employees
    ->  WHERE employee_id = NEW.manager_id;
    ->
    ->  IF NEW.salary > mgr_sal
    ->          THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
    ->  END IF;
    -> END //
Query OK, 0 rows affected (1.28 sec)

mysql> DELIMITER ;
mysql>
mysql> # 测试
mysql> # 添加成功：依然触发了触发器salary_check_trigger的执行
mysql> INSERT INTO employees(employee_id,last_name,email,hire_date,job_id,salary,manager_id)
    -> VALUES(300,'Tom','tom@126.com',CURDATE(),'AD_VP',8000,103);
Query OK, 1 row affected (0.06 sec)

mysql> # 添加失败
mysql> INSERT INTO employees(employee_id,last_name,email,hire_date,job_id,salary,manager_id)
    -> VALUES(301,'Tom1','tom1@126.com',CURDATE(),'AD_VP',10000,103);
ERROR 1644 (HY000): 薪资高于领导薪资错误
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171

---

三、查看、删除触发器

1. 查看触发器

查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。

• 方式1：查看当前数据库的所有触发器的定义

SHOW TRIGGERS
1

• 方式2：查看当前数据库中某个触发器的定义

SHOW CREATE TRIGGER 触发器名
1

• 方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。

SELECT * FROM information_schema.TRIGGERS;
1

举例

# 查看当前数据库的所有触发器的定义
SHOW TRIGGERS;

# 方式2：查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER salary_check_trigger;

# 方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;
1
2
3
4
5
6
7
8

2. 删除触发器

触发器也是数据库对象，删除触发器也用DROP语句，语法格式如下：

DROP TRIGGER IF EXISTS 触发器名称;
1

举例

DROP TRIGGER IF EXISTS after_insert_test_tri;
1

---

四、触发器的优缺点

1. 优点

• 触发器可以确保数据的完整性

这一点在我们开头提到的 商品信息 和 库存信息 表是一个意思，每当我们在商品信息表中录入、删除信息时，库存信息的表也会发生变动。这个时候，库存信息的库存量等就必须重新计算，否则商品信息与库存信息不对等，这就是数据不一致。

为了解决这个问题，我们就可以使用触发器，规定每当商品信息表有数据插入和删除的操作时，自动触发以下操作：

• 重新计算库存数量；

这样一来，商品信息与库存信息始终对等，不会互相矛盾。

• 触发器可以帮助我们记录操作日志

利用触发器，可以具体记录什么时间发生了什么。比如，记录修改会员储值金额的触发器，就是一个很好的例子。这对我们还原操作执行时的具体场景，更好地定位问题原因很有帮助。

• 触发器还可以用在操作数据前，对数据进行合法性检查

比如，超市进货的时候，需要库管录入进货价格。但是，人为操作很容易犯错误，比如说在录入数量的时候，把条形码扫进去了；录入金额的时候，看串了行，录入的价格远超售价，导致账面上的巨亏……

这些都可以通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止错误数据进入系统。

2. 缺点

• 触发器最大的一个问题就是可读性差

因为触发器存储在数据库中，并且由 事件驱动，这就意味着触发器有可能 不受应用层的控制 。这对系统维护是非常有挑战的。

比如，创建触发器用于修改会员储值操作。如果触发器中的操作出了问题，会导致会员储值金额更新失败。我用下面的代码演示一下：

mysql> update demo.membermaster set memberdeposit=20 where memberid = 2;
ERROR 1054 (42S22): Unknown column 'aa' in 'field list'
1
2

结果显示，系统提示错误，字段“aa”不存在。

这是因为，触发器中的数据插入操作多了一个字段，系统提示错误。可是，如果你不了解这个触发器，很可能会认为是更新语句本身的问题，或者是会员信息表的结构出了问题。说不定你还会给会员信息表添加一个叫“aa”的字段，试图解决这个问题，结果只能是白费力。

• 相关数据的变更，可能会导致触发器出错

特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。

注意：

如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。