数据库管理——触发器

触发器

触发器的概念精讲

举个例子：

• 当我们对一个表进行数据操作时，需要同步对其它的表执行相应的操作，正常情况下，如果我们使用sql语句进行更新，将需要执行多条操作语句！
• 比如，在某些棋牌游戏中，当玩家充值金币后，玩家表数据库中金币增加的同时，玩家所属的代理会得到相应的提成并计入代理的收益中，即代理数据库表提成记录字段也要同步更新
• 这个场景，可以轻松使用触发器来实现！

• 触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。

创建触发器

• 通过 CREATE TRIGGER 来实现

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

  • ​ 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，创建触发器实现向学员表中插入记录时，就会在插入之后更新班级表中的人数，当我们删除某条学员的记录时，就会在删除后更新班级表中的人数，具体步骤如下：

  use school;
  CREATE TABLE class(
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(128)DEFAULT NULL,
  `teacher` varchar(64) DEFAULT NULL,
  `count` int DEFAULT 0,
  UNIQUE KEY `id`(`id`)
  );#创建班级表class;
  insert into class values(101,'萌新一班','Martin',0),(102,'萌新二班','Rock',0),(103,'萌新三班','Janny',0);#创建成绩表 grade
  CREATE TABLE `student`(
  `id` int NOT NULL AUTO_INCREMENT UNIQUE,
  `name` varchar(64) DEFAULT NULL,
  `class_id` int DEFAULT NULL,
  `sex` enum('F','M') DEFAULT NULL
  );
  create trigger tri_insert_student after insert on student for each row update class set count=count+1 where class.id = NEW.class_id;#创建触发器，新增学员之后班级人数增加1
  insert into student values(1,'小花',101,'M'),(2,'小红',102,'F'),(3,'小军',102,'F'),(4,'小白',101,'F');
  select count from class;
  create trigger tri_delete_student after delete on student for each row update class set count=count-1 where id = OLD.class_id; #创建触发器，删除学员班级人数减1
  

• 触发器包含多条执行语句

  CREATE trigger trigger_name DEFORE|AFTER trigger_EVENT
  ON TABLE_NAME FOR EACH ROW
  	BEGIN
  		trigger_STMT
  	END
  

• 在MySQL中，一般情况下用 “;” 符号作为语句的结束符号，可是在创建触发器时，需要用到 “;” 符号作为执行语句的结束符号。为了解决该问题，可以使用关键字DELEMITER语句

  “DELIMITER$$”可以将结束设置成 $$"

  use school;
  create table grade(id int UNIQUE AUTO_INCREMENT,math tinyint unsigned, chinese tinyint unsigned,english tinyint unsigned);
  insert into grade values(1,80,87,91),(2,72,64,89),(3,54,69,89),(4,78,79,89);#插入
  DELIMITER $$
  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 id = OLD.class_id;
  END;
  	$$
  	DELIMITER;
  

查看触发器

• show triggers 语句查看触发器

  • 参数trigger表示触发器的名称；
  • 参数Event表示触发器的激发事件；
  • 参数Table表示触发器对象触发事件所操作的表；
  • 参数Statement表示触发器激活时所执行的语句
  • 参数Timing表示触发器所执行的时间

• 查看系统表triggers实现查看触发器

  • MySQL中，在系统数据库information_schema中存在一个存储所有触发器信息的系统表，因此查询该表格的记录也可以实现查看触发器功能

    use information_schema;
    #选择数据库information_schema;
    select * from triggers;
    select * from triggers where trigger_name='tri_delete_student';
    #查询系统表triggers 中的触发器
    

触发器的删除

DROP TRIGGER trigger_name;