关于mysql:MySQL-MariaDB-触发器的创建使用查看删除教程及应用场景实战案例

本文首发：https://kalacloud.com/blog/how-to-manage-and-use-mysql-database-triggers

触发器（Trigger）是 MySQL 中十分实用的一个性能，它能够在操作者对表进行「增删改」之前（或之后）被触发，主动执行一段当时写好的 SQL 代码。

本教程率领大家在实践中学习，你将学到触发器在理论利用场景中的重要利用。

在这个教程中，你是「卡拉云银行」的程序员，你正在搭建一套银行客户管理系统。在这套零碎中，你须要设置在INSERT 表之前检测操作者是否输出谬误数据、在 UPDATE 时，记录操作者的行为 log，以及在DELETE 时，判断删除的信息是否合乎删除规定。这三类操作都能够应用 MySQL 触发器来实现。

如果你正在数据库的根底上搭建一套数据库管理工具或企业外部工具，举荐你试试我开发的卡拉云，详情见后文。

本教程将带你一起实际的案例

• BEFORE INSERT：在插入数据前，检测插入数据是否合乎业务逻辑，如不合乎返回错误信息。
• AFTER INSERT：在表 A 创立新账户后，将创立胜利信息主动写入表 B 中。
• BEFORE UPDATE：在更新数据前，检测更新数据是否合乎业务逻辑，如不合乎返回错误信息。
• AFTER INSERT：在更新数据后，将操作行为记录在 log 中
• BEFORE DELETE：在删除数据前，查看是否有关联数据，如有，进行删除操作。
• AFTER DELETE：删除表 A 信息后，主动删除表 B 中与表 A 相关联的信息。

先决条件

在开始之前，请确保您具备以下条件：

• 一台配置好的 Ubuntu 服务器，root 账号。
• 服务器上配置好 MySQL Server（配置 MySQL 请看 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 数据库和四张表建设实现。接下来，咱们将对这个管理系统的要害节点减少对应的触发器。

扩大浏览：《如何应用 MySQL 慢查问日志进行性能优化 – Profiling、mysqldumpslow 实例详解》

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;

输入确认数据在表中：

![确认数据在表中]](https://kalacloud.com/static/…)

通过这张表，咱们能够看到，7700 曾经插入到表中。

刚刚咱们演示了在执行 insert 命令前，检测某个值是否合乎设定，接着咱们来看在执行 insert 之后，应用触发器将不同的值保留到不同的表中。

扩大浏览：《如何在两台服务器之间迁徙 MySQL / MariaDB 数据库 阿里云腾讯云迁徙案例》

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 INSERT：在更新数据后，将操作行为记录在 log 中
• BEFORE DELETE：在删除数据前，查看是否有关联数据，如有，进行删除操作。
• AFTER DELETE：删除表 A 信息后，主动删除表 B 中与表 A 相关联的信息。

接着举荐一下卡拉云，只有你会写 MySQL，就能应用卡拉云搭建本人的数据工具，比方，数据看板，企业 CRM、ERP，权限治理后盾，对账零碎等。

卡拉云是新一代低代码开发工具，免装置部署，可一键接入包含 MySQL 在内的常见数据库及 API。可依据本人的工作流，定制开发。无需繁琐的前端开发，只须要简略拖拽，即可疾速搭建企业外部工具。数月的开发工作量，应用卡拉云后可缩减至数天，欢送收费试用卡拉云。

卡拉云可一键接入常见的数据库及 API

卡拉云可依据公司工作流需要，轻松搭建数据看板或其余外部工具，并且可一键分享给组内的小伙伴。

![卡拉云 5 分钟搭建企业外部工具(https://kalacloud.com/5400a60…)

下图为应用卡拉云在 5 分钟内搭建的「优惠券发放核销」后盾，仅须要简略拖拽即可疾速生成前端组件，只有会写 SQL，便可搭建一套趁手的数据库工具。** 欢送收费试用卡拉云。**

心愿本教程对你有所帮忙。更多无关 MySQL 教程，欢送拜访卡拉云查看更多。

无关 MySQL 教程，可持续拓展学习：

• 如何近程连贯 MySQL 数据库，阿里云腾讯云外网连贯教程
• 如何在 MySQL / MariaDB 中导入导出数据，导入导出数据库文件、Excel、CSV
• 如何在两台服务器之间迁徙 MySQL 数据库 阿里云腾讯云迁徙案例
• MySQL 重置自增 ID (AUTO_INCREMENT)教程 – 完满保留表数据的终极解决方案