CREATE TRIGGER TRIG_B_CheapAssetsOut_Insert ON dbo.B_CheapAssetsOut
FOR INSERT
AS Declare @DepId varchar(50),@RName varchar(50), @RUnit varchar(20), @Number int,@TotalSum int
Declare @mRowCount int Declare DepCheapOutInsCur CURSOR FORWARD_ONLY STATIC
FOR
Select DepId,RName,RUnit,Number From Inserted
FOR READ ONLY
Open DepCheapOutInsCur
Fetch Next From DepCheapOutInsCur INTO @DepId,@RName,@RUnit,@Number
While @@Fetch_Status = 0
begin
Select @TotalSum = TotalSum From B_CheapAssetsStorage Where DepId = @DepId and RName = @RName and RUnit = @RUnit
if @TotalSum - @Number > 0
Update B_CheapAssetsStorage Set TotalSum = TotalSum - @Number Where DepId = @DepId and RName = @RName and RUnit = @RUnit
else
Delete From B_CheapAssetsStorage Where DepId = @DepId and RName = @RName and RUnit = @RUnit
Fetch Next From DepCheapOutInsCur INTO @DepId,@RName,@RUnit,@Number
end
Close DepCheapOutInsCur
DeAllocate DepCheapOutInsCur自己看看
FOR INSERT
AS Declare @DepId varchar(50),@RName varchar(50), @RUnit varchar(20), @Number int,@TotalSum int
Declare @mRowCount int Declare DepCheapOutInsCur CURSOR FORWARD_ONLY STATIC
FOR
Select DepId,RName,RUnit,Number From Inserted
FOR READ ONLY
Open DepCheapOutInsCur
Fetch Next From DepCheapOutInsCur INTO @DepId,@RName,@RUnit,@Number
While @@Fetch_Status = 0
begin
Select @TotalSum = TotalSum From B_CheapAssetsStorage Where DepId = @DepId and RName = @RName and RUnit = @RUnit
if @TotalSum - @Number > 0
Update B_CheapAssetsStorage Set TotalSum = TotalSum - @Number Where DepId = @DepId and RName = @RName and RUnit = @RUnit
else
Delete From B_CheapAssetsStorage Where DepId = @DepId and RName = @RName and RUnit = @RUnit
Fetch Next From DepCheapOutInsCur INTO @DepId,@RName,@RUnit,@Number
end
Close DepCheapOutInsCur
DeAllocate DepCheapOutInsCur自己看看
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货