mysql 触发器

mysql之触发器trigger

触发器(trigger)：监视某种情况，并触发某种操作。

触发器创建语法四要素：

1. 监视地点(table)
2. 监视事件(insert/update/delete)
3. 触发时间(after/before)
4. 触发事件(insert/update/delete)

语法：

create trigger triggerName(触发器名称)
after/before (触发时间)
insert/update/delete (监视事件)
on tableName(表名监视地点)
for each row #这句话在mysql是固定的
begin
sql语句;
end;

查看已有的触发器triggers：show triggers
删除已有的触发器triggers: drop trigger triggerName(触发器名称)

demo：

CREATE TABLE `goods` (
  `gid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `num` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='商品库存表';

CREATE TABLE `order` (
  `oid` int(11) NOT NULL AUTO_INCREMENT,
  `gid` int(11) NOT NULL,
  `much` int(11) NOT NULL,
  PRIMARY KEY (`oid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单表';

CREATE TABLE `sale` (
  `sid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `gid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
  `sale_num` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '售后数量',
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='售后数量';

#添加商品
insert into goods (name,num)
values
('cat',10),
('dog',15),
('pig',10);

1.创建订单的时候减库存:

#判断触发器是否存在
drop trigger if exists addorder;

#创建触发器
create trigger addorder
BEFORE
INSERT
on `order`
for each ROW
BEGIN
	update goods set num=num-new.much where gid=new.gid;
end;

#添加订单之前查看库存的数量
select * from goods where gid=1;	

#添加订单
insert into `order` (gid,much) VALUES (1,2);

#执行完之后查看gid是1 的商品库存数量
select * from goods where gid=1;

截图：

结论：执行添加订单之后，gid是1 的商品库存减少

2.订单中买的物品多了需要退

#判断触发器是否存在
drop trigger if EXISTS saleorder;

#创建触发器
create trigger saleorder
AFTER
INSERT
on sale
for each ROW
BEGIN
	update goods set num=num+new.sale_num where gid=new.gid;
end;

#申请售后之前查看库存的数量
select * from goods where gid=1;

#申请售后
insert into `sale` (gid,sale_num) value (1,3);

#查看申请售后之后的库存数量
select * from goods where gid=1;

截图：

3.创建订单的时候-订单的销售数量大于库存

如果剩余的库存不多，客户买的数量多余库存量，会发生的情况是，销量变成负值；
能否在购买much>num的时候，把much的最大数量改成库存的num;
before之前就先操作插入订单操作

优化版：

#判断触发器是否存在
drop trigger if EXISTS addorder;

#创建触发器
create trigger addorder
BEFORE
INSERT
on `order`
for each ROW
BEGIN
	declare rnum int;
	select num into rnum from goods where gid=new.gid;
	if new.much>rnum THEN	
		set new.much=rnum;
	end if;
	update goods set num=num-new.much where gid=new.gid;
end;

#添加订单之前查看库存的数量
select * from goods where gid=1;

#添加订单
insert into `order` (gid,much) VALUES (1,20);

#执行完之后查看gid是1 的商品库存数量
select * from goods where gid=1;

截图：


执行之后，商品库存没有变成负数