IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='SalePlan' AND TYPE='TR')
DROP TRIGGER SalePlan
GO
CREATE TRIGGER [SalePlan] ON sa_note
for INSERT
ASDECLARE @cinvcode VARCHAR(20)
DECLARE @fqty float(8)
DECLARE @Customerid varchar(12)
DECLARE @cnoteid varchar(20)
DECLARE @dsale datetimeDECLARE @tmp datetimeDECLARE @tmp11 varchar(20)set @cnoteid = (select cnoteid from inserted where bmain=1 )
set @fqty = (select fqty from inserted where bmain=1)
set @cinvcode = (select cinvcode from inserted where bmain=1)
set @customerid=(select ccuscode from sa_notehead where cnoteid=@cnoteid)
set @tmp=(select dsale from sa_notehead where cnoteid=@cnoteid)
set @dsale=convert(varchar,@tmp,120)
if not exists (select customerid from xxzx_saleplan where customerid=@customerid and cinvcode=@cinvcode and fqty>=@fqty and edate>=@dsale and bdate<=@dsale)-- if (select count(customerid) from xxzx_saleplan where customerid=@customerid and cinvcode=@cinvcode and fqty>=@fqty and edate>=@dsale and bdate<=@dsale)=0
BEGIN
--insert into xxzx_tmp (cinvcode,ccuscode,fqty,edate) values (@cinvcode,@customerid,@fqty,@dsale)
RAISERROR('此客户计划数量不足!',16,1)
rollback transaction
delete from sa_notehead where cnoteid=@cnoteid
end
else
begin
update xxzx_saleplan set fqty=fqty-@fqty where customerid=@customerid and cinvcode=@cinvcode and edate>=@dsale and bdate<=@dsale
endif not exists (select customerid from xxzx_saleplan where customerid=@customerid and cinvcode=@cinvcode and fqty>=@fqty and edate>=@dsale and bdate<=@dsale)
感觉这句话无论条件是否满足都成立,只有将where 后边的内容全部去除才可以选出customerid,请高手指点
DROP TRIGGER SalePlan
GO
CREATE TRIGGER [SalePlan] ON sa_note
for INSERT
ASDECLARE @cinvcode VARCHAR(20)
DECLARE @fqty float(8)
DECLARE @Customerid varchar(12)
DECLARE @cnoteid varchar(20)
DECLARE @dsale datetimeDECLARE @tmp datetimeDECLARE @tmp11 varchar(20)set @cnoteid = (select cnoteid from inserted where bmain=1 )
set @fqty = (select fqty from inserted where bmain=1)
set @cinvcode = (select cinvcode from inserted where bmain=1)
set @customerid=(select ccuscode from sa_notehead where cnoteid=@cnoteid)
set @tmp=(select dsale from sa_notehead where cnoteid=@cnoteid)
set @dsale=convert(varchar,@tmp,120)
if not exists (select customerid from xxzx_saleplan where customerid=@customerid and cinvcode=@cinvcode and fqty>=@fqty and edate>=@dsale and bdate<=@dsale)-- if (select count(customerid) from xxzx_saleplan where customerid=@customerid and cinvcode=@cinvcode and fqty>=@fqty and edate>=@dsale and bdate<=@dsale)=0
BEGIN
--insert into xxzx_tmp (cinvcode,ccuscode,fqty,edate) values (@cinvcode,@customerid,@fqty,@dsale)
RAISERROR('此客户计划数量不足!',16,1)
rollback transaction
delete from sa_notehead where cnoteid=@cnoteid
end
else
begin
update xxzx_saleplan set fqty=fqty-@fqty where customerid=@customerid and cinvcode=@cinvcode and edate>=@dsale and bdate<=@dsale
endif not exists (select customerid from xxzx_saleplan where customerid=@customerid and cinvcode=@cinvcode and fqty>=@fqty and edate>=@dsale and bdate<=@dsale)
感觉这句话无论条件是否满足都成立,只有将where 后边的内容全部去除才可以选出customerid,请高手指点
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货