触发器(Trigger)是 MySQL 中非常实用的一个功能,它可以在操作者对表进行「增删改」 之前(或之后)被触发,自动执行一段事先写好的 SQL 代码。
本教程带领大家在实践中学习,你将学到触发器在实际应用场景中的重要应用。
在这个教程中,你是「卡拉云银行」的程序员,你正在搭建一套银行客户管理系统。在这套系统中,你需要设置在INSERT
表之前检测操作者是否输入错误数据、在 UPDATE
时,记录操作者的行为 log ,以及在DELETE
时,判断删除的信息是否符合删除规则。 这三类操作都可以使用 MySQL 触发器来实现。
如果你正在使用触发器搭建一套企业内部工具,我推荐你试试卡拉云,详情见文末。
本教程将带你一起实践的案例
BEFORE INSERT
: 在插入数据前,检测插入数据是否符合业务逻辑,如不符合返回错误信息。AFTER INSERT
: 在表 A 创建新账户后,将创建成功信息自动写入表 B 中。BEFORE UPDATE
:在更新数据前,检测更新数据是否符合业务逻辑,如不符合返回错误信息。AFTER UPDATE
:在更新数据后,将操作行为记录在 log 中BEFORE DELETE
:在删除数据前,检查是否有关联数据,如有,停止删除操作。AFTER DELETE
:删除表 A 信息后,自动删除表 B 中与表 A 相关联的信息。
先决条件
在开始之前,请确保您具备以下条件:
- 一台配置好的 Ubuntu 服务器,root 账号。
- 服务器上配置好 MySQL Server(配置 MySQL 请看连接MySQL教程)
- MySQL root 账号
创建示例数据库
我们先创建一个干净的示例数据库,方便大家可以跟随本教程一起实践。我们会在这个数据库中演示 MySQL 触发器的多种工作方式。
首先,以 root 身份登录到你的 MySQL 服务器:
mysql -u root -p
出现提示时,请输入你 MySQL root 账号的密码,然后点击 ENTER
继续。看到 mysql>
提示后,运行以下命令,创建 demo_kalacloud
数据库:
CREATE database demo_kalacloud;
Output
Query OK, 1 row affected (0.00 sec)
接下来,切换到新建的 demo_kalacloud
数据库:
USE demo_kalacloud;
Output
Database changed
接着创建一个 customers
表。我们使用这个表记录银行客户的信息。这个表包括 customer_id
,customer_name
,和level
。咱们先把客户分为两个级别:BASIC
和VIP
。
create table customers(
customer_id BIGINT PRIMARY KEY,
customer_name VARCHAR(50),
level VARCHAR(50)
) ENGINE=INNODB;
Output
Query OK, 0 rows affected (0.01 sec)
接着,我们向 customers
表中添加一些客户记录。
Insert into customers (customer_id, customer_name, level )values('1','Jack Ma','BASIC');
Insert into customers (customer_id, customer_name, level )values('2','Robin Li','BASIC');
Insert into customers (customer_id, customer_name, level )values('3','Pony Ma','VIP');
分别运行三个 INSERT
命令后,命令行输出成功信息。
Output
Query OK, 1 row affected (0.01 sec)
我们使用 SELECT
检查一下三条信息是否已经写入表中:
Select * from customers;
下面我们创建另一个表customer_status
,用于保存 customers
表中客户的备注信息。
这个表包含 customer_id
和 status_notes
字段:
Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;
然后,我们再创建一个 sales
表,这个表与 customer_id
关联。保存与客户有关的销售数据。
Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;
Output
Query OK, 0 rows affected (0.01 sec)
最后一步,我们再建一个 audit_log
表,用来记录操作员操作「卡拉云银行」客户管理系统时的操作行为。方便管理员在发生问题时,有 log 可查。
Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;
Output
Query OK, 0 rows affected (0.02 sec)
至此,你作为「卡拉云银行」的程序员,已经把客户管理系统的demo_kalacloud
数据库和四张表建立完成。接下来,我们将对这个管理系统的关键节点增加对应的触发器。
1.BEFORE INSERT
触发器使用方法
作为严谨的银行客户管理系统,对任何写入系统的数据都应该提前检测,以防止错误的信息被写进去。
在写入前检测数据这个功能,我们可以使用BEFORE INSERT
触发器来实现。
在操作者对 sales
表中的sales_amount
字段进行写操作时,系统将在写入(INSERT
)前检查数据是否符合规范。
我们先来看一下,创建触发器的基本语法。
DELIMITER //
CREATE TRIGGER [触发器的名字]
[触发器执行时机] [触发器监测的对象]
ON [表名]
FOR EACH ROW [触发器主体代码]//
DELIMITER ;
触发器的结构包括:
DELIMITER //
:MySQL 默认分隔符是;
但在触发器中,我们使用//
表示触发器的开始与结束。[触发器的名字]
:这里填写触发器的名字[触发器执行时机]
:这里设置触发器是在关键动作执行之前触发,还是执行之后触发。[触发器监测的对象]
:触发器可以监测INSERT
、UPDATE
、DELETE
的操作,当监测的命令对触发器关联的表进行操作时,触发器就被激活了。[表名]
:将这个触发器与数据库中的表进行关联,触发器定义在表上,也附着在表上,如果这个表被删除了,那么这个触发器也随之被删除。FOR EACH ROW
:这句表示只要满足触发器触发条件,触发器都会被执行,也就是说带上这个参数后,触发器将监测每一行对关联表操作的代码,一旦符合条件,触发器就会被触发。[触发器主体代码]
:这里是当满足触发条件后,被触发执行的代码主体。这里可以是一句 SQL 语句,也可以是多行命令。如果是多行命令,那么这些命令要写在BEGIN...END
之间。
注:在创建触发器主体时,还可以使用OLD
和NEW
来获取 SQL 执行INSERT
,UPDATE
和DELETE
操作前后的写入数据。这里没看明白没关系,我们将会在接下来的实践中,展开讲解。
讲到这里,大家看了一大堆云里雾里的概念,如果没看懂,也别担心。接下来进入实践环节,只要跟着贴代码看返回结果,很快你就能够通透理解触发器了。
现在,我们来创建第一个触发器,BEFORE INSERT
(在执行 insert
之前,执行触发器)。这个触发器用于监测操作者在写入 sales
表中的 sales_amount
值时,这个值是否大于 10000
,如果大于,那么返回错误信息进行报错。
登录 MySQL Server 后,我们创建一个触发器:
DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "你输入的销售总额超过 10000 元。";
END IF//
DELIMITER ;
上面这段代码中,我们使用IF...THEN...END IF
来创建一个监测 INSERT
语句写入的值是否在限定的范围内的触发器。
这个触发器的功能时监测 INSERT
在写入sales_amount
值时,这个新增的(NEW
)值是否符合条件( > 10000
)。
当操作员录入一个超过 10000 的数字,会返回如下错误信息:
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '你输入的销售总额超过 10000 元。';
我们来试试看,看看触发器是否已启用。
我们向 sales_amount
中插入一条 11000
的值。
Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');
命令行返回错误信息,这就是我们刚刚创建触发器时,填入的错误信息。与我们的设置一致。
下面我们 insert
一个值小于 10000
的数字:
Insert into sales(sales_id, customer_id, sales_amount) values('1','1','7700');
输入值为 7700
小于设定的 10000
,insert
命令执行成功。
Output
Query OK, 1 row affected (0.01 sec)
我们调出 sales
表,看看是否插入成功:
Select * from sales;
确认数据在表中:
通过这张表,我们可以看到,7700 已经插入到表中。
刚刚我们演示了在执行 insert
命令前,检测某个值是否符合设定,接着我们来看在执行 insert
之后,使用触发器将不同的值保存到不同的表中。连接数据库后需要开发后台系统?
试试卡拉云,拖拽组件连接 API 和数据库直接生成后台系统,两个月的工期降低至1天
立即试用
2.AFTER INSERT
触发器使用方法
接着我们讲解 AFTER INSERT
,触发器在监测到我们成功执行了 INSERT
命令后,再执行触发器中设置好的代码。
例如:在银行账户系统中,当我们新建一个账户后,我们将创建成功信息写入对应的 customer_status
表中。
在这个案例中,你作为「卡拉云银行」的程序员,现在要创建一个AFTER INSERT
触发器,在创建新客户账户后,将成功信息写入customer_status
表中
要创建AFTER INSERT
触发器,请输入以下命令:
DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, '账户创建成功')//
DELIMITER ;
Output
Query OK, 0 rows affected (0.00 sec)
这个触发器在操作者向 customers
表中 INSERT
新客户信息后,再向 customer_status
表对应的行中写入成功信息。
现在我们 INSERT
一条信息,看看触发器是否已启用:
Insert into customers (customer_id, customer_name, level )values('4','Xing Wang','VIP');
Output
Query OK, 1 row affected (0.01 sec)
记录 INSERT
成功,接着我们来检查customer_status
表中是否写入了对应的成功数据。
Select * from customer_status;
这里可以看到,我们向 customers
表插入了一个customer_id
为 4
的新用户 ,随后,触发器根据代码自动向customer_status
表中也插入了一个 customer_id
为 4
的开户成功信息。
AFTER INSERT
特别适合这种状态变更的关联写入操作。比如开户、暂停、注销等各类状态变更。
到这里,触发器在INSERT
执行前、后的应用,我们已经讲完了,接着我们来讲 UPDATE
触发器。
扩展阅读:《MySQL 配置文件 my.cnf / my.ini 逐行详解》
3.BEFORE UPDATE
触发器使用方法
BEFORE UPDATE
触发器与BEFORE INSERT
触发器非常类似,我们可以使用BEFORE UPDATE
触发器在更新数据之前,先做一次业务逻辑检测,避免发生误操作。
刚刚我们创建示例数据库时,创建了两个级别的客户,VIP 和 BASIC 级别。卡拉云银行的客户一旦升级至 VIP,就不能再降级至 BASIC 级别了。
我们使用 BEFORE UPDATE
来贯彻这一规则,这个触发器将在 UPDATE
语句执行之前,先判断是否为降级行为,如果是,则输出报错信息。
我们来创建这个触发器:
DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'VIP 级别客户不能降级为普通级别客户';
END IF //
DELIMITER ;
我们可以使用 OLD
来获取执行 UPDATE
命令前,客户的 level
值。同样,我们使用该IF...THEN...END IF
语句来对 level
值是否符合规则进行判断。
我们先来查看一下 customers
表中的数据。
select * from customers;
好,我们选一个已经是 VIP 级别的客户,对他进行降级操作,看看我们的触发器是否能够正确执行。
接下来,运行以下 SQL 命令,试试能不能将 customer_id
为 3
的 VIP 客户降级成 BASIC
客户:
Update customers set level='BASIC' where customer_id='3';
执行代码后,命令行返回错误信息:
这说明我们刚刚设置的触发器已经起作用了。
接着我们来试试,对一个BASIC
级别的客户运行相同的命令,看看能不能把他升级到VIP
级别:
Update customers set level='VIP' where customer_id='2';
执行成功:
Output
Rows matched: 1 Changed: 1 Warnings: 0
我们再来看一下 customers
表中的数据情况:
select * from customers;
可以看到刚才 customer_id
为 2
的 BASIC
客户已经升级为 VIP
客户。
BEFORE UPDATE
触发器用于在更新数据前进行确认,很好的守护了系统的业务规则。接着我们来看看 AFTER UPDATE
在客户管理系统中的应用。
扩展阅读:《MySQL Workbench 操作 MySQL / MariaDB 数据库中文教程》
4.AFTER INSERT
触发器使用方法
本节我们来演示 AFTER UPDATE
在实际中的应用。AFTER UPDATE
多用于 log 记录,在管理系统多操作者使用的环境中,管理员需要设置操作 log 记录,以便在出问题时,可以查看操作者对表编辑的操作,可追根溯源。
我们先来创建一个对 sales
表操作的 log 记录触发器。
当操作者对 sales
表进行修改后,操作记录会被写入 audit_log
表中。
触发器将监测用户 ID 、更新前的销售总额、更新后的销售总额、操作者 ID、修改时间等信息,作为 log 存入 audit_log
表中。
使用以下命令建立这个 log 记录触发器:
DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;
当操作者对 sales
表中的一条客户信息进行 UPDATE
操作时,触发器会在UPDATE
操作之后,将操作行为记录在 audit_log
中。包括 sales_id
,修改 sales_amount
值的前后变化。
销售总额的变化是审计的关键数据,所以要把它记录在 audit_log
中。使用OLD
来获取更新前的 sales_amount
值,使用 NEW
来获取更新后的值。
另外我们还要记录修改 sales
表的操作者信息及操作时间。
你可以使用 SELECT USER()
来检测当前操作用户的账号,用 NOW()
语句抓去当前服务器日期和时间。
为了测试这个触发器,我们先在 sales
表中创建一条信息记录:
Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');
Output
Query OK, 1 row affected (0.00 sec)
接下来,我们来更新这条记录:
Update sales set sales_amount='9000' where sales_id='5';
您将看到以下输出:
Output
Rows matched: 1 Changed: 1 Warnings: 0
理论上,我们更新了 sales
表后,触发器应该触发了操作,将我们刚刚的修改记录到了audit_log
表中。我们用以下命令,看看audit_log
表中是否已经有记录了。
Select * from audit_log;
如下表,触发器更新了audit_log
表,表中包含了sales_amount
更新前的旧值和更新后的新值。
至此,使用 AFTER UPDATE
制作的 log 自动记录触发器就完成了。
下一节,我们来学习 DELETE
相关的触发器。
扩展阅读:《如何查看 MySQL 数据库、表、索引容量大小?找到占用空间最大的表》
5.BEFORE DELETE
触发器使用方法
BEFORE DELETE
触发器会在DELETE
语句执行之前调用。
这些类型的触发器通常用于在不同的相关表上强制执行参照完整性。
BEFORE DELETE
的应用场景通常是确保有关联的数据不被错误的误删除掉。
例如:sales
表通过customer_id
与customers
表相关联。如果操作者删除了customers
表中的一条数据,那么 sales
表中某些数据就失去了关联线索。
为了避免这种情况的发生,我们需要创建一个 BEFORE DELETE
触发器,防止记录被误删除。
DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '这位客户有相关联的销售记录,不能删除。';
END IF//
DELIMITER ;
现在,我们试着删除有销售关联信息的客户:
Delete from customers where customer_id='2';
所以,你会看到以下输出:
这个触发器做到了先检测 sales
是否与正要被删除的 customers
表中的数据有关联,防止有关联信息的数据被误删除。
不过有时候,我们需要删除主数据后,再让系统自动帮我们删除与之相关联的其他所有数据。这时,我们就要用到 AFTER DELETE
这个触发器了。
扩展阅读:《在 MySQL 中 DATETIME 和 TIMESTAMP 时间类型的区别及使用场景 - 实战案例讲解》
6.AFTER DELETE
触发器使用方法
接着说说 AFTER DELETE
,一旦记录被成功删除,这个触发器就会被激活。
这个触发器在实际场景用的应用也比较广泛。比如银行系统中的升级降级操作,当客户花掉自己的账户积分后,激活触发器,触发器可以判断剩余积分是否满足客户当前等级,如果不满足,自动做降级操作。
AFTER DELETE
触发器的另一个用途是在删除主表中的数据后,与这个主表关联的数据,一起自动删除。
我们来看一下这个触发器如何创建:
DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;
接下来,我们来试试这个触发器。删除销售记录中 customer_id
为 2
的销售记录:
Delete from sales where customer_id='2';
Output
Query OK, 1 row affected (0.00 sec)
接着我们检查以下 customers
表中的关联信息是否一起自动删除:
Select * from customers where customer_id='2';
命令行会返回 Empty Set
的结果,我们刚刚删除了 sales
表中的信息后,customers
表中的关联信息也被一起删除了。
以上就是 MySQL 触发器的六种使用方式和对应的场景。
扩展阅读:《最好用的 10 款 MySQL / MariaDB 管理工具横向测评 - 免费和付费到底怎么选?》
7.查看触发器
(1)直接查看触发器
当我们想查看数据库中的触发器有哪些时,可用以下命令:
SHOW TRIGGERS;
后面加上 \G
是触发器列表竖排列:
SHOW TRIGGERS \G
刚刚我们创建的触发器都罗列在这个列表当中了。
(2)在 triggers 表中查看触发器信息
在 MySQL Server 中,数据库 information_schema
的 triggers
表中存着所有触发器的信息。所有我们可以通过 SELECT
来查看。
SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名称';
当然,也可以不指定触发器名称,来查看所有。
SELECT * FROM information_schema.triggers \G
扩展阅读:《如何在 MySQL / MariaDB 中查询数据库中带有某个字段/列名的所有表名》
8.删除触发器
最后,咱们来说说如何删除触发器。删除命令也很简单,Drop trigger 触发器名字
即可。
Drop trigger [触发器名称];
例如,咱们把刚刚创建的最后一个触发器删掉:
Drop trigger delete_related_info;
Output
Query OK, 0 rows affected (0.00 sec)
特别提示:我们不能对已经创建好的触发器进行修改。如果你想修改,只能先删除,再重新创建。
扩展阅读:《MySQL / MariaDB 中如何存储图片 BLOB 数据类型详解》
9.总结
在本教程中,我们展示了触发器的六种形式,即在INSERT
、DELETE
、UPDATE
执行前或后执行触发器,以及对应的六个实战案例。
BEFORE INSERT
: 在插入数据前,检测插入数据是否符合业务逻辑,如不符合返回错误信息。AFTER INSERT
: 在表 A 创建新账户后,将创建成功信息自动写入表 B 中。BEFORE UPDATE
:在更新数据前,检测更新数据是否符合业务逻辑,如不符合返回错误信息。AFTER UPDATE
:在更新数据后,将操作行为记录在 log 中BEFORE DELETE
:在删除数据前,检查是否有关联数据,如有,停止删除操作。AFTER DELETE
:删除表 A 信息后,自动删除表 B 中与表 A 相关联的信息。