mysql 触发器

在ＭySQL中，触发器可以在你执行INSERT、UPDATE或DELETE的时候，执行一些特定的操作。在创建触发器时，可以指定是在执行SQL语句之前或是之后执行这些操作。通过触发器，你可以实现一些业务逻辑或一些数据限制，在简化应用程序逻辑，优化系统性能时非常有用。但在使用MySQL触发器也有一些注意事项，否则会导致触发器不能工作或不按指定的方式工作。

1. MySQL触发器注意事项

MySQL触发器使用非常简单，当然其功能也比较有限。当你在项目中大量使用触发器，那么你应该注意以下几点，以避免触发器不按预期效果执行：

MySQL触发器能基于行触发，MySQL触发器始终时基于表中的一条记录触发，而不是一组SQL语句。因此，如果需要变动整个数据集而数据集数据量又较大时，触发器效果会非常低。
每一个表的一个事件只能定义一个触发器，例如：不能在AFTER INSERT上定义一个以上的触发器。

2. MySQL触发器可能导致的问题

由于MySQL触发器基于行触发的特性，因此对于批量操作并不适合使用触发器，如：汇总表、缓存表等。触发器使用不当，可能会导致以下问题：

一个MySQL触发器可能会关联到另外一张表或几张表的操作。因此，会导致数据库服务器负荷也会相应的增加一倍或几倍，如果出现因为触发器问题导致的性能问题，会很难定位问题位置和原因。
在基于锁的操作中，触发器可能会导致锁等待或死锁。触发器执行失败，原来执行的SQL语名也会执行失败。而因为触发器导致的失败结果和失败原因，往往很难排查。

3. MySQL触发器的好处

对MySQL触发器有足够的认识和了解后，MySQL触发器会给我们带来极大的便利。当实现一些系统约束时，或在实现系统维护及针对操作数据的更新时，使用触发器都非常方便。在之胶我们介绍了MySQL触发器不适合做的一些工作，但MySQL触发器在以下一些应用场景中，会非常实用：

基于行数据变更的日志记录。如：在用户订单系统中，我们可以基于用户订单数据状态的改变，使用触发器构建用户订单日志表数据。
基于行数据变更的关系数据的更新。如：用户订单改变至付款或相关状态时，我们可以基于用户订单数据状态的改变，使用触发器改变用户会付款或相应状态信息。
基于行数据变更的数据汇总。如：用户订单成交或失败，我们可以基于用户订单数据状态的改变，使用触发器构建用户总成交量或失败量汇总数据。

以上仅列举了一些常用场景，合理的利用MySQL触发器会在数据库工作效率和开发效率上有很大的提高。

由于MySQL触发器的种种问题，要求我们在创建触发器就应该充分考虑。避免使用不合适的触发器，并能对所有触发器有足够的了解，以便问题的定位和排查。

特性

有begin end体，begin end;之间的语句可以写的简单或者复杂
什么条件会触发：I、D、U
什么时候触发：在增删改前或者后
触发频率：针对每一行执行
触发器定义在表上，附着在表上

注意

尽量少使用触发器，不建议使用。
假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高，资源消耗要小。
触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。
语法

1、创建只有一个执行语句的触发器

1	CREATE TRIGGER 触发器名 BEFORE\|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;

2、创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
        执行语句列表
END;

例

CREATE TRIGGER trig2 BEFORE DELETE
  ON work FOR EACH ROW
     BEGIN
     　	INSERT INTO time VALUES(NOW());
   　　	INSERT INTO time VALUES(NOW());
	END

3、NEW与OLD详解

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

另外，OLD是只读的，而NEW则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

4、查看触发器

SHOW TRIGGERS;
显示所有触发器的基本信息；无法查询指定的触发器。
SELECT * FROM information_schema.triggers;
显示所有触发器的详细信息；同时，该方法可以查询制定触发器的详细信息。

如果加where，可以写成

1 2	select * from information_schema.triggers where trigger_name='tri_insert_order';

所有触发器信息都存储在information_schema数据库下的triggers表中，可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询。

5、删除触发器

1	DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

删除触发器之后最好使用上面的方法查看一遍；同时，也可以使用database.trig来指定某个数据库中的触发器。

示例

在做支付宝订单支付的时，支付宝支付接口在一个单独的项目中，在后台调取接口生成订单去支付，当完成支付时，因为回调方法在另一个项目中，没法修改，只能用触发器来实现对表数据的监控，来完成剩下的业务逻辑。

代码实现

BEGIN
  DECLARE done INT DEFAULT FALSE ; /*遍历数据结束标志 */
  declare lenght_time int(5); /*时长*/
  declare  column_id varchar(32);  /*栏目id*/
  /*定义游标 以及赋值*/
  declare column_ids cursor for 
          select sod.service_id,sod.duration  from sys_order_detail sod  where order_id in
        (SELECT t.order_id
        FROM sys_order_info t
        WHERE order_number = NEW.out_trade_no);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;/* 将结束标志绑定到游标  */
  
   IF (NEW.trade_status = 'TRADE_SUCCESS') THEN
   
   UPDATE sys_order_info 

   set order_status = '2'
   
   where order_number = NEW.out_trade_no;
		/*打开游标*/
		open  column_ids ;
			/*循环开始*/
			read_loop:loop
				/*提前游标的值  */
				fetch  column_ids into column_id ,lenght_time; 
				/*声明结束的时候*/
				if done 
				then leave read_loop ; end if ;
				/*根据时长和栏目id修改时长*/				
				UPDATE xypt_column SET end_time = DATE_ADD(end_time, INTERVAL lenght_time MONTH) where id =column_id	;			
			end loop ;  /*循环结束*/
		close column_ids ;/*关闭游标*/
	ELSE
     
    UPDATE sys_order_info 

   set order_status = '3'
   
   where order_number = NEW.out_trade_no;

 	 END IF;
END

参考地址

点击查看触发器概念 https://itbilu.com/database/mysql/4y77zMXel.html
点击查看触发器应用 https://www.cnblogs.com/geaozhang/p/6819648.html
点击查看官网示例 https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html