请教:如何将的触发器1应用到我下面的触发器2中(条件:SO_SOdetails.cdefine35<inventory.cinvdefine12) ,万分感谢
(红色字体部分为两表的关系,即a.cinvcode=c.cinvcode)需求:用本触发器计算求出的SO_SOdetails.cdefine35与inventory.cinvdefine12比较,如果前者大于后者,则允许保存譔销售订单,否则则错误提示并不允许保存触发器1、
CREATE somainupc ON [dbo].[SO_SOdetails]
for INSERT, UPDATE, DELETE
AS
IF EXISTS(....)
BEGIN
RAISERROR('提示信息!',16,1)
ROLLBACK TRANSACTION
END 触发器2、
CREATE TRIGGER somainupc ON [dbo].[SO_SOdetails]
for INSERT, UPDATE, DELETE
ASdeclare @newbz as float ,@oldbz as float ,@cccb as float,@yhtxlr as float
set @newbz=120 --新桶
set @oldbz=100 --旧桶
set @cccb =70 --仓储成本
set @yhtxlr=0.0042*1.17
update a set a.cDefine27 = --自动更新运费
case when b.cSCCode in (1,2,7,11) and cdefine2="是" then --承担运费且包车
round( cDefine25*1/iQuantity*0.93*1.17,2)
when b.cSCCode in (1,2,7,11) and cdefine2="否" then --承担运费且不包车
round(cDefine25*iQuantity/iQuantity*0.93*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 nullupdate 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
null
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 nullupdate a set a.cdefine34=@cccb --自动更仓储成本
from so_sodetails a inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is nullupdate 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 int --取销售订单销量合计
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=round(b.cdefine7*(case when b.cdefine5 in(1,2,3,4,5,6) then b.cdefine5 else 6 end) *@yhtxlr/(@hjiquantity),2) --计算承兑汇票单位利息
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is nullupdate 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 nullupdate a set a.cdefine30=round(itaxunitprice-(cdefine29+cdefine27+cdefine31+cdefine34+cdefine23)*iQuantity/1.17,0) --计算所得税前利润
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null
(红色字体部分为两表的关系,即a.cinvcode=c.cinvcode)需求:用本触发器计算求出的SO_SOdetails.cdefine35与inventory.cinvdefine12比较,如果前者大于后者,则允许保存譔销售订单,否则则错误提示并不允许保存触发器1、
CREATE somainupc ON [dbo].[SO_SOdetails]
for INSERT, UPDATE, DELETE
AS
IF EXISTS(....)
BEGIN
RAISERROR('提示信息!',16,1)
ROLLBACK TRANSACTION
END 触发器2、
CREATE TRIGGER somainupc ON [dbo].[SO_SOdetails]
for INSERT, UPDATE, DELETE
ASdeclare @newbz as float ,@oldbz as float ,@cccb as float,@yhtxlr as float
set @newbz=120 --新桶
set @oldbz=100 --旧桶
set @cccb =70 --仓储成本
set @yhtxlr=0.0042*1.17
update a set a.cDefine27 = --自动更新运费
case when b.cSCCode in (1,2,7,11) and cdefine2="是" then --承担运费且包车
round( cDefine25*1/iQuantity*0.93*1.17,2)
when b.cSCCode in (1,2,7,11) and cdefine2="否" then --承担运费且不包车
round(cDefine25*iQuantity/iQuantity*0.93*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 nullupdate 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
null
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 nullupdate a set a.cdefine34=@cccb --自动更仓储成本
from so_sodetails a inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is nullupdate 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 int --取销售订单销量合计
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=round(b.cdefine7*(case when b.cdefine5 in(1,2,3,4,5,6) then b.cdefine5 else 6 end) *@yhtxlr/(@hjiquantity),2) --计算承兑汇票单位利息
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is nullupdate 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 nullupdate a set a.cdefine30=round(itaxunitprice-(cdefine29+cdefine27+cdefine31+cdefine34+cdefine23)*iQuantity/1.17,0) --计算所得税前利润
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null
解决方案 »
- 求一个 SQL 语句,各位请进
- 触发器里的语法
- 一条应该很简单的查询语句却难到了我,帮帮忙
- where条件、group by all 和 rollup不能同时使用,那我怎么实现这个效果呢?
- 新手上路,请教连不上sql server 2005的问题!
- 为什么数据库日志很快的过于庞大?
- 一个比较难的查询语句,请大家帮忙...
- 急急我的SQL2005备份报错问题?
- 在SQL Server7.0中,我对一个表做了一个UPDATE触发器,把修改过的数据放入一个临时表中。可是每次我从Enterprise Manger中打开该表,手工个修改其中内容的时侯,无论做多少次,临时表中始终放的是最最原始的那个数据,这是为什么?
- 如何有存储过程在两个不同排序规则的数据库中进行数据的联查
- 一个小问题,求解
- mssql 有随机数函数吗 有md5加密函数吗
CREATE TRIGGER somainupc ON [dbo].[SO_SOdetails]
for INSERT, UPDATE, DELETE
AS
IF EXISTS(....)
BEGIN
RAISERROR('提示信息!',16,1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
declare @newbz as float ,@oldbz as float ,@cccb as float,@yhtxlr as float
set @newbz=120 --新桶
set @oldbz=100 --旧桶
set @cccb =70 --仓储成本
set @yhtxlr=0.0042*1.17
update a set a.cDefine27 = --自动更新运费
case when b.cSCCode in (1,2,7,11) and cdefine2="是" then --承担运费且包车
round( cDefine25*1/iQuantity*0.93*1.17,2)
when b.cSCCode in (1,2,7,11) and cdefine2="否" then --承担运费且不包车
round(cDefine25*iQuantity/iQuantity*0.93*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
null
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 int --取销售订单销量合计
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=round(b.cdefine7*(case when b.cdefine5 in(1,2,3,4,5,6) then b.cdefine5 else 6 end) *@yhtxlr/(@hjiquantity),2) --计算承兑汇票单位利息
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=round(itaxunitprice-(cdefine29+cdefine27+cdefine31+cdefine34+cdefine23)*iQuantity/1.17,0) --计算所得税前利润
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null
END
GO
只是我不知道怎么合起来,请教了,谢谢
判断条件SO_SOdetails.cdefine35都没计算出结果,显然不行吧
--try
CREATE TRIGGER somainupc ON [dbo].[SO_SOdetails]
for INSERT, UPDATE, DELETE
AS
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 b.cverifier is null and a.cdefine35<=cinvdefine12
)
BEGIN
RAISERROR('提示信息!',16,1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
declare @newbz as float ,@oldbz as float ,@cccb as float,@yhtxlr as float
set @newbz=120 --新桶
set @oldbz=100 --旧桶
set @cccb =70 --仓储成本
set @yhtxlr=0.0042*1.17
update a set a.cDefine27 = --自动更新运费
case when b.cSCCode in (1,2,7,11) and cdefine2="是" then --承担运费且包车
round( cDefine25*1/iQuantity*0.93*1.17,2)
when b.cSCCode in (1,2,7,11) and cdefine2="否" then --承担运费且不包车
round(cDefine25*iQuantity/iQuantity*0.93*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
null
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 int --取销售订单销量合计
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=round(b.cdefine7*(case when b.cdefine5 in(1,2,3,4,5,6) then b.cdefine5 else 6 end) *@yhtxlr/(@hjiquantity),2) --计算承兑汇票单位利息
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=round(itaxunitprice-(cdefine29+cdefine27+cdefine31+cdefine34+cdefine23)*iQuantity/1.17,0) --计算所得税前利润
from so_sodetails a
inner join so_somain b on a.cSOCode=b.cSOCode
where b.cverifier is null
END
GO