1,什么是触发器?
百度给出的定义:触发器(trigger)是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。顾名思义:触发器就是由某个事件引发的。
2.怎么学习触发器?
记住触发器的四个要素:监视事件(增、删、改)、监视地点(某张表)、触发事件(增、删、改)、触发时间(before、after)。
触发器的语法:create trigger tgadd after/before insert/delete/update on tablename for each row begin sql; end# (使用之前修改语句结束定界符delimiter #)
实例:商城下订单和库存量
create table goods(
goods_id int(2),
goods_num int(10)
)charset=utf8;
insert into goods values(1,10);
insert into goods values(2,20);
insert into goods values(3,30);
insert into goods values(4,40);
insert into goods values(5,50);
create table orders (
goods_id int(2),
orders_id int(2),
orders_num int(10)
)charset=utf8;
create trigger guardnum after insert on orders for each row begin update goods set goods_num=goods_num-new.orders_num where goods_id=new.goods_id; end#
create trigger guardnum after update on orders for each row begin update goods set goods_num=goods_num+old.orders_num -new.orders_num wheregoods_id=new.goods_id; end#
create trigger guardnum before insert on orders for each row begin if new.orders_num >(select num from goods where goods_id=new.goods_id) then set new.orders_num =(select num from goods where goods_id=new.goods_id) ; end if; update goods set goods_num=goods_num-new.orders_num where goods_id=new.goods_id; end#