审核成功触发,是一个比较典型的场景。需要用到update触发器,跟踪到审核状态的变化。
引用的源码《采购检验单审核后反写收料通知单》,其中采购检验单是BOS自定义单据。
if (object_id('bobang_tgr_check_update', 'TR') is not null)
drop trigger bobang_tgr_check_update
go
create trigger cl_tgr_check_update
on bobang_bos_check
after update
as
declare @FID int
declare @FMultiCheckStatus varchar(100)
declare @FAuxQtyPass float --合格数量
declare @FQtyPass float --基本单位合格数量
declare @FAuxNotPassQty float --不合格数量
declare @FNotPassQty float --基本单位不合格数量
declare @FAuxConPassQty float --让步接收数量
declare @FConPassQty float --基本单位让步接收数量
declare @FID_Src bigint --源单ID
declare @FEntryID_SRC bigint --源单FEntryID
select @FID=FID,@FMultiCheckStatus=FMultiCheckStatus
from inserted
--审核时
if update(FMultiCheckStatus) and @FMultiCheckStatus=16
begin
declare mycursor cursor for
select FAuxPassQty,FPassQty,FAuxUnPassQty,FUnPassQty,FAuxConcessQty,FConcessQty,FID_Src,FEntryID_Src
from bobang_bos_checkentry where FID=@FID
open mycursor
fetch next from mycursor
into @FAuxQtyPass,@FQtyPass,@FAuxNotPassQty,@FNotPassQty,@FAuxConPassQty,@FConPassQty,@FID_Src,@FEntryID_SRC
while (@@fetch_status=0)
begin
update t1
set t1.FAuxQtyPass=t1.FAuxQtyPass+isnull(@FAuxQtyPass,0),
t1.FQtyPass=t1.FQtyPass+isnull(@FQtyPass,0),
t1.FAuxNotPassQty=t1.FAuxNotPassQty+isnull(@FAuxNotPassQty,0),
t1.FNotPassQty=t1.FNotPassQty+isnull(@FNotPassQty,0),
t1.FAuxConPassQty=t1.FAuxConPassQty+isnull(@FAuxConPassQty,0),
t1.FConPassQty=t1.FConPassQty+isnull(@FConPassQty,0)
from POInStockEntry t1
left join POInStock t2 on t1.FInterID=t2.FInterID
where t1.FInterID=@FID_Src and t1.FEntryID=@FEntryID_SRC
and t2.FTranType=72
fetch next from mycursor
into @FAuxQtyPass,@FQtyPass,@FAuxNotPassQty,@FNotPassQty,@FAuxConPassQty,@FConPassQty,@FID_Src,@FEntryID_SRC
end
close mycursor
DEALLOCATE mycursor
end
--驳回前检查
declare @isTuiLiao int
declare @isRuKu int
if update(FMultiCheckStatus) and @FMultiCheckStatus=4
begin
declare mycursor cursor for
select FAuxPassQty,FPassQty,FAuxUnPassQty,FUnPassQty,FAuxConcessQty,FConcessQty,FID_Src,FEntryID_Src
from bobang_bos_checkentry where FID=@FID
open mycursor
fetch next from mycursor
into @FAuxQtyPass,@FQtyPass,@FAuxNotPassQty,@FNotPassQty,@FAuxConPassQty,@FConPassQty,@FID_Src,@FEntryID_SRC
while (@@fetch_status=0)
begin
select @isTuiLiao=COUNT(*) from POInStockEntry
where FSourceTrantype=72 and FSourceInterId=@FID_Src and FSourceEntryID=@FEntryID_SRC
if @isTuiLiao>0
begin
raiserror ('已下推退料通知单,不能反审核!',16,1)
rollback tran
end
select @isRuKu=COUNT(*) from ICStockBillEntry
where FSourceTrantype=72 and FSourceInterId=@FID_Src and FSourceEntryID=@FEntryID_SRC
if @isTuiLiao>0
begin
raiserror ('已下推外购入库单,不能反审核!',16,1)
rollback tran
end
fetch next from mycursor
into @FAuxQtyPass,@FQtyPass,@FAuxNotPassQty,@FNotPassQty,@FAuxConPassQty,@FConPassQty,@FID_Src,@FEntryID_SRC
end
close mycursor
DEALLOCATE mycursor
end
--驳回初始时
if update(FMultiCheckStatus) and @FMultiCheckStatus=2
begin
declare mycursor cursor for
select FAuxPassQty,FPassQty,FAuxUnPassQty,FUnPassQty,FAuxConcessQty,FConcessQty,FID_Src,FEntryID_Src
from bobang_bos_checkentry where FID=@FID
open mycursor
fetch next from mycursor
into @FAuxQtyPass,@FQtyPass,@FAuxNotPassQty,@FNotPassQty,@FAuxConPassQty,@FConPassQty,@FID_Src,@FEntryID_SRC
while (@@fetch_status=0)
begin
update t1
set t1.FAuxQtyPass=t1.FAuxQtyPass-isnull(@FAuxQtyPass,0),
t1.FQtyPass=t1.FQtyPass-isnull(@FQtyPass,0),
t1.FAuxNotPassQty=t1.FAuxNotPassQty-isnull(@FAuxNotPassQty,0),
t1.FNotPassQty=t1.FNotPassQty-isnull(@FNotPassQty,0),
t1.FAuxConPassQty=t1.FAuxConPassQty-isnull(@FAuxConPassQty,0),
t1.FConPassQty=t1.FConPassQty-isnull(@FConPassQty,0)
from POInStockEntry t1
left join POInStock t2 on t1.FInterID=t2.FInterID
where t1.FInterID=@FID_Src and t1.FEntryID=@FEntryID_SRC
and t2.FTranType=72
fetch next from mycursor
into @FAuxQtyPass,@FQtyPass,@FAuxNotPassQty,@FNotPassQty,@FAuxConPassQty,@FConPassQty,@FID_Src,@FEntryID_SRC
end
close mycursor
DEALLOCATE mycursor
end