定义:触发器是一类特殊的事务,可以监视某种数据操作(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目前不支持语句级触发器