MySQL触发器

MySQL触发器

触发器概念

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete，update）时就会激活它执行。

触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：

1. 监视地点(table)
2. 监视事件(insert/update/delete) 
3. 触发时间(after/before) 
4. 触发事件(insert/update/delete)

触发器基本语法如下所示：

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name 
    FOR EACH ROW #这句话在mysql是固定的  
    [begin]
    trigger_body
    [end]

语法中

• trigger_name：触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。
• trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发；

BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。

• 若希望验证新数据是否满足条件，则使用 BEFORE 选项
• 若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。

• trigger_event:：{ INSERT |UPDATE | DELETE }，触发该触发器的具体事件；

注意：三种触发器的执行时间如下。

• INSERT：将新行插入表时激活触发器。例如，INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活，也能被 LOAD DATA 语句激活。
• DELETE： 从表中删除某一行数据时激活触发器，例如 DELETE 和 REPLACE 语句。
• UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句。

• tbl_name：该触发器作用在tbl_name上；

同一个表不能拥有两个具有相同触发时刻和事件的触发器。

• FOR EACH ROW：一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

注意：每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER，因此每个表最多支持 6 个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。

创建触发器

创建 BEFORE 类型触发器

在 test_db 数据库中，数据表 tb_emp8 为员工信息表，包含 id、name、deptId 和 salary 字段，数据表 tb_emp8 的表结构如下所示。

mysql> SELECT * FROM tb_emp8;
Empty set (0.07 sec)
mysql> DESC tb_emp8;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(22) | YES  | UNI | NULL    |       |
| deptId | int(11)     | NO   | MUL | NULL    |       |
| salary | float       | YES  |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.05 sec)

【实例 1】

创建一个名为 SumOfSalary 的触发器，触发的条件是向数据表 tb_emp8 中插入数据之前，对新插入的 salary 字段值进行求和计算。输入的 SQL 语句和执行过程如下所示。

mysql> CREATE TRIGGER SumOfSalary
    -> BEFORE INSERT ON tb_emp8
    -> FOR EACH ROW
    -> SET @sum=@sum+NEW.salary;
Query OK, 0 rows affected (0.35 sec)

触发器 SumOfSalary 创建完成之后，向表 tb_emp8 中插入记录时，定义的 sum 值由 0 变成了 1500，即插入值 1000 和 500 的和，如下所示。

SET @sum=0;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tb_emp8
    -> VALUES(1,'A',1,1000),(2,'B',1,500);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT @sum;
+------+
| @sum |
+------+
| 1500 |
+------+
1 row in set (0.03 sec)

创建 AFTER 类型触发器

在 test_db 数据库中，数据表 tb_emp6 和 tb_emp7 都为员工信息表，包含 id、name、deptId 和 salary 字段，数据表 tb_emp6 和 tb_emp7 的表结构如下所示。

mysql> SELECT * FROM tb_emp6;
Empty set (0.07 sec)
mysql> SELECT * FROM tb_emp7;
Empty set (0.03 sec)
mysql> DESC tb_emp6;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> DESC tb_emp7;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

【实例 2】

创建一个名为 double_salary 的触发器，触发的条件是向数据表 tb_emp6 中插入数据之后，再向数据表 tb_emp7 中插入相同的数据，并且 salary 为 tb_emp6 中新插入的 salary 字段值的 2 倍。输入的 SQL 语句和执行过程如下所示。

mysql> CREATE TRIGGER double_salary
    -> AFTER INSERT ON tb_emp6
    -> FOR EACH ROW
    -> INSERT INTO tb_emp7
    -> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);
Query OK, 0 rows affected (0.25 sec)

触发器 double_salary 创建完成之后，向表 tb_emp6 中插入记录时，同时向表 tb_emp7 中插入相同的记录，并且 salary 字段为 tb_emp6 中 salary 字段值的 2 倍，如下所示。

mysql> INSERT INTO tb_emp6
    -> VALUES (1,'A',1,1000),(2,'B',1,500);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM tb_emp6;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | A    |      1 |   1000 |
