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 中

五、触发器的优缺点

优点：

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

缺点：

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

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

现在有两张空数据表：

• 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 类型的，那么一个表报错，另一个表将会回滚。

十、总结

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