实验8-存储过程和触发器的创建及应用

（1）创建一个有输入参数的存储过程，用于查询指定类别的所有商品信息。并执行该存储过程。

create procedure proc_displaygoods
	@category varchar(100)
as
select G.GoodsNO 商品编号,G.GoodsName 商品名,G.SalePrice 售价,G.InPrice 进价,G.Number 数量,C.CategoryNO 类别编号,C.CategoryName 类别
from Goods G
join Category C on G.CategoryNO = C.CategoryNO
where CategoryName like @category

execute dbo.proc_displaygoods '饼干'

（2）创建一个有输入输出参数的存储过程，用于查询指定商品名的售价。并执行该存储过程。

create procedure proc_findsale
	@goodsname varchar(100),@price decimal(18,2) output
as
select @price=SalePrice from Goods where GoodsName = @goodsname
go

declare @goodsname varchar(100),@price decimal(18,2)
set @goodsname = '好吃点'
execute dbo.proc_findsale @goodsname,@price output
select @goodsname 商品名,@price 售价
go

（3）创建一个触发器。向销售表SaleBill中插入一条记录时，这个触发器将更新商品表Goods。Goods 表中数量为原有数量减去销售数量。如果库存数量小于10，则提示"该商品数量小于10，低于安全库存量，请及时进货”；如果原有数量不足，则提示“数量不足！”。

create trigger tri_updateSaleBill
on SaleBill
after insert
as
begin
	declare @number int,@goodsno varchar(10)
	select @number = Number,@goodsno = GoodsNO from inserted
	if(select Number from Goods where GoodsNO = @goodsno) < @number
		begin
			print '库存不足'
			rollback
		end
	else
		begin
			update Goods set Number = Number - @number where GoodsNO = @goodsno
			if(select Number from Goods where GoodsNO = @goodsno) < 10
				begin
					print '该商品数量小于10，低于安全库存量，请及时进货'
				end
		end
end


CREATE TRIGGER update_goods_number
   ON  salebill
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	declare @salenumber int
	declare @storenumber int
	declare @goodsno varchar(30)
	select @salenumber=number, @goodsno=goodsno from inserted
	select @storenumber=number from goods where goodsno=@goodsno
	if @storenumber<@salenumber 
		begin
			print '库存数量不足'
			rollback
		end
	else
		begin
			update goods set number=number-@salenumber where goodsno=@goodsno
			select @storenumber=number from goods where goodsno=@goodsno
			if @storenumber < 10 
				print '该商品数量小于10，低于安全库存量，请及时进货'
		end
END



--添加一条记录，用于验证
insert into supermarket.dbo.SaleBill values('GN0020','S01','2018-06-09 00:00:00',3);