MySQL触发器详解-优快云博客

本文链接：https://blog.youkuaiyun.com/zhang_referee/article/details/79107441

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触发程序。

3.创建触发器

#商品库存表
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;

查看数据库表情况：

查看触发器：

show triggers

测试触发器是否可用：

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

可以看到的是，当goods_order执行insert操作时，触发了名为goodsnumber的触发器，从而使得商品村库表(goods_number)的相应的数据发生改变。

删除触发器：

DROP TRIGGER TRIGGERNAME;

4. 触发器实例

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;

4.2 修改订单修改库存的触发器

#修改订单修改库存的触发器
#
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;

4.3 综合实例

当用户订单购买数量大于5时，强制更改为5。

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;