现在要做一个关于审核和反审核的触发器。审核时检查当前出库单据的数量 与一个表的数量进行对比来判断是不是可以审核。反审核的时候更新当前表的数量到一张二开的表上面 本来是用select @FCheckerID=FCheckerID from inserted
if (isnull(@FCheckerID,0) <> 0 and (select FTranType from inserted)=21 ) begin end
做区分的 但是 这样在保存的时候也会触发 会造成数据错误
后来在网上查了下就这样处理了下
select @FCheckerID=FCheckerID from inserted
select @DELETED_FCheckerID=FCheckerID from deleted
if (isnull(@FCheckerID,0) = 0 AND @DELETED_FCheckerID <>0) begin ---反审核操作
但是这样可以触发反审核操作 审核的又无法触发了 求解 给怎么改这个触发器
源码如下
if (object_id('JS_ICStockBill', 'tr') is not null)
drop trigger [JS_ICStockBill]
go
create trigger [JS_ICStockBill]
on ICStockBill
after update --插入触发
--FOR UPDATE
as
SET NOCOUNT ON
declare @FCheckerID varchar(100),@DELETED_FCheckerID varchar(100) --int
declare @GFDID int declare @GF varchar(100)
select @GFDID=Fid from t_DYSealNumber where isnull(FCheckerID,0) <= 0 --获取有效的关封单的关封ID
----截止重量=截止重量-已出库重量
if(isnull(@GFDID,0)<>0 )begin
declare @PresentOutWeight decimal(28,5),@FinalCustomer nvarchar(100),@FBillNo varchar(100)
--当前出库重量、最终客户、单据编号
declare @AlwaysOutWeight decimal(28,5),@AlwaysEndWeight decimal(28,5)
--总已出库重量、总截止重量
declare @CustomsSealOne nvarchar(100),@FIDOne int ,@EndQtyOne decimal(28,5) --关封1,截止重量1
declare @CustomsSealTwo nvarchar(100),@FIDTwo int ,@EndQtyTwe decimal(28,5) ---关封2 ,截止重量2
declare @CustomsSealThree nvarchar(100),@FIDThree int ,@EndQtyThree decimal(28,5) --关封3 ,截止重量3
------------------------------给当前出库重量、最终客户、单据编号赋值---------------------------
select @PresentOutWeight=FHeadSelfB0160 ,@FinalCustomer=FHeadSelfB0149 ,@FBillNo=FBillNo,@FCheckerID=FCheckerID
from inserted
select @DELETED_FCheckerID=FCheckerID from deleted
-------------------------------------审核操作-----------------------------------------
--if (isnull(@FCheckerID,0) <> 0 and (select FTranType from inserted)=21 and isnull(@GF,'0')='0' ) begin
if (isnull(@FCheckerID,0) <> 0 AND @DELETED_FCheckerID =0) begin
-----------------------------给总已出库数量、总截止重量赋值-------------------------
select @AlwaysOutWeight=SUM(FOutgoingQuantity), @AlwaysEndWeight=sum(FCutoffNumber)
from t_DYSealNumberEntry where FAvailabilityDate<=GETDATE() and FCheckBox=1 and FBase=@FinalCustomer
-------------------------------获取前最早的三个有效的关封号------------------------------------
Select IDENTITY(int,1,1) as Nid, FSealNumber,FOutgoingQuantity,FCutoffNumber,FAvailabilityDate,FID
into #1
from t_DYSealNumberEntry where FAvailabilityDate<=GETDATE() and FCheckBox=1 and FBase=@FinalCustomer
order BY FEntryID
--------------------------------------给各个关封号、重量赋值-----------------------------------------
select @CustomsSealOne=FSealNumber,@FIDOne=FID,@EndQtyOne=FCutoffNumber-FOutgoingQuantity from #1 where Nid=1
select @CustomsSealTwo=FSealNumber,@FIDTwo=FID,@EndQtyTwe=FCutoffNumber-FOutgoingQuantity from #1 where Nid=2
select @CustomsSealThree=FSealNumber,@FIDThree=FID,@EndQtyThree=FCutoffNumber-FOutgoingQuantity from #1 where Nid=3
-----------------------------------------截止功能----总截止重量减去当前出库重量减去已出库数量是否小于0
if (@AlwaysEndWeight-@PresentOutWeight-@AlwaysOutWeight<0) begin
RAISERROR('===当前出库重量已超出关封数量，单据审核未成功，请联系报关人员维护关封管控表===',18,18)
ROLLBACK TRANSACTION
end
-----------------------------------------判定操作关封一---------------------------------------------
if (@EndQtyOne-@PresentOutWeight>0) begin
----更新关封号一
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@PresentOutWeight
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@PresentOutWeight
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=1) AND FID=@FIDOne
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=1)
----更新出库单表头字段
update ICStockBill set FHeadSelfB0173=@CustomsSealOne where FTranType=21 and FBillNo=@FBillNo
end
-----------------------------------------判定操作关封一---------------------------------------------
if (@EndQtyOne-@PresentOutWeight=0) begin
----更新关封号一
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@PresentOutWeight ,FCheckBox=0
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@PresentOutWeight
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=1) AND FID=@FIDOne
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=1)
----更新出库单表头字段
update ICStockBill set FHeadSelfB0173=@CustomsSealOne where FTranType=21 and FBillNo=@FBillNo
end
-----------------------------------------判定操作关封二---------------------------------------------
if (@EndQtyOne-@PresentOutWeight<0) and (@EndQtyOne+@EndQtyTwe-@PresentOutWeight>0) begin
----更新关封号一
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@EndQtyOne,FCheckBox=0
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@EndQtyOne
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=1) AND FID=@FIDOne
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=1)
----更新关封号二
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@PresentOutWeight-@EndQtyOne
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@PresentOutWeight+@EndQtyOne
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=2) AND FID=@FIDTwo
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=2)
----更新出库单表头字段
update ICStockBill set FHeadSelfB0173=@CustomsSealOne+' '+@CustomsSealTwo ,
FHeadSelfB0174=@EndQtyOne,FHeadSelfB0175=@EndQtyTwe
where FTranType=21 and FBillNo=@FBillNo
end
-----------------------------------------判定操作关封二---------------------------------------------
if (@EndQtyOne-@PresentOutWeight<0) and (@EndQtyOne+@EndQtyTwe-@PresentOutWeight=0) begin
----更新关封号一
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@EndQtyOne,FCheckBox=0
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@EndQtyOne
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=1) AND FID=@FIDOne
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=1)
----更新关封号二
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@EndQtyTwe,FCheckBox=0
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@EndQtyTwe
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=2) AND FID=@FIDTwo
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=2)
----更新出库单表头字段
update ICStockBill set FHeadSelfB0173=@CustomsSealOne+' '+@CustomsSealTwo ,
FHeadSelfB0174=@EndQtyOne,FHeadSelfB0175=@EndQtyTwe
where FTranType=21 and FBillNo=@FBillNo
end
-----------------------------------------判定操作关封三---------------------------------------------
if (@EndQtyOne-@PresentOutWeight<0) and (@EndQtyOne+@EndQtyTwe-@PresentOutWeight<0)
and(@EndQtyOne+@EndQtyTwe+@EndQtyThree-@PresentOutWeight>0) begin
----更新关封号一
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@EndQtyOne,FCheckBox=0
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@EndQtyOne
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=1) AND FID=@FIDOne
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=1)
----更新关封号二
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@EndQtyTwe,FCheckBox=0
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@EndQtyTwe
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=2) AND FID=@FIDTwo
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=2)
----更新关封号三
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@PresentOutWeight-@EndQtyOne-@EndQtyTwe
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@PresentOutWeight+@EndQtyOne+@EndQtyTwe
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=3) AND FID=@FIDThree
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=3)
----更新出库单表头字段
update ICStockBill set FHeadSelfB0173=@CustomsSealOne+' '+@CustomsSealTwo+' '+@CustomsSealThree,
FHeadSelfB0174=@EndQtyOne,FHeadSelfB0175=@EndQtyTwe
where FTranType=21 and FBillNo=@FBillNo
end
-----------------------------------------判定操作关封三---------------------------------------------
if (@EndQtyOne-@PresentOutWeight<0) and (@EndQtyOne+@EndQtyTwe-@PresentOutWeight<0)
and(@EndQtyOne+@EndQtyTwe+@EndQtyThree-@PresentOutWeight=0) begin
----更新关封号一
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@EndQtyOne,FCheckBox=0
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@EndQtyOne
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=1) AND FID=@FIDOne
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=1)
----更新关封号二
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@EndQtyTwe,FCheckBox=0
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@EndQtyTwe
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=2) AND FID=@FIDTwo
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=2)
----更新关封号三
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity+@EndQtyThree,FCheckBox=0
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity-@EndQtyThree
where FAvailabilityDate =(select FAvailabilityDate from #1 where Nid=3) AND FID=@FIDThree
and FBase=@FinalCustomer and FSealNumber=(select FSealNumber from #1 where Nid=3)
----更新出库单表头字段
update ICStockBill set FHeadSelfB0173=@CustomsSealOne+' '+@CustomsSealTwo+' '+@CustomsSealThree,
FHeadSelfB0174=@EndQtyOne,FHeadSelfB0175=@EndQtyTwe
where FTranType=21 and FBillNo=@FBillNo
end
-----------------------------------------判定操作关封四---------------------------------------------
if (@EndQtyOne-@PresentOutWeight<0) and (@EndQtyOne+@EndQtyTwe-@PresentOutWeight<0)
and(@EndQtyOne+@EndQtyTwe+@EndQtyThree-@PresentOutWeight<0) begin
RAISERROR('===出库重量大于三次有效关封记录，审核操作已取消，请联系报关人员调整关封表===',18,18)
ROLLBACK TRANSACTION
end
end
------------------------------------------------反审核操作--------------------------------------------
-- declare @GFYXX1222 Varchar(12)
--SELECT @GFYXX1222=COUNT(1) from ICStockBill where FTranType=21 and FBillNo=@FBillNo and FStatus=0
--RAISERROR(@GFYXX1222,18,18)
-- ROLLBACK TRANSACTION
--if (isnull(@FCheckerID,0) = 0 and (select FTranType from inserted)=21 and isnull(@GF,'0')<>'0' ) begin
--if (isnull(@FCheckerID,0) = 0 AND @DELETED_FCheckerID <>0) begin
if exists(select 1 from deleted where isnull(fcheckerid,0)>0 )BEGIN
-----------------------定义变量获取关封号、关封号码有效性、出库重量，关封号占用量------------------------------
declare @GFYXX1 int declare @GFYXX2 int
declare @GFYXX3 int declare @GFZYL1 int declare @GFZYL2 int
SELECT
@CustomsSealOne=PARSENAME(REPLACE(FHeadSelfB0173,' ','.'),1),
@CustomsSealTwo=PARSENAME(REPLACE(FHeadSelfB0173,' ','.'),2),
@CustomsSealThree=PARSENAME(REPLACE(FHeadSelfB0173,' ','.'),3),
@GFZYL1=FHeadSelfB0174,@GFZYL2=FHeadSelfB0175
FROM inserted
---------获取不同关封号的有效性
select @GFYXX1=FCheckBox from t_DYSealNumberEntry where FSealNumber=@CustomsSealOne and FBase=@FinalCustomer
select @GFYXX2=FCheckBox from t_DYSealNumberEntry where FSealNumber=@CustomsSealTwo and FBase=@FinalCustomer
select @GFYXX3=FCheckBox from t_DYSealNumberEntry where FSealNumber=@CustomsSealThree and FBase=@FinalCustomer
--------------------------------------三个关封号------------------------------------------------------
if(ISNULL(@CustomsSealOne,'0')<>'0' and ISNULL(@CustomsSealTwo,'0')<>'0' and ISNULL(@CustomsSealThree,'0')<>'0') begin
if(@GFYXX1=1 and @GFYXX2=1 and @GFYXX3=1 )begin
--~~~~更新关封表关封号三已出库字段
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity-@PresentOutWeight+isnull(@GFZYL2,0)+isnull(@GFZYL1,0)
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity+@PresentOutWeight-isnull(@GFZYL2,0)-isnull(@GFZYL1,0)
where FBase=@FinalCustomer and FSealNumber=@CustomsSealOne
--~~~~更新关封表关封号二已出库字段
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity-isnull(@GFZYL2,0)
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity+isnull(@GFZYL2,0)
where FBase=@FinalCustomer and FSealNumber=@CustomsSealTwo
--~~~~更新关封表关封号一已出库字段
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity-isnull(@GFZYL1,0)
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity+isnull(@GFZYL1,0)
where FBase=@FinalCustomer and FSealNumber=@CustomsSealThree
--~~~~更新出库单表头关封号字段为空
--update ICStockBill set FHeadSelfB0173=null,FHeadSelfB0174=0,FHeadSelfB0175=0
-- where FTranType=21 and FBillNo=@FBillNo
end
if(@GFYXX1=0 or @GFYXX2=0 or @GFYXX3=0 )begin
RAISERROR('===当前单据关封号存在已经失效的关封号，请联系报关人员确认===',18,18)
ROLLBACK TRANSACTION
end
end
---------------------------------------两个关封号----------------------------------------------------
if(ISNULL(@CustomsSealOne,'0')<>'0' and ISNULL(@CustomsSealTwo,'0')<>'0' and ISNULL(@CustomsSealThree,'0')='0') begin
if(@GFYXX1=1 and @GFYXX2=1)begin
--~~~~更新关封表关封号一已出库字段
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity-isnull(@GFZYL1,0)
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity+isnull(@GFZYL1,0)
where FBase=@FinalCustomer and FSealNumber=@CustomsSealTwo
--~~~~更新关封表关封号二已出库字段
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity-isnull(@GFZYL2,0)
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity+isnull(@GFZYL2,0)
where FBase=@FinalCustomer and FSealNumber=@CustomsSealOne
--~~~~更新出库单表头关封号字段为空
--update ICStockBill set FHeadSelfB0173=null,FHeadSelfB0174=0,FHeadSelfB0175=0
-- where FTranType=21 and FBillNo=@FBillNo
end
if(@GFYXX1=0 or @GFYXX2=0)begin
RAISERROR('===当前单据关封号存在已经失效的关封号，请联系报关人员确认===',18,18)
ROLLBACK TRANSACTION
end
end
----------------------------------------一个关封号--------------------------------------------------
if(ISNULL(@CustomsSealOne,'0')<>'0' and ISNULL(@CustomsSealTwo,'0')='0' and ISNULL(@CustomsSealThree,'0')='0') begin
--~~~~更新关封表已出库字段
if(@GFYXX1=1)begin
update t_DYSealNumberEntry set FOutgoingQuantity=FOutgoingQuantity-@PresentOutWeight
,FReminderQuantity=FCutoffNumber-FOutgoingQuantity+@PresentOutWeight
where FBase=@FinalCustomer and FSealNumber=@CustomsSealOne
--~~~~更新出库单表头关封号字段为空
--update ICStockBill set FHeadSelfB0173=null,FHeadSelfB0174=0,FHeadSelfB0175=0
-- where FTranType=21 and FBillNo=@FBillNo
end
if(@GFYXX1=0)begin
RAISERROR('===当前单据关封号已经失效，请联系报关人员确认===',18,18)
ROLLBACK TRANSACTION
end
end
end
end
推荐阅读