---TB050272 王繼州           綜合設(shè)計(jì)儲存過程、觸發(fā)器

use supermarket

------------===========自定義視圖===========---------------
--查詢供貨商對應(yīng)自己的供貨商品視圖

go
create view wjz_progood
as
select tord.providerid ,t.providername,tor.goodsid,tb.upc
from t_orderdetail tor,t_order tord,t_provider t,t_goods tb
where tor.orderid=tord.orderid and tord.providerid=t.providerid
and tor.goodsid=tb.goodsid
go
--待退貨商品和對應(yīng)的供貨商的視圖
create view wjz_proback
as
select w.providerid,t.goodsid,t.goodsname,tb.upc,t.goodsnum,t.typename,t.brand,t.spec
from wjz_progood w,t_waitgoods t,t_goods tb
where w.goodsid=t.goodsid and t.goodsid=tb.goodsid
go


-----------========自動生成ID號的函數(shù)=========-------------

create proc proid
@tablename  varchar(20),@columnname varchar(20),@stra varchar(20 )='noid' output
as
declare @strb varchar(2),@strc varchar(20),@num int
if exists(select * from sysobjects where name='a' and type='u')
drop table a
execute('select top 1 ' + @columnname + ' into a from ' + @tablename + ' order by '+ @columnname + ' desc')
set @stra=(select * from a)
set @num=len(@stra)
set @strb=left(@stra,1)
set @strc=right(@stra,len(@stra)-1)
set @strc=cast(@strc as int)+1
set @strc=replace(space(@num-1-len(cast(@strc as char)))+@strc,' ','0')
set @stra=@strb + @strc
go
----------退貨詳情表中添加數(shù)據(jù)-------
create procedure wjz_bgdadd
@backid varchar(8),@goodid varchar(8),@goodname varchar(20),@upc varchar(15),@num int
as
declare @price1 money,@price money
select @price1=inprice from t_price where goodsid=@goodid
set @price=@price1*@num
insert into t_bgooddetail values(@backid,@goodid,@goodname,@upc,@num,@price)
go
-----------退貨單中添加內(nèi)容-----------
create procedure wjz_bgtadd
@backid varchar(8),@prid varchar(5),@employid varchar(8)
as 
declare @tstart datetime,@endtime datetime,@m money
set @tstart=getdate()
set @endtime=dateadd(day,7,@tstart)
select @m=sum(inprice) from t_bgooddetail where backid=@backid
insert into t_bgoodtable(backid,providerid,stime,protime,allmoney,employeeid) values(@backid,@prid,@tstart,@endtime,@m,@employid)
go


---------======觸發(fā)器=======-------------
---
create trigger wjz_epladd
on t_employee for insert
as 
declare @eid varchar(5)
select @eid=employeeid from inserted
insert into t_purview values(@eid,1,'123')
go


----退貨單表的狀態(tài)更新的時候,待退貨商品的數(shù)量改變
create trigger wjz_bwch
on t_bgoodtable for update
as
declare @bid varchar(8),@goodid varchar(8),@num int,@num2 int,@status varchar(4)
select @bid=backid ,@status=state from inserted
if @status='確認(rèn)'
begin
declare p cursor forward_only 
for select goodsid,num from t_bgooddetail where backid=@bid
open p
fetch next from p into @goodid,@num
while @@fetch_status=0
begin
	select @num2=goodsnum from t_waitgoods where goodsid=@goodid
	if @num=@num2--如果待退貨商品的數(shù)量為0則直接從表中刪除
		begin 
		delete t_waitgoods where goodsid=@goodid
		end 
	else
		begin
		update t_waitgoods set goodsnum=@num2-@num where goodsid=@goodid
		end
fetch next from p into @goodid,@num
end
close p
deallocate p
end

go
---根據(jù)入庫商品的價格調(diào)整零售的價格
create trigger wjz_price
on t_order for update
as
declare @orderid varchar(8),@goodid varchar(8),@num float,@price money,@price1 decimal(8,1),@num2 int,
@goodname varchar(20),@spec varchar(10),@brand varchar(20),@type varchar(20),@status varchar(4),@num3 int,@allmoney money
select @orderid=orderid,@status=orderstate from inserted
if @status='確認(rèn)'
begin
select @allmoney=sum(num*inprice) from t_orderdetail where orderid=@orderid
update t_order set allmoney=@allmoney where orderid=@orderid
declare p cursor forward_only
for select goodsid,inprice from t_orderdetail where orderid=@orderid
open p
fetch next from p into @goodid,@price
while @@fetch_status=0
begin 
	select @num2=num from t_orderdetail where goodsid=@goodid
	select @num3=store from t_goods where goodsid=@goodid
	set @num3=@num3+@num2
	update t_goods set store=@num3 where goodsid=@goodid
	if not exists(select * from t_price where goodsid=@goodid)
		begin
		select @goodname=goodsname,@spec=spec,@brand=brand,@type=typename from t_goods where goodsid=@goodid
		insert into t_price(goodsid,goodsname,spec,brand,typename) values(@goodid,@goodname,@spec,@brand,@type)
		end
	select @num=rate from t_price where goodsid=@goodid
	set @price1=@num*@price
	update t_price set inprice=@price,outprice=@price1 where goodsid=@goodid
	fetch next from p into @goodid,@price
end
close p
deallocate p
end
go
--------------檢查待退貨商品
create trigger wjz_qback
on t_qback for update
as
declare @bid varchar(8),@goodid varchar(8),@goodname varchar(20),@num int,
@brand varchar(20),@type varchar(20),@spec varchar(10),@num2 int
select @bid=qbackid from inserted
declare p cursor forward_only
for select goodsid,goodsname,num from t_qbackdetail where qbackid=@bid
open p
fetch next from p into @goodid,@goodname,@num
while @@fetch_status=0
begin
	select @type=typename,@brand=brand,@spec=spec from t_goods where goodsid=@goodid
	if exists(select * from t_waitgoods where goodsid=@goodid)
		begin
		select @num2=goodsnum from t_waitgoods where goodsid=@goodid
		set @num2=@num2+@num
		update t_waitgoods set goodsnum=@num2 where goodsid=@goodid
		end
	else
		begin
		insert into t_waitgoods values(@goodid,@goodname,@num,@type,@brand,@spec)
		end
	fetch next from p into @goodid,@goodname,@num
end
close p
deallocate p

---楊峙凌---存儲過程
--刪除流水號時同時修改賣場相應(yīng)商品的庫存
if exists(select * from sysobjects where name='proYZLaaa' and type='p')
drop procedure proYZLaaa
go
create procedure proYZLaaa @saleid int
as
declare a cursor for 
select goodsid,num from t_sdetail where saleid=@saleid
declare @goodsid varchar(8),@num int
open a
fetch next from a into @goodsid,@num
while @@fetch_status=0
begin
update t_market set num=num+@num where goodsid=@goodsid
fetch next from a into @goodsid,@num
end
close a
deallocate a
go
--張超--存儲過程
--當(dāng)修改訂單狀態(tài)為“已提”時自動減少相應(yīng)庫存數(shù)量
create proc zc_aaa @corderid int 
as
declare zc_bbb cursor for
select goodsid,num from t_corderdetail where corderid=@corderid
declare @goodsid varchar(8),@num int
open zc_bbb
fetch next from zc_bbb into @goodsid,@num
while @@fetch_status=0
begin
update t_goods set store=store-@num where goodsid=@goodsid
fetch next from zc_bbb into @goodsid,@num
end
close zc_bbb
deallocate zc_bbb