mysql之触发器

定义：触发器是一类特殊的事务，可以监视某种数据操作（insert/update/delete）并触发相关操作（insert/update/delete）,也就是说在表中的某些特定数据变化时自动完成某些操作。

触发器组成的4要素：监视地点（table）   监视事件（insert/update/delete）     触发时间（after/before）      触发事件（insert/update/delete）

触发器格式：

create trigger  触发器名称

after/before(触发时间)

insert/update/delete(监视事件)

on  表名（监视地址）

for each row

begin

sql1;

.....

sqlN;

end;

例子：比如现在有  商品表--goods     字段：gid,name,num

  订单表-ord        字段：oid,gid,nuch

      当生成一个新的订单时商品表就要减少相应的商品数量

   在这些条件之上创建一个触发器：

监视谁 ord   监视动作 insert     触发时间 after      触发事件 update

create trigger t1

after

insert

on ord

for each row

begin

update goods set num=num-2 where gid=1;

end;

t1这个触发器的运行结果是，无论生成哪种商品的新订单，gid=1的商品都会减2，不能满足相应商品减少的需求

引入行变量：

注意：在同一张数据表同一操作时不能同时创建两个触发器

show triggers  查看触发器命令

drop trigger triggerName  删除某一触发器命令

当执行insert操作时ord表产生一个新的记录，用new代替 ord 新行，相反执行delete操作时，用old代替 ord 改变之前的旧行

1、当ord表里增加一条记录，goods表减少商品数量

create trigger t2

after

insert

on ord

for each row

begin

update goods setnum=num-new.much where gid=old.gid;

end;

2、当ord表里减少一条记录，goods表增加相应商品数量

create trigger t3

after

delete

on ord

for each row

begin

update goods set num=num+old.much where gid=old.gid;

end;

3、当修改ord表里商品数量，goods表相应商品数量也要改变

create trigger t4

before

update

on ord

for each row

begin

update goods set num=num+old.much-new.much where gid=old.gid;

end;

上面的触发器，当购买量 much> 库存量num 时，就会发生商品数量为负数的情况也就是爆仓了，所以需要在update之前加上一个判断购买量与库存量的关系

如下：

create trigger t5

before

insert

on ord

for each row

begin

declare ---在触发器中声明变量

rnum intselect num into rnum from goods where gid=new.gid

if new.much>rnum then

new.much=rnum

end if;

update goods set num=num-new.much where gid=old.gid;

end$ --因为默认；为结束标记，但是sql语句要使用；所以改变结束符号为$

before 与after的区别：before可以防止爆仓，所以在begin与end之间添加判断购买量与库存量的关系,如果把before变为after的话会报1362错误：updating of newrow is not allowed in after trigger.原因是：insert之后，new行已经插入到表中。成为事实，改为new已经晚了

for each row::声明行级触发器

在oracle中，for each row如果不写的话默认为语句级触发器 无论update语句影响了多少行，下面的动作都只会执行一次，

例如：1个人下了一个订单，买了5件商品，insert执行了5次，用行级触发器的话就会修改5次库存，用语句级触发器的话就只会insert一条发货提醒

切记：mysql目前不支持语句级触发器