set @Sum_Num=(SELECT count(*) as Sum_Num FROM Barn WHERE rtrim(ProBarcode)=rtrim(@ProBarcode) AND rtrim(pihao)=rtrim(@pihao))
set @Dan_Price=(select Dan_Price from Barn WHERE rtrim(ProBarcode)=rtrim(@ProBarcode) AND rtrim(pihao)=rtrim(@pihao))
set @zk=(select zk from Barn WHERE rtrim(ProBarcode)=rtrim(@ProBarcode) AND rtrim(pihao)=rtrim(@pihao))
set @num=(select Sum_Num from Barn WHERE rtrim(ProBarcode)=rtrim(@ProBarcode) AND rtrim(pihao)=rtrim(@pihao))
set @zhiliang=(select ifyanshou from storagein where rtrim(ProBarcode)=rtrim(@ProBarcode) AND rtrim(pihao)=rtrim(@pihao) and id=@id)
if(@zhiliang <>'0')
begin
if (@Sum_Num>0)
begin
if((@num+@ProNum)>0)
begin
UPDATE Barn SET Sum_Num = Sum_Num + @ProNum,Dan_Price=(@num*dbo.GetZkPrice(@Dan_Price,@zk)+@ProNum*dbo.GetZkPrice(@S_Dan_Price,@S_zk))/(@num+@ProNum),zk=0 WHERE (rtrim(ProBarcode) = @ProBarcode) AND (rtrim(pihao) = @pihao)
end
else--
begin
UPDATE Barn SET Sum_Num = Sum_Num + (@ProNum),zk=0 WHERE (rtrim(ProBarcode) = @ProBarcode) AND (rtrim(pihao) = @pihao)
end
end
else
begin
INSERT INTO Barn ( Departmentcode, inputcode, ProviderBh, BalanceMode,StorageInType, ProBarcode, BarnCode, Dan_Price, Pei_Price, Pi_Price, pihao, AvailabilityTime, Sum_Num, memo,zk) SELECT Departmentcode, inputcode, ProviderBh, BalanceMode, StorageInType, ProBarcode, BarnCode, Dan_Price, Pei_Price, Pi_Price, pihao, AvailabilityTime, ProNum, memo,zk FROM StorageIn WHERE (id = @id)
end
/*修改基础档案的进价,零售价格,批发价格*/
update Product set Dan_Price=dbo.GetZkPrice(@S_Dan_Price,@S_zk),Pei_Price=@S_Pei_Price,Pi_Price=@S_Pi_Price where barcode=@ProBarcode
/*修改仓库库存的零售价格,批发价格*/
update Barn set Pei_Price=@S_Pei_Price,Pi_Price= @S_Pi_Price where probarcode=@ProBarcode
/*修改门店库存的零售价格*/
update Barn_Branch set Pei_Price=@S_Pei_Price where probarcode=@ProBarcode
end
此段不是已经判断 库存中是否有这个商品呀
你又整个触发器来“忽悠”一通做啥子哦?
set @Dan_Price=(select Dan_Price from Barn WHERE rtrim(ProBarcode)=rtrim(@ProBarcode) AND rtrim(pihao)=rtrim(@pihao))
set @zk=(select zk from Barn WHERE rtrim(ProBarcode)=rtrim(@ProBarcode) AND rtrim(pihao)=rtrim(@pihao))
set @num=(select Sum_Num from Barn WHERE rtrim(ProBarcode)=rtrim(@ProBarcode) AND rtrim(pihao)=rtrim(@pihao))
set @zhiliang=(select ifyanshou from storagein where rtrim(ProBarcode)=rtrim(@ProBarcode) AND rtrim(pihao)=rtrim(@pihao) and id=@id)
if(@zhiliang <>'0')
begin
if (@Sum_Num>0)
begin
if((@num+@ProNum)>0)
begin
UPDATE Barn SET Sum_Num = Sum_Num + @ProNum,Dan_Price=(@num*dbo.GetZkPrice(@Dan_Price,@zk)+@ProNum*dbo.GetZkPrice(@S_Dan_Price,@S_zk))/(@num+@ProNum),zk=0 WHERE (rtrim(ProBarcode) = @ProBarcode) AND (rtrim(pihao) = @pihao)
end
else--
begin
UPDATE Barn SET Sum_Num = Sum_Num + (@ProNum),zk=0 WHERE (rtrim(ProBarcode) = @ProBarcode) AND (rtrim(pihao) = @pihao)
end
end
else
begin
INSERT INTO Barn ( Departmentcode, inputcode, ProviderBh, BalanceMode,StorageInType, ProBarcode, BarnCode, Dan_Price, Pei_Price, Pi_Price, pihao, AvailabilityTime, Sum_Num, memo,zk) SELECT Departmentcode, inputcode, ProviderBh, BalanceMode, StorageInType, ProBarcode, BarnCode, Dan_Price, Pei_Price, Pi_Price, pihao, AvailabilityTime, ProNum, memo,zk FROM StorageIn WHERE (id = @id)
end
/*修改基础档案的进价,零售价格,批发价格*/
update Product set Dan_Price=dbo.GetZkPrice(@S_Dan_Price,@S_zk),Pei_Price=@S_Pei_Price,Pi_Price=@S_Pi_Price where barcode=@ProBarcode
/*修改仓库库存的零售价格,批发价格*/
update Barn set Pei_Price=@S_Pei_Price,Pi_Price= @S_Pi_Price where probarcode=@ProBarcode
/*修改门店库存的零售价格*/
update Barn_Branch set Pei_Price=@S_Pei_Price where probarcode=@ProBarcode
end
此段不是已经判断 库存中是否有这个商品呀
你又整个触发器来“忽悠”一通做啥子哦?
解决方案 »
- Report Services不能正常显示报表
- 数据库还原
- 关于BULK INSERT的问题,在线等???
- sql 2005 function 是否可以返回 table
- 这个存储过程该怎么写?
- 急!!服务器坏了,SQL做了9月份做了完整备份,其它时间做了差异备份,换台服务器怎么恢复数据库啊
- MS Server 2000中设置表的访问权限
- 见文章内容中的SQL语句,如果把Insert Into 拿掉,结果出来只要3秒,可把Insert Into开起来就要36秒,是什么原因啊?
- 当对一张表执行SELECT时有些日期区间操作正常但有些区间会出现SELECT ERROR的提示
- 请教sql server2012 需要限制最大内存吗?
- 这个查询一个语句能实现?
- [请教]使用游标的问题
开始(AS后)加 begin tran 在结尾(GO前)加 commit tran