1.触发器:trigger,是指事先为某张表绑定一段代码,当表中的某些内容发生改变(增、删、改)的时候,系统会自动触发代码并执行。
2.触发器四要素
监视地点:orders(订单)表
监视事件:增/删/改
触发时间:after/before
触发事件:增/删/改
3.建立2张表,orders表和goods表,内容如下:
订单表初始为空;
4.1.添加订单,库存减少
create trigger tg1
after insert on orders
for each row
begin
update goods set num = num-new.much where id = new.gid;
end$
测试触发器tg1:insert into orders(gid,much)values(2,5);
4.2.删除订单,库存增加
create trigger tg2
after delete on orders
for each row
begin
update goods set num = num+old.much where id = old.gid;
end$
4.3.修改订单数量,库存量随之改变
create trigger tg3
after update on orders
for each row
begin
update goods set num = num+old.much-new.much where id = new.gid;
5.如上创建了3个触发器,分别监视orders表的增/删/改的行为