Mysql数据库-触发器

4. Mysql数据库-触发器

4.1 触发器概述

1. 触发器(trigger)是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。

2. 触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

3. 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

# 举例
 1. 地雷(触发器) : 是要有人触发它的引爆机制, 它才会爆炸
 2. 根据触发机制的不同: insert/update/delete

4.1.1 触发器解释

1595944780499

在上面的对于 account 表的 增删改操作中，我们可以使用触发器对其操作进行记录，将操作的日志记录到 account_log 表中。

4.1.2 触发器类型

1595944012882

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;

测试如下：

image-20210213173450823

创建 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;

测试如下：

image-20210213173909177

扩展 : 创建 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;

测试如下：

image-20210213175554450

4.3 查看触发器

-- 可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。
show triggers ;

1595949829255

# 解释
1. trigger: 触发器名
2. event : 监听的事件 (引爆机制)
3. table : 监听的表
4. statement : 触发器语句(begin和end之间的内容)
5. timing : 时机

4.4 删除触发器

--语法
drop trigger 触发器名;

-- 删除account_delete触发器
drop trigger account_delete;