谢谢大家USE [UFDATA_003_2009]
GO
/****** 对象: Trigger [dbo].[somainupc] 脚本日期: 12/29/2009 16:42:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[somainupc] ON [dbo].[SO_SODetails]
for INSERT, UPDATE, DELETE
AS
declare @message as varchar(10)
declare @zdcgjia as varchar(10)
declare @jiagece as varchar(10)
SELECT @message=a.cdefine35,@zdcgjia=c.cinvdefine12, @jiagece=c.cinvdefine12- a.cdefine35 from so_sodetails as a inner join inserted as b on a.cSOCode=b.cSOCode
inner join inventory as c on a.cinvcode=c.cinvcode IF EXISTS(
SELECT 1
from so_sodetails as a inner join inserted as b on a.cSOCode=b.cSOCode
inner join inventory as c on a.cinvcode=c.cinvcode
where a.cdefine35<=cinvdefine12 --与存货档案中的最低价格进行比较,如果出灌价小,进行如下操作
)
BEGIN
RAISERROR(@message,18,8)
RAISERROR('经测算本订单的出灌价为%s元,比公司规定的最低限价%s元低%s元,该订单不允许保存,请调整销售价格或最低限价!',16,1,@message,@zdcgjia,@jiagece) --问题1 该自定义提示在SQL2000中提示正确,在SQL2005中提示 数字(未显示文字)如何修改?
--问题2 可否在此增加管理员密码校对,如输入1122,则跳过系统控制,允许保存,密码不符则执行不保存
ROLLBACK TRANSACTION
END
ELSE
BEGIN
declare @newbz as float ,@oldbz as float ,@cccb as float,@yhtxlr as float
set @newbz=140 --新桶
set @oldbz=110 --旧桶
set @cccb =70 --仓储成本
set @yhtxlr=0.003*1.17
update a set a.cDefine27 = --自动更新运费
case when b.cSCCode in (1,2,7,11) and cdefine2="是" then --承担运费且包车
round( cDefine25*1/iQuantity*0.95*1.17,2)
when b.cSCCode in (1,2,7,11) and cdefine2="否" then --承担运费且不包车
round(cDefine25*iQuantity/iQuantity*0.95*1.17,2)
else --除承担运费外,代垫和自提均为0
0
end
from so_sodetails a
inner join so_somain b
on a.cSOCode=b.cSOCode
where b.cverifier is null update a set a.cdefine31= --自动更新包装桶成本
case when c.cinvstd="净水" or c.cinvstd="原装桶"then --规格为净水或原装桶的包装费用是0
0
when c.cinvstd="二次桶" and c.cinvdefine11 is not null then --规格为二次桶的按不含税成本元每只计算
round((iQuantity*1000/c.cinvdefine11*@oldbz)/iQuantity,2)
when c.cinvstd="二次桶" and c.cinvdefine11 is null then --规格为全二次桶且存货档案未设置自定项(一桶=多少KG)的暂按660元每吨
round(6.25*@oldbz,2)
when c.cinvstd="全新桶" and c.cinvdefine11 is not null then --规格为全新桶的按不含税成本元每只计算
round(iQuantity*1000/c.cinvdefine11*@newbz/iQuantity,2)
when c.cinvstd="全新桶" and c.cinvdefine11 is null then --规格为全新桶且存货档案未设置自定项(一桶=多少KG)的暂按960元每吨
round(6.25*@newbz,2)
else
0
end
from (so_sodetails as a
inner join so_somain as b on a.cSOCode=b.cSOCode) inner join inventory as c on a.cinvcode=c.cinvcode
where b.cverifier is null update a set a.cdefine34=@cccb --自动更仓储成本
from so_sodetails a inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null update a set a.cdefine29=round(d.成本单价*1.17,2) --自动更新采购成本
FROM (Inventory INNER JOIN SO_SODetails as a ON Inventory.cInvCode = a.cInvCode) INNER JOIN 库存量表 as d ON Inventory.cInvName = d.产品ID; declare @hjiquantity float --取销售订单销量合计
SELECT @hjiquantity= SUM(SO_SODetails.iQuantity)
FROM SO_SODetails INNER JOIN
SO_SOMain ON SO_SODetails.ID = SO_SOMain.ID
GROUP BY SO_SODetails.cSOCode
--如果承兑汇票期限未输入,则按照6个月计算
update a set a.cdefine23=
case when b.cdefine7 is null then 0
else
round(b.cdefine7*(case when b.cdefine5 in(1,2,3,4,5,6) then b.cdefine5 else 6 end) *@yhtxlr/(@hjiquantity),2) --计算承兑汇票单位利息
end
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null update a set a.cdefine35=a.itaxunitprice-a.cdefine31-a.cdefine27 -a.cdefine23 --更新出灌价,扣除运费、包装、利息
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null update a set a.cdefine30=iQuantity*(iunitprice-cast(cdefine29 as float)/1.17-cast(cdefine27 as float)/1.17-cast(cdefine31 as float)/1.17-cast(cdefine34 as float)-cast(cdefine23 as float)/1.17) --计算所得税前利润
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null
END
GO
/****** 对象: Trigger [dbo].[somainupc] 脚本日期: 12/29/2009 16:42:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[somainupc] ON [dbo].[SO_SODetails]
for INSERT, UPDATE, DELETE
AS
declare @message as varchar(10)
declare @zdcgjia as varchar(10)
declare @jiagece as varchar(10)
SELECT @message=a.cdefine35,@zdcgjia=c.cinvdefine12, @jiagece=c.cinvdefine12- a.cdefine35 from so_sodetails as a inner join inserted as b on a.cSOCode=b.cSOCode
inner join inventory as c on a.cinvcode=c.cinvcode IF EXISTS(
SELECT 1
from so_sodetails as a inner join inserted as b on a.cSOCode=b.cSOCode
inner join inventory as c on a.cinvcode=c.cinvcode
where a.cdefine35<=cinvdefine12 --与存货档案中的最低价格进行比较,如果出灌价小,进行如下操作
)
BEGIN
RAISERROR(@message,18,8)
RAISERROR('经测算本订单的出灌价为%s元,比公司规定的最低限价%s元低%s元,该订单不允许保存,请调整销售价格或最低限价!',16,1,@message,@zdcgjia,@jiagece) --问题1 该自定义提示在SQL2000中提示正确,在SQL2005中提示 数字(未显示文字)如何修改?
--问题2 可否在此增加管理员密码校对,如输入1122,则跳过系统控制,允许保存,密码不符则执行不保存
ROLLBACK TRANSACTION
END
ELSE
BEGIN
declare @newbz as float ,@oldbz as float ,@cccb as float,@yhtxlr as float
set @newbz=140 --新桶
set @oldbz=110 --旧桶
set @cccb =70 --仓储成本
set @yhtxlr=0.003*1.17
update a set a.cDefine27 = --自动更新运费
case when b.cSCCode in (1,2,7,11) and cdefine2="是" then --承担运费且包车
round( cDefine25*1/iQuantity*0.95*1.17,2)
when b.cSCCode in (1,2,7,11) and cdefine2="否" then --承担运费且不包车
round(cDefine25*iQuantity/iQuantity*0.95*1.17,2)
else --除承担运费外,代垫和自提均为0
0
end
from so_sodetails a
inner join so_somain b
on a.cSOCode=b.cSOCode
where b.cverifier is null update a set a.cdefine31= --自动更新包装桶成本
case when c.cinvstd="净水" or c.cinvstd="原装桶"then --规格为净水或原装桶的包装费用是0
0
when c.cinvstd="二次桶" and c.cinvdefine11 is not null then --规格为二次桶的按不含税成本元每只计算
round((iQuantity*1000/c.cinvdefine11*@oldbz)/iQuantity,2)
when c.cinvstd="二次桶" and c.cinvdefine11 is null then --规格为全二次桶且存货档案未设置自定项(一桶=多少KG)的暂按660元每吨
round(6.25*@oldbz,2)
when c.cinvstd="全新桶" and c.cinvdefine11 is not null then --规格为全新桶的按不含税成本元每只计算
round(iQuantity*1000/c.cinvdefine11*@newbz/iQuantity,2)
when c.cinvstd="全新桶" and c.cinvdefine11 is null then --规格为全新桶且存货档案未设置自定项(一桶=多少KG)的暂按960元每吨
round(6.25*@newbz,2)
else
0
end
from (so_sodetails as a
inner join so_somain as b on a.cSOCode=b.cSOCode) inner join inventory as c on a.cinvcode=c.cinvcode
where b.cverifier is null update a set a.cdefine34=@cccb --自动更仓储成本
from so_sodetails a inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null update a set a.cdefine29=round(d.成本单价*1.17,2) --自动更新采购成本
FROM (Inventory INNER JOIN SO_SODetails as a ON Inventory.cInvCode = a.cInvCode) INNER JOIN 库存量表 as d ON Inventory.cInvName = d.产品ID; declare @hjiquantity float --取销售订单销量合计
SELECT @hjiquantity= SUM(SO_SODetails.iQuantity)
FROM SO_SODetails INNER JOIN
SO_SOMain ON SO_SODetails.ID = SO_SOMain.ID
GROUP BY SO_SODetails.cSOCode
--如果承兑汇票期限未输入,则按照6个月计算
update a set a.cdefine23=
case when b.cdefine7 is null then 0
else
round(b.cdefine7*(case when b.cdefine5 in(1,2,3,4,5,6) then b.cdefine5 else 6 end) *@yhtxlr/(@hjiquantity),2) --计算承兑汇票单位利息
end
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null update a set a.cdefine35=a.itaxunitprice-a.cdefine31-a.cdefine27 -a.cdefine23 --更新出灌价,扣除运费、包装、利息
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null update a set a.cdefine30=iQuantity*(iunitprice-cast(cdefine29 as float)/1.17-cast(cdefine27 as float)/1.17-cast(cdefine31 as float)/1.17-cast(cdefine34 as float)-cast(cdefine23 as float)/1.17) --计算所得税前利润
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null
END
解决方案 »
- SQL2005中查看事务日志方法
- 在触发器中加入表变量如何操作(sql server2000)
- 这个SQL怎么写啊? 急~!
- 急,关于SQL Server 2000系统目录视图的几个问题(如何使用替代方法实现SQL Server 2005相应系统目录视图的某些字段)?
- sql server权限问题
- 已知每月第一天,求每月第二个星期二
- sql GROUP BY的问题
- 请教一个dts的问题。
- 在线等待:修改字段属性,为何“殊途”不“同归”?
- 请教——数据库——高手!小弟这边有礼啦!
- SQl Server2005还原2000时 出现问题,目录查找失败,出现操作系统错误..
- 如何实现某一字段的值转为标题,多谢大家了,急~
问题2 可否在此增加管理员密码校对,如输入1122,则跳过系统控制,允许保存,密码不符则执行不保存