Mysql触发器 - 乐耶园

一、触发器初接触

前段时间，在医院上线项目，有个需求是：Java技术往表里一直写入数据，需要我将这些数据在存到另一个表中。当然，业务逻辑也能实现，但是想直接通过mysql来解决掉这个问题。于是就接触到了触发器…

二、触发器含义

触发器可以让你在增、删、改的时候执行一些特定的操作。可以在mysql中指定sql语句在执行前触发或执行后触发。

三、实现需求举例

例如我上述的需求，当一个数据表中新增了数据后，就立马同步到另一个表中。
当购买一个商品后，订单表中新增一条数据，势必会造成库存减少。可用mysql触发器来实现。
在写入数据前，进行数据的校验。

业务逻辑中的代码可以交予触发器来实现，触发器实现的也能交予业务逻辑来实现，具体是哪个更好，需要考虑业务逻辑、优化、简便…

扩展文章：大型系统必须得要存储过程和触发器吗？

四、触发详解

４.1基本语法：

delimiter 自定义结束符号
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin 
    -- 触发器内容主体，每行用分号结尾
end
自定义的结束符号
delimiter ;

关于on 表 for each：触发对象，触发器绑定的实质是表中的所有行，因此当每一行发生指定改变时，触发器就会被触发。

4.2 关于触发事件

只要数据发生（增删改）改变，就可以引起触发事件。

INSERT
DELETE
UPDATE

4.3 关于触发时间

before：在数据改变发生前来触发。
after：在数据改变发生后来触发。

4.4 关于触发对象

表中的每一行，表就是触发对象。

4.5 关于new和old

触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中

触发器事件 new和old INSERT 没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据 DELETE 既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据 UPDATE 没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据

五、触发器的优缺点

优点：

触发器可以代替业务逻辑，减少客户端和服务器之间的通信。
触发器基于行触发，具有事务的原子特性。
一个好的触发器可以简化很多应用逻辑。
可以对数据进行校验。

缺点：

触发器的出现的问题很难排查，增加数据库的维护成本。
复杂逻辑会使触发器逻辑层层嵌套，级联关系追溯起来比较麻烦。
编码中存储过程的代码是明式调用，触发器是隐式调用，不利于代码的维护与阅读。
可能会造成死锁和锁等待，如果触发器执行失败，出现sql报错，我们可能也不知道这是触发器的原因。
可移植性差。

扩展文章：为什么大家都不推荐使用MySQL触发器而用存储过程？

六、创建一个简单的触发器

现在有两张空数据表：

student表：

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | tinyint(255) | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
| sex   | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

stulog表：

mysql> desc stulog;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | tinyint(11) | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

6.1 创建一个触发器

该触发器逻辑：往student表中插入（触发条件）一条数据，插入后（触发时间），自动把新数据中的name字段给同步到stulog表中

mysql> delimiter ##
mysql> -- 创建触发器
mysql> create trigger test after insert on student for each row 
    -> begin 
    ->     insert into stulog VALUES(null,new.name);
    -> end 
    -> ##
Query OK, 0 rows affected (0.03 sec)

6.2 插入一条数据

mysql> insert into student (name,sex) values("光头强","男");
Query OK, 1 row affected (0.01 sec)

6.3 查看student中的数据

mysql> select * from student;
+----+-----------+------+
| id | name      | sex  |
+----+-----------+------+
|  1 | 光头强    | 男   |
+----+-----------+------+
1 row in set (0.00 sec)

6.4 查看stulog中的数据

mysql> select * from stulog;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 光头强    |
+----+-----------+
1 row in set (0.00 sec)

发现已经将新插入的数据光头强给同步过来了。

七、查看一个触发器

7.1 查看所有触发器

mysql> show triggers;

7.2 查看某个触发器的信息

mysql> show create trigger test;

八、删除修改一个触发器

触发器不能修改，只能删除。

删除一个表的同时，也会自动删除该表上的触发器。另外，触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，再重新创建。

删除命令：

mysql> drop trigger test;
Query OK, 0 rows affected (0.00 sec)

九、触发器的原子性

MYISAM：

如果含有触发的表是MYISAM的，如果其中一个表报错，另一个表也会正常执行。

InooDb：

如果含有触发的表是InooDb类型的，那么一个表报错，另一个表将会回滚。

十、总结

具体是否是使用触发器，需要多方面的考量业务逻辑、硬件容量、并发等各方面因素。

在这里插入图片描述

参考文章：https://blog.csdn.net/babycan5/article/details/82789099 《高性能MySQL》