mysql 触发器

1. 触发器   

      今天要讨论的东西是触发器，这个东西怎么说呢，工作中用的比较少（至少我个人是这样认为的），但在招聘面试中却常被问答！为什么在生产环境中用的比较少呢？因为使用触发器会对mysql性能有影响，再者就是业务逻辑写到数据库，虽然写的代码少了，但是不好调试，也不好控制，维护和升级很蛋疼！好了言归正传，下面详细讨论下触发器！

   先看下官方对触发器的解释：MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

    个人理解：当被监视的表触发某项操作时（值insert/update/delete），所引发的一系列动作。在MySQL Server里面也就是对某一个表的一定的操作，触发某种条件（Insert,Update,Delete 等），从而自动执行的一段程序。从这种意义上讲触发器是一个特殊的存储过程。举个栗子：商品订单表和商品库存表。当用户下订单时，库存相应的应该减少亦或者商品订单表数据发生改变，商品库存表数据应该随之改变才对，也就是说商品订单表数据改变出发了商品库存的改变。

 2. 触发器语法

     触发器创建语法四要素：1.监视地点（tableName） 2.监视事件（insert/update/delete） 3.触发时间（after/before） 4.触发事件（insert/update/delete）

      CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

可归纳为：

  CREATE TRIGGER TRIGGERNAME

   AFTER /BEFORE

  INSERT/UPDATE/DELETE  ON TABLENAME

  FOR EACH ROW

  BEGIN

       SQL;

  END

----------------------------------------------------------------------------------------------------------------------------------

 注：各自颜色对应上面的四要素，为了能够定义执行多条语句的触发程序使用BEGIN ... END复合语句结构。通过使用BEGIN ... END结构，能够定义执行多条语句的触发程序。在BEGIN块中，还能使用存储子程序中允许的其他语法，如条件和循环等。但是，正如存储子程序那样，定义执行多条语句的触发程序时，如果使用mysql程序来输入触发程序，需要重新定义语句分隔符，以便能够在触发程序定义中使用字符“;”。

     对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。

#商品库存表
create table goods_number(
gid smallint auto_increment,
numbers smallint unsigned not null ,
primary key(gid)
)engine = myisam default charset = utf8;

insert into goods_number(gid,numbers) values(1,123),(2,112),(3,223),(4,234),(5,100);

    

#商品订单表
create table goods_order(
orderid smallint auto_increment,
order_number varchar(32) not null ,
goods_id smallint unsigned not null,
gn smallint unsigned not null,
primary key(orderid)
)engine = myisam default charset = utf8;

  如何在触发器中引用新行的值

       对于insert 而言，新增的行用 new 表示，行中的每一列的值，用new.列名来表示

       对于delete来言，原本有一行后来被删除，想引用被删除的这一行，用old.来删除

       对于update来说，被修改的行，修改前的数据，用old来表示，old.列名引用被修改之前行中列的值。修改后的数据，用new来表示，new.列名引用被修改之后行中列的值。

    

#下订单修改库存的触发器
#
create trigger goodsnumber
after insert on  goods_order 
for each row
Begin
update goods_number set numbers = numbers - new.gn where gid = new.orderid;
End;

     查看数据库表情况：   

测试触发器是否可用：

insert into goods_order(orderid,order_number,goods_id,gn) values(1,'whdbxb123456789',1,12),(2,'whdbxb234567890',2,12);

     4.1 当商品订单删除时，商品库存表数据修改时的触发器

#删除订单修改库存的触发器
#
create trigger goodsnumberinc
after delete on goods_order
for each row
Begin
update goods_number set numbers = numbers + old.gn where gid = old.orderid;
end;

 

#修改订单修改库存的触发器
#
create trigger goodsnumberupdate
after update on goods_order
for each row 
Begin 
update goods_number set numbers = numbers + old.gn - new.gn where gid = old.orderid;
End;

create trigger goodsnumber_distinguish_with_after_before
before insert on goods_order 
for each row
begin
  if new.gn > 5 then
    set new.gn = 5;
  end if;
  update goods_number set numbers = numbers - new.gn where gid = new.orderid;
end;

5.after 和 before 的区别

    After 是完成数据的增删改 ，再触发，触发的语句晚于监视的增删改，无法影响前面的增删改动作

    Before 是先完成后触发，再增删改，触发的语句先于监视的增删改发生，我们有机会判断，修改即将发生的操作

详细参考本文章综合案例。