MySQL - 触发器

触发器概念

• 在实际开发中往往会碰到这样的情况：
  当我们对一个表进行数据操作时，需要同步对其它的表执行相应的操作，正常情况下，如果我们使用sql语句进行更新，将需要执行多条操作语句！
• 触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL从5.0.2版本开始支持触发器。

1. 创建触发器
   在MySQL中创建触发器通过SQL语句CREATE TRIGGER来实现，其语法形式如下：

   CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT     
      ON TABLE_NAME FOR EACH ROW trigger_STMT          
   1
   2

   在上述语句中，参数trigger_name表示要创建的触发器名；
   参数BEFORE和AFTER指定了触发器执行的时间，前者在触发器事件之前执行触发器语句，后者在触发器事件之后执行触发器语句；
   参数trigger_EVENT表示触发事件，即触发器执行条件，包含DELETE、INSERT和UPDATE语句；参数TABLE_NAME表示触发事件的操作表名；参数FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器；
   参数trigger_STMT表示激活触发器后被执行的语句。执行语句中如果要引用更新记录中的字段，对于INSERT语句,只有NEW是合法的，表示当前已插入的记录；对于DELETE语句，只有OLD才合法，表示当前删除的记录；而UPDATE语句可以和NEW（更新后）以及OLD（更新前）同时使用。
   注意：不能创建具有相同名字的触发器。另外，对于具有相同触发程序动作时间和事件的给定表，不能有两个触发器。因此，对于有经验的用户，在创建触发器之前，需要查看MySQL中是否已经存在该标识符的触发器和触发器的相关事件。

   执行SQL语句CREATE TRIGGER，在数据库school中存在两个表对象：学员表student和班级表 class，创建触发器实现向学员表中插入记录时，就会在插入之后更新班级表中的人数，当我们删除某条学员的记录时，就会在删除后更新班级表中的人数，具体步骤如下：

   mysql> CREATE TABLE class(id int UNIQUE, name varchar(128), teacher varchar(64), count int DEFAULT 0);  			 #创建班级表 class
   mysql> insert into class values(101, '萌新一班', '张三', 0),(102, '萌新二班', '李四', 0),(103, '萌新三班', '王五', 0);  #班级表中插入数据
   mysql> CREATE TABLE student(id int, name varchar(64), class_id int, sex enum('男','女')); 				 #创建学生表 student
   //创建insert触发器, 当学生表插入一条记录时, 对应的班级count+1
   mysql> create trigger tri_insert_student AFTER insert ON student FOR EACH ROW update class set count=count+1 where class.id=NEW.class_id;
   mysql> insert into student values(1,'小花',101,'女'),(2,'小红',102, '女'),(3,'小军',102,'男'),(4,'小白',101,'男');  #插入多条记录 
   mysql> select *from class;		#插入后查询班级人数
   +------+------------+---------+-------+
   | id   | class_name | teacher | count |
   +------+------------+---------+-------+
   |  101 | 萌新一班   | 张三    |     2 |
   |  102 | 萌新二班   | 李四    |     2 |
   |  103 | 萌新三班   | 王五    |     0 |
   +------+------------+---------+-------+
   //创建delete触发器, 当学生表删除一条记录时, 对应的班级count-1
   mysql> create trigger tri_delete_student AFTER delete ON student FOR EACH ROW update class set count=count-1 where class.id=OLD.class_id;
   mysql> delete from student where id=1;	#删除id为1的学生
   mysql> select * from class;		#删除后查询班级人数
   +------+------------+---------+-------+
   | id   | class_name | teacher | count |
   +------+------------+---------+-------+
   |  101 | 萌新一班   | 张三    |     1  |
   |  102 | 萌新二班   | 李四    |     2  |
   |  103 | 萌新三班   | 王五    |     0  |
   +------+------------+---------+-------+
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25

