文章目录
一、概述
（一）什么是触发器
在实际开发中往往会碰到这样的情况：
当我们对一个表进行数据操作时，需要同步对其它的表执行相应的操作，正常情况下，如果我们使用 sql语句进行更新，将需要执行多条操作语句！
比如，在某些棋牌游戏中，当玩家充值金币后，玩家表数据库中金币增加的同时，玩家所属的代理会得到相应的提成并计入代理的收益中，即代理数据库表提成记录字段也要同步更新。而以上的场景，我们可以轻松使用触发器来实现！
- 触发器（TRIGGER）是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。只有当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL 从 5.0.2 版本开始支持触发器。
- 触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行
- 触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作；
- 使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发；
（二）触发器与存储过程的异同
相同点：1. 触发器是一种特殊的存储过程，触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段。
不同点：2. 存储器调用时需要调用SQL片段，而触发器不需要调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。
（三）触发器的作用
- 可在写入数据前，强制检验或者转换数据(保证护数据安全)
- 触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚
二、创建触发器
在 MySQL 中创建触发器通过 SQL 语句 CREATE TRIGGER 来实现，
语法形式：
CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT ON TABLE_NAME FOR EACH ROW trigger_STMT
在上述语句中，参数 trigger_name 表示要创建的触发器名；参数 BEFORE 和 AFTER 指定了触发器执行的时间，前者在触发器事件之前执行触发器语句，后者在触发器事件之后执行触发器语句；参数 trigger_EVENT 表示触发事件，即触发器执行条件，包含 DELETE、INSERT 和 UPDATE 语句；参数TABLE_NAME 表示触发事件的操作表名；参数 FOR EACH ROW 表示任何一条记录上的操作满足触发事件都会触发该触发器；参数 trigger_STMT 表示激活触发器后被执行的语句。执行语句中如果要引用更新记录中的字段，对于 INSERT语句,只有 NEW 是合法的，表示当前已插入的记录；对于 DELETE 语句，只有 OLD 才合法，表示当前删除的记录；而 UPDATE 语句可以和 NEW（更新后）以及 OLD（更新前）同时使用
注意：
1.不能创建具有相同名字的触发器。另外，对于具有相同触发程序动作时间和事件的给定表，不能有两个触发器。因此，对于有经验的用户，在创建触发器之前，需要查看 MySQL 中是否已经存在该标识符的触发器和触发器的相关事件
2.在 MySQL 5 中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一，即同一数据库中的两个表可能具有相同名字的触发器
3.每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update
实例：
use school; #选择数据库 school
CREATE TABLE class (`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL,
`teacher` varchar(64) DEFAULT NULL,
`count` int DEFAULT 0,
UNIQUE KEY `id` (`id`)
); #创建班级表 class
insert into class values(101, '萌新一班', 'Martin', 0),(102, '萌新二班', 'Rock', 0),(103, ' 萌新三班', 'Janny', 0);
#创建成绩表 grade
CREATE TABLE `student` (`id` int NOT NULL AUTO_INCREMENT UNIQUE,
`name` varchar(64) DEFAULT NULL,
`class_id` int DEFAULT NULL,
`sex` enum('F','M') DEFAULT NULL
);
create trigger tri_insert_student after insert on student for each row update class set count=count+1 where class.id = NEW.class_id; #创建触发器，新增学员班级人数增 1
insert into student values(1,'小花',101,'M'),(2,'小红',102, 'F'),(3,'小军',102,'F'),(4,'小白',101,'F'); #插入多条记录
select count from class ; #查询 class 表人数
create trigger tri_delete_student after delete on student for each row update class set count=count-1 where id = OLD.class_id; #创建触发器，删除学员班级人数减 1
触发器包含多条执行语句
CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT ON TABLE_NAME FOR EACH ROW
BEGIN
trigger_STMT
END
在上述语句中，比“只有一条执行语句的触发器”语法多出来两个关键字 BEGIN 和 END，在这两个关键字之间是所要执行的多个执行语句的内容，执行语句之间用分号隔开。
在 MySQL 中，一般情况下用“;”符号作为语句的结束符号，可是在创建触发器时，需要用到“;”符号作为执行语句的结束符号。为了解决该问题，可以使用关键字 DELIMITER 语句。
例如，“DELIMITER ##”。可以将结束符号设置成“##”。
use school; #选择数据库 school
#创建成绩表 grade:
create table grade(id int UNIQUE AUTO_INCREMENT,
math tinyint unsigned,
chinese tinyint unsigned, english tinyint unsigned
);
insert into grade values(1, 80, 87, 91),(2, 72, 64, 89),(3, 54, 69, 87),(4, 78, 79, 89); #插入多条记录
DELIMITER $$
create trigger tri_delete_student after delete on student for each row
BEGIN
Delete from grade where id = OLD.id; #删除成绩表中的记录
update class set count=count-1 where id = OLD.class_id; #更新班级表中的记录
END;
$$
DELIMITER ;
三、查看触发器
（一）SHOW TRIGGERS 语句查看触发器
那么如何查看 MySQL 软件中已经存在的触发器呢？在 MySQL 软件中查看已经存在的触发器，通过 SQL 语句SHOW TRIGGERS 来实现，其语法形式如下，执行上面的 SQL 语句，执行结果如图 9-10 所示。
SHOW TRIGGERS ;
通过下图的执行结果可以发现，执行完“SHOW TRIGGERS”语句后会显示一个列表，在该列表中会显示出所有触发器的信息。其中，参数 Trigger 表示触发器的名称；参数 Event 表示触发器的激发事件；参数 Table 表示触发器对象触发事件所操作的表；参数 Statement 表示触发器激活时所执行的语句；参数 Timing 表示触发器所执行的时间
（二）查看系统表 triggers 实现查看触发器
在 MySQL 中，在系统数据库 information_schema 中存在一个存储所有触发器信息的系统表 triggers，因此查询该表格的记录也可以实现查看触发器功能。系统表 triggers 的表结构
use information_schema; #选择数据库 information_schema
select * from triggers;
select * from triggers where trigger_name=’tri_delete_student’; # 查 询 系 统 表triggers 中的触发器
四、删除触发器
在 MySQL 软件中，可以通过 DROP TRIGGER 语句或通过工具来删除触发器。删除触发器可以通过 SQL 语句 DROP TRIGGER 来实现，其语法形式如下：
DROP TRIGGER trigger_name;
在上述语句中，参数 trigger_name 表示所要删除的触发器名称。
注意：触发器不能修改，只能删除
五、触发器类型NEW和OLD的使用
触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中
语法：old/new.字段名
需要注意的是，old 和 new 不是所有触发器都有
| 触发器类型 | new和old的使用 |
|---|---|
| INSERT型触发器 | 没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据 |
| UPDATE型触发器 | 既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据 |
| DELETE型触发器 | 没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据 |
create database if not exists mydb01_trigger;
use mydb01_trigger;
-- 用户表
create table if not exists user(
uid int primary key auto_increment,
username varchar(50) not null,
password varchar(50) not null
)default charset=utf8;
-- 用户信息操作日志表
create table if not exists user_logs(
id int primary key auto_increment,
time timestamp,
log_text varchar(255)
)default charset=utf8;
-- 需求1:当user表添加一行数据，则会自动在user_log添加日志记录
-- 定义触发器： trigger_test1
create trigger trigger_test1 after insert on user for each row
insert into user_logs values(NULL,now(),'new');
-- 在user表添加数据，让触发器自动执行
insert into user values(3,'zbb','123456');
-- NEW和OLD
-- insert 触发器
-- NEW
-- 定义触发器： trigger_test2
drop trigger trigger_test1
create trigger trigger_test2 after insert on user for each row
insert into user_logs values(NULL,now(),concat('有新用户添加,信息为：',NEW.username,NEW.password));
insert into user values(4,'abb','123456');
-- update 触发器
-- NEW
-- 定义触发器： trigger_test3
-- OLD
drop trigger trigger_test2
create trigger trigger_test3 after update on user for each row
insert into user_logs values(NULL,now(),concat('有用户信息修改,旧数据是：',OLD.uid,OLD.username,OLD.password));
update user set password = '00000' where uid=3;
-- NEW
drop trigger trigger_test3
create trigger trigger_test4 after update on user for each row
insert into user_logs values(NULL,now(),concat('有用户信息修改：新数据是',NEW.uid,NEW.username,NEW.password));
update user set password = '666666' where uid=3;
-- delete类型触发器
-- OLD
create trigger trigger_test5 after delete on user for each row
insert into user_logs values(NULL,now(),concat('有用户被删除，删除信息为：',OLD.uid,OLD.username,OLD.password));
delete from user where uid=3;
参考文章：MySQL的触发器
972
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