|  2 | B    |      1 |    500 |
+----+------+--------+--------+
3 rows in set (0.04 sec)
mysql> SELECT * FROM tb_emp7;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | A    |      1 |   2000 |
|  2 | B    |      1 |   1000 |
+----+------+--------+--------+
2 rows in set (0.06 sec)

判断值后调用触发器

这里简单讲述几个判断插入类型的触发器。

比如触发器调用，当插入时间小时为20时，对数据进行插入：

DROP TRIGGER IF EXISTS `ins_info`;  
create trigger ins_info  
after insert on nhfxelect for each row   
begin  
    if HOUR(new.RecordTime)='20' then    
    insert into nhfxbyhour (UnitDepName, UnitDepCode, ElectCost, TimeJG, RecordTime)  
        values( '数统学院', '1', new.USERKWH, '20', new.RecordTime);  
    end if;  
end;  

这个触发器中，RecordTime为datetime类型，如"2016-08-28 20:10:00"，这时hour()这个值为20才能插入；否则数据不能插入。同时可以date_format(new.RecordTime, ‘%Y-%m-%d’)判断日期为某天或某年某月进行插入。

同时，再如更新触发器，如果设置的值为某个范围，才进行操作或性别为"男"或"女"才进行操作。

基本语法：

if 判断条件 then
    sql语句;
end if;

NEW和OLD

NEW在触发器为INSERT事件类型时有效，表示当前正在插入的数据；同理，OLD在触发器类型为DELETE事件类型时有效，表示当前正在删除的数据。

查找触发器

可以通过执行SHOW TRIGGERS命令查看触发器，但是因为不能查询指定的触发器，所以每次都返回所有的触发器的信息，使用不方便。但是可以使用查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。如：

mysql> USE information_schema;

Database changed

mysql> SELECT  * FROM triggers WHERE trigger_name='trigger_student_count_insert';

修改触发器

可以使用 ALTER FUNCTION 语句来修改自定义函数的某些相关特征。若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。

删除触发器

DROP TRIGGER trigger_name;

触发器的使用限制

<1> 触发器只能创建在永久表上，不能对临时表创建触发器；

<2> 触发器不能使用CALL语句调用具有返回值或使用了动态SQL的存储过程（存储过程可以使用OUT或INOUT参数返回给触发器返回值）。

<3> 触发器中不能使用开启或结束事务的语句段，比如，开始事务（START TRANSACTION）、提交事务（COMMIT）或是回滚事务（ROLLBACK），但是回滚到一个保存点（SAVEPOINT是允许的，因为回滚到保存点不会结束事务）；

<4> 外键不会激活触发器；

<5> 当使用基于行的复制时，从表上的触发器不会因操作主表中的数据而激活。当使用基于语句的复制时，从表上的触发器会被激活。参考 Section 17.4.1.34,“Replication and Triggers”；

<6> 触发器中不允许返回值，因此触发器中不能有返回语句，如果要立即停止一个触发器，应该使用LEAVE语句；

触发器中的异常机制

MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为一个事务被回滚，但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚，这也是设计触发器的时候需要注意的问题。

触发器尽量避免

下面简单参考知乎和优快云论坛，简单讲解几个内容：

问题一：大型系统必须得要存储过程和触发器吗？-知乎

回答一：

我们先要弄清楚二个问题：

1.什么是大型系统？

2.你讨论的是什么领域的应用，可以大致分为二种：互联网、企业内部

接下来给你举一些例子：

1.SAP、peopleSoft、ERP等企业级别应用

一般情况下，会使用存储过程和触发器，减少开发成本，毕竟其业务逻辑修改频繁，而且为通用，很多时候会把一些业务逻辑编写成存储过程，像Oracle会写成包，比存储过程更强大。

另外一个原因是服务器的负载是可控，也即系统的访问人数首先是可控的，没有那么大，而且这些数据又非常关键，为此往往使用的设备也比较好，多用存储柜子支撑数据库。

2.另外一类互联网行业的