2. 触发器包含多条执行语句

   CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT     
    	 ON TABLE_NAME FOR EACH ROW                                 
   	     BEGIN        //开始                                                
   	      trigger_STMT//语句                                                 
   	     END          //结束                     
   1
   2
   3
   4
   5

   在上述语句中，比“只有一条执行语句的触发器”语法多出来两个关键字BEGIN和END，在这两个关键字之间是所要执行的多个执行语句的内容，执行语句之间用分号隔开。
   在MySQL中，一般情况下用“;”符号作为语句的结束符号，可是在创建触发器时，需要用到“;”符号作为执行语句的结束符号。为了解决该问题，可以使用关键字DELIMITER语句。例如，“DELIMITER $$”可以将结束符号设置成“ $ $ ”。

   mysql> create table grade(id int, math tinyint, chinese tinyint, english tinyint); 				 #创建成绩表grade
   mysql> insert into grade values(1, 80, 87, 91),(2, 72, 64, 89),(3, 54, 69, 87),(4, 78, 79, 89);  #插入多条记录
   mysql> MELIMITER $$ 	//使用$$作为结束符';'
   mysql> create trigger tri_delete_student after delete on student for each row 
          BEGIN	
          	  DELETE FROM grade where id=OLD.id;
          	  update class set count=count-1 where class.id=OLD.class_id;
          END;   
     	   $$ //结束
     	   
   mysql> MELIMITER ;	//修改 ';' 作为结束符
   mysql> delete from student where id=1;	#删除学生表中id为1的学生
   mysql> select * from student;			#查看学生表的信息
   +------+------+----------+------+
   | id   | name | class_id | sex  |
   +------+------+----------+------+
   |    2 | 小红 |      102 | 女    |
   |    3 | 小军 |      102 | 男    |
   |    4 | 小白 |      101 | 男    |
   +------+------+----------+------+
   mysql> select * from grade;			#查看成绩表的信息
   +------+------+---------+---------+
   | id   | math | chinese | english |
   +------+------+---------+---------+
   |    2 |   72 |      64 |      89 |
   |    3 |   54 |      69 |      87 |
   |    4 |   78 |      79 |      89 |
   +------+------+---------+---------+
   select * from class;				#查看班级表的信息
   +------+------------+---------+-------+
   | id   | class_name | teacher | count |
   +------+------------+---------+-------+
   |  101 | 萌新一班   | 张三    |     1  |
   |  102 | 萌新二班   | 李四    |     2  |
   |  103 | 萌新三班   | 王五    |     0  |
   +------+------------+---------+-------+
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   36

3. 查看触发器
   (1) SHOW TRIGGERS语句查看触发器
   那么如何查看MySQL软件中已经存在的触发器呢？在MySQL软件中查看已经存在的触发器，通过SQL语句SHOW TRIGGERS来实现，其语法形式如下，执行上面的SQL语句，执行结果如图9-10所示。

   SHOW TRIGGERS ;  
   1

   执行完“SHOW TRIGGERS”语句后会显示一个列表，在该列表中会显示出所有触发器的信息。其中，参数Trigger表示触发器的名称；参数Event表示触发器的激发事件；参数Table表示触发器对象触发事件所操作的表；参数Statement表示触发器激活时所执行的语句；参数Timing表示触发器所执行的时间。
   
   (2) 查看系统表triggers实现查看触发器
   在MySQL中，在系统数据库information_schema中存在一个存储所有触发器信息的系统表triggers，因此查询该表格的记录也可以实现查看触发器功能。系统表triggers的表结构

   mysql> use information_schema;   #选择数据库information_schema                  
   mysql> select * from triggers;                                                        
   mysql> select * from triggers where trigger_name=’tri_delete_student’; #查询系统表triggers中的触发器                                                                   
   1
   2
   3

4. 触发器的删除
   在MySQL软件中，可以通过DROP TRIGGER语句或通过工具来删除触发器。
   在MySQL中，删除触发器可以通过SQL语句DROP TRIGGER来实现，其语法形式如下：

   DROP TRIGGER trigger_name; 
   1

   在上述语句中，参数trigger_name表示所要删除的触发器名称。