MySQL分析与整理 — 触发器

￡神魔★判官ぃ 2023-02-25 10:18 19阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]

> 文章中所有操作均是在 MySQL 5.7 版本下进行的

触发器可以理解为和表有关系的特殊的存储过程，可以在 insert，update，delete 表中数据的时候触发执行。它是自动触发执行的，执行了预先编写好的若干条 SQL 语句。这些若干条 SQL 语句是一个事务操作，要么全部执行，要么都不执行。这些若干条 SQL 语句不能使用以显示或隐式方式开始或结束事务的语句，如start trans-action，commit 或 rollback等。

触发器的定义有那么几个要素必须要遵循：

*  触发器是定义在表上的。
 *  触发器是针对表的 insert，update，delete 操作触发的。
 *  触发器是在表进行 insert，update，delete 之前或者之后触发。
 *  load data 和 replace 语句，这两种语句也能引起触发器的触发。

注：load data 语句用于将一个文件装入到一个数据表中，相当与一系列的 insert 操作。replace 语句一般来说和 insert 语句很像，只是在表中有 primary key 或 unique索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，相当于 delete 和 insert 操作。

### 1 使用触发器 ###

#### 1.1 创建触发器 ####

触发器创建语法：
    create trigger trigger_name 
    trigger_time trigger_event 
    on table_name for each row 
    [trigger_order]
    trigger_body;
    解释：
    trigger_name：触发器的名称，自定义。
    trigger_time：触发器的触发时间，before或after。
    trigger_event：触发器的触发事件，insert，update，delete。
    table_name：触发器定义在哪个表上的，在哪张表上建立的触发器。
    trigger_body：触发器触发要执行的程序体，可以是一条SQL语句，也可以是用begin和end包含的一条或多条SQL语句。
    trigger_order：可以省略，MySQL5.7之后加入的功能。用于定义多个触发器，使用follows(之后尾随)或precedes(在…之先)来选择触发器执行的先后顺序。

关于 for each row：表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次，我们管它叫行级触发器。如果是熟悉 Oracle 的朋友会知道，Oracle 的触发器中的 for each row 是可以不写的，如果不写 for each row 被叫做语句级触发器，不管影响多少行数据触发器仅仅运行一次。但是在 MySQL 中，是不支持语句级触发器的，所以 for each row 不能不写。

#### 1.2 查看触发器 ####

在 MySQL 中，所有触发器信息都存储在 information\_schema 数据库下的 triggers 表中，可以使用 select 语句查询，如果触发器信息过多，最好通过 trigger\_name 字段指定或模糊查询。

select * from information_schema.triggers 
    where trigger_name = '触发器名'\G;
    -- 或者
    select * from information_schema.triggers 
    where trigger_name like '%触发器名%'\G;
    
    -- 还可以用 show trigger\G; 查看，没有条件是查看全部
    show trigger [from db_name] [like expr];
    -- 或者
    show trigger\G;

#### 1.3 使用触发器 ####

insert型触发器：插入某一行时激活触发器，通过insert，load data，replace语句触发。
    update型触发器：更改某一行时激活触发器，通过update语句触发。
    delete型触发器：删除某一行时激活触发器，通过delete，replace语句触发。
    
    MySQL中定义了new/NEW（大小学通用）和old/OLD，用来表示触发器的所在表中，触发了触发器的那一行数据。
    在insert型触发器中，new用来表示将要（BEFORE）或已经（AFTER）插入的新数据。
    在update型触发器中，old用来表示将要或已经被修改的原数据，new用来表示将要或已经修改为的新数据。
    在delete型触发器中，old用来表示将要或已经被删除的原数据。
    使用方法：new.columnName（columnName为相应数据表某一列名）
    old是只读的，而new则可以在触发器中使用set赋值，不会再次触发触发器，造成循环调用。

触发器的执行顺序（InnoDB 存储引擎下的数据库）：

*  如果 before 类型的触发器执行失败，sql 无法正确执行。
 *  sql 执行失败时，after 类型的触发器不会触发。
 *  after 类型的触发器执行失败，sql 会回滚。

MySQL 的触发器是按照 before 触发器，行操作，after 触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作，如果对事务表进行的操作，如果出现错误，那么将会被回滚，如果对非事务表进行操作，那么就无法回滚，数据可能会出错。

#### 1.4 删除触发器 ####

drop trigger [if exists] [schema_name.]trigger_name;
    schema_name是数据库的名称，如果省略了schema_name，删除当前数据库中触发器。
    trigger_name是要删除的触发器的名称。
    if exists是为了避免在没有触发器的情况下删除触发器。
    注：
    	执行drop trigger语句需要SUPER权限。
    	删除一个表的同时，也会自动删除该表上的触发器。
    	触发器不能更新或覆盖，修改触发器是先删除再重新创建。

### 2 触发器应用示例 ###

