4.1 触发器概述
1. 触发器(trigger)是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。
2. 触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
3. 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
# 举例
1. 地雷(触发器) : 是要有人触发它的引爆机制, 它才会爆炸
2. 根据触发机制的不同: insert/update/delete
4.1.1 触发器解释
在上面的对于 account 表的 增删改操作中，我们可以使用触发器对其操作进行记录，将操作的日志记录到 account_log 表中。
4.1.2 触发器类型
4.2 创建触发器
4.2.1 语法说明
语法:
delimiter $ -- delimiter空格$ , 表示声明结束符为$
create trigger 触发器名称
before/after -- 在...之前/在...之后
insert/update/delete -- 触发事件
on 表名 -- 监听的表
for each row -- 行级触发器
begin
触发器要执行的功能
end$
delimiter; -- 重新声明结束符为分号
# delimiter 定界符
1. 该关键字用来声明SQL语句的结束符,用来告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。
2. 默认情况下，delimiter是分号, 在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。
3. 一般情况下, 我们不需要重新声明结束符.
但是像上面的语法中 '触发器的要执行的功能' 的完整内容是begin开始到end结尾, 其中begin和end中间的内容是完整的sql语句,会涉及到分号.
因为默认结束符是分号,如果不修改结束符,那么mysql一遇到分号，它就要自动执行,触发器创建语句就会执行不完整,从而报错.
所以像这样的语句, 就需要事先把delimiter换成其它符号.
执行示例：
-- 表示声明结束符为$
mysql> delimiter $
mysql>
-- 此时使用分号; 作为结尾不能执行SQL语句了。需要加上刚刚声明的 $ 结束符才会执行
mysql> show databases; -- 使用 ; 按下回车，不会执行SQL
-> $ -- 使用 $ 按下回车，执行SQL
+---------------------------+
| Database |
+---------------------------+
| information_schema |
....
| testdb |
| userdemo |
+---------------------------+
18 rows in set (0.04 sec)
mysql>
-- 重新声明 ; 为结束符号
mysql> delimiter ;
mysql>
-- 可以使用 ; 按下回车执行SQL了
mysql> show databases;
+---------------------------+
| Database |
+---------------------------+
| information_schema |
....
| testdb |
| userdemo |
+---------------------------+
18 rows in set (0.04 sec)
4.2.2 创建示例
示例:
需求: 通过触发器记录 account 表的数据变更日志 , 包含增加, 修改 , 删除 ;
-- 数据准备
-- 创建账户表account
create table account(
id int primary key auto_increment,
name varchar(20),
money double
);
insert into account values(null,'张三',1000),(null,'李四',1000);
-- 创建日志表account_log
create table account_log(
id int(11) primary key auto_increment, -- 日志id
operation varchar(20), -- 操作类型(insert/update/delete)
operation_time datetime, -- 操作时间
operation_id int, -- 操作表的ID
operation_params varchar(500) -- 操作参数
);
执行如下：
-- 查询account表的数据
mysql> select * from account;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 1000 |
+----+--------+-------+
2 rows in set (0.01 sec)
-- 查询account_log日志表数据
mysql> select * from account_log;
Empty set (0.00 sec)
创建 insert 型触发器，完成插入数据时的日志记录 :
-- 创建 insert 型触发器，完成插入数据时的日志记录
delimiter $ -- 声明结束符为$
create trigger account_insert
after insert -- 插入操作之后
on account -- 当account表被插入数据之后
for each row -- 行级触发器
begin
-- 触发器功能: 往account_log添加一条日志: 记录插入操作的信息
-- new 关键字为新增的一条数据
-- new.id 表示插入到account表之后的id
-- 信息: 插入后(id=?,name=?,money=?)
insert into account_log
values(
null, -- id
'insert', -- operation
now(), -- operation_time
new.id, -- operation_id
concat('插入后(id=',new.id,',name=',new.name,',money=',new.money) -- operation_params
);
end$
delimiter ; -- 声明结束符为 ;
-- 查看触发器
SHOW TRIGGERS;
-- 测试
-- 向account表添加一条记录
insert into account values(null,'王五',2000);
-- 查询account表
select * from account;
-- 查询account_log表
select * from account_log;
测试如下：
-- 查看当前的account表数据
mysql> select * from account;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 1000 |
+----+--------+-------+
2 rows in set (0.00 sec)
-- 查看当前account_log表数据
mysql> select * from account_log;
Empty set (0.00 sec)
-- 往account表插入一条数据，触发 触发器 account_insert
mysql> insert into account values(null,'王五',2000);
Query OK, 1 row affected (0.01 sec)
-- 查看account_log中由触发器生成的数据
mysql> select * from account_log;
+----+-----------+---------------------+--------------+---------------------------------------+
| id | operation | operation_time | operation_id | operation_params |
+----+-----------+---------------------+--------------+---------------------------------------+
| 1 | insert | 2021-02-13 17:20:07 | 3 | 插入后(id=3,name=王五,money=2000 |
+----+-----------+---------------------+--------------+---------------------------------------+
1 row in set (0.00 sec)
mysql>
创建 update 型触发器，完成更新数据时的日志记录 :
-- 创建 update 型触发器
delimiter $ -- 声明结束符 $
create trigger account_update -- 创建触发器 account_update
after update -- 在 update 操作之后触发
on account -- 监听 account 表
for each row -- 行级触发器
begin
-- 往account_log写入日志信息
-- old关键字：update之前的数据；new关键字：update之后的数据
insert into account_log
values(
null, -- id
'update', -- operation
now(), -- operation_time
new.id, -- operation_id
concat( '修改前(id=',old.id,',name=',old.name,',money=',old.money,')',
'修改后(id=',new.id,',name=',new.name,',money=',new.money,')')); -- operation_params
end$
delimiter ; -- 声明结束符 ;
-- 测试
-- 修改account表中农李四的金额为2000
update account set money=2000 where id=2;
-- 查询account表
select * from account;
-- 查询account_log表
select * from account_log;
测试如下：
创建 delete 型的触发器 , 完成删除数据时的日志记录 :
-- 创建 delete 型的触发器 , 完成删除数据时的日志记录
delimiter $ -- 声明结束符 $
create trigger account_delete -- 创建触发器 account_delete
after delete -- 在delete操作后触发
on account -- 监听 account 表
for each row -- 行级触发器
begin
-- 往account_log写入日志信息
insert into account_log
values(
null, -- id
'delete', -- operation
now(), -- operation_time
old.id, -- operation_id
concat('删除前(id=',old.id,',name=',old.name,',money=',old.money,')')); -- operation_params
end$
delimiter ; -- 声明结束符 ;
-- 测试
-- 删除account表中王五
delete from account where id = 3;
-- 查询account表
select * from account;
-- 查询account_log表
select * from account_log;
测试如下：
扩展 : 创建 insert-before 触发器
-- 数据插入之前对要出入的money进行判断,如果money<100,那么就设置money=100
delimiter $ -- 声明结束符 $
create trigger account_before -- 创建触发器 account_before
before INSERT -- 在 insert 之前触发
on account -- 监听 account 表
for each row -- 行级触发器
begin
-- 判断新插入的数据 new.money 如果小于 100，那么则将 new.money 设置为 100
if new.money < 100
then set new.money = 100;
end if;
end$
delimiter ; -- 声明结束符 ;
-- 测试
-- 插入一条数据, 准备插入money=99,经过触发器之后, 实际插入money=100
insert into account values(null,'马六',99);
-- 查询account表
select * from account;
测试如下：
4.3 查看触发器
-- 可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。
show triggers ;
# 解释
1. trigger: 触发器名
2. event : 监听的事件 (引爆机制)
3. table : 监听的表
4. statement : 触发器语句(begin和end之间的内容)
5. timing : 时机
4.4 删除触发器
--语法
drop trigger 触发器名;
-- 删除account_delete触发器
drop trigger account_delete;