set @types=(select ccprec_sub_type from Inserted)
set @subtypes=(select ccprec_sub_subtype from Inserted)
if update(CcpRec_Sub_S_Zjr)
begin
if @subtypes='销售出库'
begin
BEGIN TRANSACTION c1
update CCPrec_Sub set CcpRec_Sub_S_Zjr_BHS=b.CcpRec_Sub_S_Zjr/1.17,CcpRec_Sub_S_Zjr_BWB_BHS=b.CcpRec_Sub_S_Zjr_BWB/1.17 from CCPrec_Sub a join inserted b on a.ID=b.ID
COMMIT TRANSACTION c1
end
else
begin
BEGIN TRANSACTION d1
update CCPrec_Sub set CcpRec_Sub_S_Zjr_BHS=b.CcpRec_Sub_S_Zjr,CcpRec_Sub_S_Zjr_BWB_BHS=b.CcpRec_Sub_S_Zjr_BWB from CCPrec_Sub a join inserted b on a.ID=b.ID
COMMIT TRANSACTION d1
end
end
set @subtypes=(select ccprec_sub_subtype from Inserted)
if update(CcpRec_Sub_S_Zjr)
begin
if @subtypes='销售出库'
begin
BEGIN TRANSACTION c1
update CCPrec_Sub set CcpRec_Sub_S_Zjr_BHS=b.CcpRec_Sub_S_Zjr/1.17,CcpRec_Sub_S_Zjr_BWB_BHS=b.CcpRec_Sub_S_Zjr_BWB/1.17 from CCPrec_Sub a join inserted b on a.ID=b.ID
COMMIT TRANSACTION c1
end
else
begin
BEGIN TRANSACTION d1
update CCPrec_Sub set CcpRec_Sub_S_Zjr_BHS=b.CcpRec_Sub_S_Zjr,CcpRec_Sub_S_Zjr_BWB_BHS=b.CcpRec_Sub_S_Zjr_BWB from CCPrec_Sub a join inserted b on a.ID=b.ID
COMMIT TRANSACTION d1
end
end
DECLARE @types varchar(1)
DECLARE @subtypes varchar(30)
错误是子查询返回的值不止一个,请指正,谢谢
set @types=(select ccprec_sub_type from Inserted)
set @subtypes=(select ccprec_sub_subtype from Inserted)
---你可以先print输出查看下其返回值,再操作..
set @subtypes=(select ccprec_sub_subtype from Inserted)
Inserted有多个ccprec_sub_type或ccprec_sub_subtype
改为
set @types=(select top 1 ccprec_sub_type from Inserted)
set @subtypes=(select top 1 ccprec_sub_subtype from Inserted)
set @subtypes=(select top 1 ccprec_sub_subtype from Inserted)另,建议取消自定义事务,对一条语句,系统会自动设置一个事务的.
set @types=(select top 1 ccprec_sub_type from Inserted)
set @subtypes=(select top 1 ccprec_sub_subtype from Inserted)
set @subtypes=(select ccprec_sub_subtype from Inserted)
set赋值时只能是一个值,不能是一组值,上面的select ccprec_sub_type from Inserted
或者select ccprec_sub_subtype from Inserted的返回值太多,
这说明你修改或插入时,一次性有多个记录,所以修改你的程序改为一次性只允许一条记录修改或插入即可
,这样可不修改触发器
update CCPrec_Sub
set CcpRec_Sub_S_Zjr_BHS=b.CcpRec_Sub_S_Zjr/1.17,CcpRec_Sub_S_Zjr_BWB_BHS=b.CcpRec_Sub_S_Zjr_BWB/1.17
from CCPrec_Sub a join inserted b on a.ID=b.ID
where b.ccprec_sub_subtype='销售出库'update CCPrec_Sub
set CcpRec_Sub_S_Zjr_BHS=b.CcpRec_Sub_S_Zjr,CcpRec_Sub_S_Zjr_BWB_BHS=b.CcpRec_Sub_S_Zjr_BWB
from CCPrec_Sub a join inserted b on a.ID=b.ID
where b.ccprec_sub_subtype<>'销售出库'
from CCPrec_Sub a join inserted b on a.ID=b.ID
update a set CcpRec_Sub_S_Zjr_BHS=b.CcpRec_Sub_S_Zjr/ case when b.ccprec_sub_subtype='销售出库' then 1.17 else 1 end,CcpRec_Sub_S_Zjr_BWB_BHS=b.CcpRec_Sub_S_Zjr_BWB/case when b.ccprec_sub_subtype='销售出库' then 1.17 else 1 end
from CCPrec_Sub a join inserted b on a.ID=b.ID