/*
    有两个表，商品表，订单表
    */
    -- 商品表
    drop table if exists tbl_product;
    create table tbl_product(
    	pid int primary key,	-- 产品编号
    	pd_name varchar(10),	-- 商品名
    	pd_price double,		-- 商品价格
    	pd_num int				-- 商品库存数量
    ) ENGINE=InnoDB default CHARSET=utf8;
    -- 插入3条初始数据
    insert into tbl_product values 
    (1001, '花生', 50, 2), (1002, '毛豆', 60, 8), (1003, '奶糖', 100, 10);
    -- 订单表
    drop table if exists tbl_orders;
    create table tbl_orders(
    	oid int primary key auto_increment,	-- 订单id自增长
    	pid int,	-- 订单的产品编号
    	pnum int	-- 订单的产品数量
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*
    业务描述：
    	在订单表上创建触发器，当插入订单之后，会根据订单的产品编号和数量自动减少产品的数量。
    */
    -- 创建触发器
    delimiter $
    create trigger trigger_orders 
    after insert on tbl_orders for each row 
    begin
    	update tbl_product set pd_num = pd_num - new.pnum where pid = new.pid;
    end
    $
    delimiter ;	-- 记得改回来
    /*
    以上命令解释：
    因为begin和end之间可以是一条或者多条sql语句组合，每条sql语句都是以“;”英文分号结束。
    MySQL数据库默认是以“;”英文分号认为结束和执行命令，所以会出现第一条sql的分号之后到end的命令不认。
    通过delimiter命令临时改变MySQL的默认结束命令为其它字符，这里改是了“$”。
    通知MySQL这个命令是到“$”结束。
    不用了，记得改回来，用“delimiter ;”命令恢复为英文分号。
    其中触发器中的new是存放插入的订单记录。
    */
    -- 插入执行测试数据，向tbl_orders插入数据
    insert into tbl_orders (pid, pnum) values (1002, 5);
    insert into tbl_orders (pid, pnum) values (1003, 6);
    -- 查看商品表中的库存变化，库存发生变化，说明触发器起作用了。
    select * from tbl_product;
    +------+---------+----------+--------+
    | pid  | pd_name | pd_price | pd_num |
    +------+---------+----------+--------+
    | 1001 | 花生    |       50 |      2 |
    | 1002 | 毛豆    |       60 |      3 |
    | 1003 | 奶糖    |      100 |      4 |
    +------+---------+----------+--------+

以上示例其实会出现库存不够的情况，MySQL 不能在触发器中通过回滚事务取消操作，其实我们可以把触发器设计的更完美一些。如下：

drop trigger trigger_orders;
    delimiter $
    create trigger trigger_orders 
    before insert on tbl_orders for each row 
    begin
    	-- 定义一个变量num
    	declare num int;
    	-- 查询出要插入订单商品的库存数量赋值给定义的变量num
    	select pd_num into num from tbl_product where pid = new.pid;
    	-- 判断 如果库存数据量>=订单的数量，执行插入订单，并修改相应库存
    	if num >= new.pnum then
    		update tbl_product set pd_num = pd_num - new.pnum where pid = new.pid;
    	end if;
    end
    $
    delimiter ;	-- 记得改回来
    -- 插入数据
    insert into tbl_orders (pid, pnum) values (1001, 5);
    -- 查看商品表中的库存变化，1001库存不够5，未发生变化。
    select * from tbl_product;
    +------+---------+----------+--------+
    | pid  | pd_name | pd_price | pd_num |
    +------+---------+----------+--------+
    | 1001 | 花生    |       50 |      2 |
    | 1002 | 毛豆    |       60 |      3 |
    | 1003 | 奶糖    |      100 |      4 |
    +------+---------+----------+--------+
    -- 再次插入数据
    insert into tbl_orders (pid, pnum) values (1001, 1);
    -- 查看商品表中的库存已变化，1001库存大于1，库存发生改变
    select * from tbl_product;
    +------+---------+----------+--------+
    | pid  | pd_name | pd_price | pd_num |
    +------+---------+----------+--------+
    | 1001 | 花生    |       50 |      1 |
    | 1002 | 毛豆    |       60 |      3 |
    | 1003 | 奶糖    |      100 |      4 |
    +------+---------+----------+--------+
    /*
    其实我们还可以设计的更完美一些，比如库存不够，订单数据都不能插入，这里就不再分析了。
    */

其实我们还可以设计的更完美一些，比如库存不够，订单数据就不能插入。

### 结语 ###

触发器的应用，随着项目或者产品的开发已经很少用了，很多业务处理和逻辑处理都已经移到程序层面上去判断和处理了，况且触发器在一定程度上影响了表的存储速度。所以当不在需要某个触发器的时候，最好是删除掉，以免造成一些数据上的混乱。最后有个点就是：**如果删除了某个表，这个表上曾经创建过的触发器将一并删除**。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]: /images/20230209/2a8f297f0ed747a3990f630c5b1215e4.png