1.创建两个表
create table goods
(
id int unsigned not null auto_increment,
name varchar(20) not null default '' comment '商品名称',
num int not null default 0 comment '数量',
primary key(id)
)engine=MyISAM default charset=utf8 comment '商品表';
create table orders
(
oid int unsigned not null auto_increment,
gid int not null default 0 comment '商品id',
much int not null default 0,
primary key(oid)
)engine=MyISAM default charset=utf8 comment '订单表';
插入商品的基础数据
insert into goods(name,num) values('goods1',30),('goods2',30),('goods3',30);
2.创建第一个触发器
delimiter $
create trigger tg1
after
insert
on orders
for each row
begin
update goods set num = num - 3 where id = 1;
end$
insert into orders(gid,much) values(1,3)$
每次添加的时候，都只会减少商品id为1的数量(瑕疵)
3.对触发器tg1进行修改，使之完善(监听insert)
先删除tg1 drop trigger tg1$
create trigger tg2
after
insert
on orders
for each row
begin
update goods set num = num - new.much where id = new.gid;
end$
insert into orders(gid,much) values(2,3)$
4.当用户测下订单时，商品的数量再加回去(监听delete)
create trigger tg3
after
delete
on orders
for each row
begin
update goods set num = num + old.much where id = old.gid;
end$
delete from orders where oid = 3$
5.用户修改了订单(监听update)
create trigger tg4
after
update
on orders
for each row
begin
update goods set num = num + old.much - new.much where id = new.id(或者old.id)
end$
先买了三个商品
insert into orders(gid,much) values(1,3)$
修改订单
update orders set much = 5 where oid = 1$
6.当下的订单里面商品的数量大于现有的库存，商品表就会被修改为负数
insert into orders(gid,much) values(3,40);
after:先完成数据的增删改，再触发触发器
before:先触发触发器，再进行增删改
create trigger tg5
before
insert
on orders
for each row
begin
declare rnum int;
select num into rnum from goods where id = new.gid;
if new.much > rnum then
set new.much = rnum;
end if;
update goods set num = num - new.much where id = new.gid;
end$
insert into orders(gid,much) values(2,30)$