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

1
2
3
4
5
6
7
8
9

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 '饼干'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

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);