IT虾米网

MySQL(十一)之触发器详解

admin 2018年06月04日 数据库 361 0

上一篇介绍的是比较简单的视图,其实用起来是相对比较简单的,以后有什么更多的关于视图的用法,到时候在自己补充。接下来让我们一起了解一下触发器的使用!

一、触发器概述

 1.1、什么是触发器

  触发器(Trigger):监视某种情况,并触发某种操作。在MySQL Server里面也就是对某一个表的一定的操作,触发某种条件(Insert,Update,Delete 等),从而自动执行的一段程序

  注意:你必须拥有相当大的权限才能创建触发器(CREATE TRIGGER),如果你已经是Root用户,那么就足够了。这跟SQL的标准有所不同。

 1.2、触发器作用

  那么为什么要使用数据库对象触发器呢?在具体开发项目时,经常会遇到如下实例:
    1)在学生表中拥有字段学生姓名,字段学生总数,每当添加一条学生信息时,学生的总数就必须同时更改。
    2)在学生表中还会有学生姓名的缩写,学生住址等字段,添加学生信息时,往往需要检查电话、邮箱等格式是否正确。
  上面的例子使用触发器完成时具有这样的特点,需要在表发生改变时,自动进行一些处理。MySQL在触发DELETE/UPDATE/INSERT语句时就会自动执行所设置的操作,其他SQL语句则不会激活触发器

 1.3、触发器四要素

  监视地点:table
  监听事件:insert/update/delete
  触发时间:after/before
  触发事件:insert/update/delete

二、触发器用法

 2.1、触发器语法

  CREATE TRIGGER <触发器名称>  --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
  { BEFORE | AFTER }  --触发器有执行的时间设置:可以设置为事件发生前或后。
  { INSERT | UPDATE | DELETE }  --同样也能设定触发的事件:它们可以在执行insert、update或delete的过程中触发。
  ON <表名称>  --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
  FOR EACH ROW  --触发器的执行间隔:FOR EACH ROW子句通知触发器 每隔一行执行一次动作,而不是对整个表执行一次。
  <触发器SQL语句>  --触发器包含所要触发的SQL语句:这里的语句可以是任何合法的语句, 包括复合语句,但是这里的语句受的限制和函数的一样。

   简单的写就是这样:

  create trigger tg_name after/before insert/update/delete on table 
  for each row ####这句话在MySQL中是固定的 
  begin 
  sql语句; 
  end$

 

  分析:由于在以上代码段中的“sql语句;”是以分号结尾,所以需要将MySQL中的结尾标志换成“$”,更换MySQL命令结束标志的命令:delimiter $;
       以上触发器语法中的各个段的颜色与四要素对应查看理解。

 2.2、创建触发器

  1)创建两张表

  create table tb_goods(id int primary key auto_increment,name varchar(20),num int); 
  create table tb_orders(id int primary key auto_increment,good_id int,much int); 

 

  2)在商品表中插入数据

  insert into tb_goods(name,num)values('商品1',10),('商品2',10),('商品3',10);

 

  3)假如我们卖了3个商品1

  没有触发器:

    往订单表中插入一条记录:insert into tb_orders(good_id,much) values(1,3);
    更新商品表中商品1的剩余数量:update tb_goods set num=num-3 where id=1;    

  创建触发器:   

  create trigger tg_1 after insert on tb_orders
  for each row
  begin
  update tb_goods set num=num-3;
  end$

 

  这个时候如果执行insert into tb_orders(good_id,much) values(1,3);会发现商品的数量变为7了,说明在插入一条订单的时候,触发器自动做了更新操作。 

 2.3、触发器对值得引用

  上述触发器有一个问题,因为在触发器中写死了num和id,所以不管买哪个商品,最终更新的都是商品1的数量。这个时候,需要将触发器中的值变为动态获取。  

  对于insert来说,新插入的行用new来表示,行中的每一列的值用“new.列名”来表示:  

  新建能动态获取值的触发器: 

  create trigger tg_2 after insert on tb_orders 
  for each row 
  begin 
  update tb_goods set num=num-new.much where id=new.id; 
  end$

 

  删除第一个触发器:drop trigger tg_1;
  插入一条订单记录:insert into tb_orders(good_id,much) values(2,3)$
  执行完发现商品的数量变为7了,这样子就是合适的。

三、触发器实例

  1)创建表tab1 

  DROP TABLE IF EXISTS tab1; 
  CREATE TABLE tab1( 
      tab1_id varchar(11) 
  );

  2)创建表tab2

  DROP TABLE IF EXISTS tab2; 
  CREATE TABLE tab2( 
      tab2_id varchar(11) 
  );

  3)创建触发器:t_afterinsert_on_tab1   

DROP TRIGGER IF EXISTS t_afterinsert_on_tab1; 
CREATE TRIGGER t_afterinsert_on_tab1  
AFTER INSERT ON tab1 
FOR EACH ROW 
BEGIN 
     insert into tab2(tab2_id) values(new.tab1_id); 
END;

  4)想tab1插入数据 

INSERT INTO tab1(tab1_id) values('0001');

  5)查看变化 

SELECT * FROM tab1; 
SELECT * FROM tab2;

 

发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

MySQL(十二)优化原理详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。