比如淘宝、知呼、微博等，数据库的压力是非常大的，也往往会最容易成为瓶颈，而且多用PC服务器支撑，用户量的增速是不可控的，同时在线访问的用户量也是不可控的，为此肯定会把业务逻辑放到其他语言的代码层，而且可以借助一些LVS等类型软硬件做负载均衡，以及平滑增减Web层的服务器，从而达到线性的增减而支持大规模的访问。

所以不管你的这个系统是否庞大，首先要分业务支持的对象，系统最可能容易出现瓶颈的地方在那？

当然也不是说互联网行业的应用就绝对不用存储过程，这个也不对，曾在阿里做的Oracle迁移MySQL系统确实用了，因为历史的原因，另外还有一些新系统也有用，比如晚上进行定期的数据统计的一些操作，不过有量上的控制。存储过程是好东西，要分场景，分业务类型来用就可以把握好。

回答2

肯定不能一刀切的说能用或者不能用，不同类型的系统、不同的规模、不同的历史原因都会有不同的解决方案。

一般情况下，Web应用的瓶颈常在DB上，所以会尽可能的减少DB做的事情，把耗时的服务做成Scale Out，这种情况下，肯定不会使用存储过程；而如果只是一般的应用，DB没有性能上的问题，在适当的场景下，也可以使用存储过程。

至于触发器，我是知道有这东西但从来没用过。我希望风险可控，遇到问题能够快速的找到原因，尽可能不会去使用触发器。

回答3

1. PLSQL可以大大降低parse/exec 百分比；
2. 存储过程可以自动完成静态SQL variable bind；
3. 存储过程大大减少了JDBC网络传输与交互，速度快；
4. oracle 中存储过程内部commit为异步写，一定程度上减少了等redo日志落地时间；
5. 存储过程最大问题就是给数据库开发工作压力太大，另外架构升级时候会比较难解耦；
6. 触发器不推荐使用，触发操作能在业务层解决就在业务层解决，否则很难维护，而且容易产生死锁。

问题2：为什么大家都不推荐使用MySQL触发器而用存储过程？- segmentfault

回答1

1. 存储过程和触发器二者是有很大的联系的，我的一般理解就是触发器是一个隐藏的存储过程，因为它不需要参数，不需要显示调用，往往在你不知情的情况下已经做了很多操作。从这个角度来说，由于是隐藏的，无形中增加了系统的复杂性，非DBA人员理解起来数据库就会有困难，因为它不执行根本感觉不到它的存在。
2. 再有，涉及到复杂的逻辑的时候，触发器的嵌套是避免不了的，如果再涉及几个存储过程，再加上事务等等，很容易出现死锁现象，再调试的时候也会经常性的从一个触发器转到另外一个，级联关系的不断追溯，很容易使人头大。其实，从性能上，触发器并没有提升多少性能，只是从代码上来说，可能在coding的时候很容易实现业务，所以我的观点是：摒弃触发器！触发器的功能基本都可以用存储过程来实现。
3. 在编码中存储过程显示调用很容易阅读代码，触发器隐式调用容易被忽略。
4. 存储过程的致命伤在于移植性，存储过程不能跨库移植，比如事先是在mysql数据库的存储过程，考虑性能要移植到oracle上面那么所有的存储过程都需要被重写一遍。

回答2

这种东西只有在并发不高的项目，管理系统中用。如果是面向用户的高并发应用，都不要使用。触发器和存储过程本身难以开发和维护，不能高效移植。触发器完全可以用事务替代。存储过程可以用后端脚本替代。

回答3

我觉得来自两方面的因素：

1. 存储过程需要显式调用，意思是阅读源码的时候你能知道存储过程的存在，而触发器必须在数据库端才能看到，容易被忽略。
2. Mysql的触发器本身不是很好，比如after delete无法链式反应的问题。

我认为性能上其实还是触发器占优势的，但是基于以上原因不受青睐。

【参考文章】

http://c.biancheng.net/view/2590.html

https://blog.youkuaiyun.com/qq_36396104/article/details/80469997