mysql触发器

什么是触发器？

触发器与函数、存储过程一样，触发器是一种对象，它能根据对表的操作时间，触发一些动作，这些动作可以是insert,update,delete等修改操作。

触发器的概念

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作(insert，delete， update)时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)

触发器语法

Delimiter &&
CREATE TRIGGER 	//创建触发器
trigger_name  	//触发器的名称
trigger_time 	//触发器的时间 参数为BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后。
trigger_event 	//是触发器的触发事件，包括insert、update和delete
ON tbl_name        //表示在这个表上 
FOR EACH ROW 	//表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。
[trigger_order]    //是MySQL5.7之后的一个功能，用于定义多个触发器，使用follows(尾随)或precedes(在…之先)来选择触发器执行的先后顺序。 
begin
sql语句
end && 		// 从begin到end就是触发器的主体,触发了什么。
Delimiter ;

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

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

定义一个名为trig1的触发器，一旦在work表中有插入动作，就会自动往time表里插入当前时间

DELIMITER &&
 CREATE TRIGGER trig1 AFTER INSERT
  ON work FOR EACH ROW
 INSERT INTO time VALUES(NOW());
&&
DELIMITER ;

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

DELIMITER &&
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件

ON 表名 FOR EACH ROW

BEGIN
        执行语句列表
END &&
DELIMITER ;

定义一个触发器，一旦有满足条件的删除操作，就会执行BEGIN和END中的语句

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

NEW与OLD详解

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

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

使用方法：
NEW.columnName （columnName为相应数据表某一列名）
另外，OLD是只读的，而NEW则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

delimiter $$
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
 　　IF NEW.amount < 0 THEN
 　　　　SET NEW.amount = 0;
 　　ELSEIF NEW.amount > 100 THEN
 　　　　SET NEW.amount = 100;
 　　END IF;
 END$$
delimiter ;

上面触发器的意思是当新修改的数<0 ，则把该数值改为0 如果修改成为>100的数则把数组改为100

查看触发器

1、SHOW TRIGGERS语句查看触发器信息
结果，显示所有触发器的基本信息；无法查询指定的触发器。
2、在information_schema.triggers表中查看触发器信息
SELECT * FROM information_schema.triggers
结果，显示所有触发器的详细信息；同时，该方法可以查询制定触发器的详细信息。
所有触发器信息都存储在information_schema数据库下的triggers表中，可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询。

删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

如果不需要某个触发器时一定要将这个触发器删除，以免造成意外操作，这很关键。

触发器尽量避免使用

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

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