alter table tb add CONSTRAINT column_d_chk CHECK (column_d IS NULL OR column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR column_d LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),
--限制0<col<100 alter table tb add constraint check_col CHECK(col>0 and col<100)
你是想做ERP程序的修改吧, 建议不添加约束,添加触发器即可.
CREATE TRIGGER check1 ON table1 for INSERT,UPDATE AS IF EXISTS(select 1 from INSERTED where ....) BEGIN RAISERROR('提示信息!',16,1) ROLLBACK TRANSACTION END
请教:如何将12楼的代码应用到我下面的触发器中(条件:SO_SOdetails.cdefine35<inventory.cinvdefine12) ,万分感谢 (红色字体部分为两表的关系,即a.cinvcode=c.cinvcode) 需求:用本触发器计算求出的SO_SOdetails.cdefine35与inventory.cinvdefine12比较,如果前者大于后者,则允许保存譔销售订单,否则则错误提示并不允许保存 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
--2:可以用规则rule
alter table 表 add constraint 约束名 check(字段 <->条件)
go
create table tb(ID int identity(1,1),Sex varchar(2) check (sex in('男','女')),[name] varchar(10))
insert tb select '男','张三'
insert tb select 'a','adds'
add CONSTRAINT column_d_chk
CHECK
(column_d IS NULL OR
column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
column_d LIKE
"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),
alter table tb
add constraint check_col CHECK(col>0 and col<100)
建议不添加约束,添加触发器即可.
for INSERT,UPDATE
AS
IF EXISTS(select 1 from INSERTED where ....)
BEGIN
RAISERROR('提示信息!',16,1)
ROLLBACK TRANSACTION
END
很好,就要这个效果再请教appleller,为什么RAISERROR('提示信息!',16,1) 中的提示信息不显示,提示的是ERP中设置的提示信息
(红色字体部分为两表的关系,即a.cinvcode=c.cinvcode)
需求:用本触发器计算求出的SO_SOdetails.cdefine35与inventory.cinvdefine12比较,如果前者大于后者,则允许保存譔销售订单,否则则错误提示并不允许保存
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
应该是提示 ERP中设置的提示信息 + RAISERROR中的信息