CREATE TRIGGER T_INSERTITEM ON dbo.STOCK_IN_LN -------/进仓 FOR INSERT AS begin declare @WAY_NO VARCHAR(12), @ITEM_CODE NVARCHAR(20), @UNIT NVARCHAR(6), @LOCATION NVARCHAR(10), @QTY float, @IS_IN INT
declare YB02 CURSOR /*声明游标*/ for select WAY_NO,ITEM_CODE,UNIT,LOCATION,IS_IN,QTY from INSERTED open YB02 fetch next from YB02 into @WAY_NO,@ITEM_CODE,@UNIT, @LOCATION ,@IS_IN ,@QTY /*读取第一条数据*/ while(@@fetch_status=0) /*表示成功执行fetch语句*/
IF @QTY =NULL
SET @QTY=0
IF @IS_IN=1 --- 进仓 begin IF (SELECT count(*) from STOCK_INFO WHERE ( @ITEM_CODE=ITEM_CODE AND @UNIT=UNIT AND @LOCATION =LOCATION) )=0 insert into STOCK_INFO ( ITEM_CODE, UNIT, LOCATION,QTY_IN) SELECT @ITEM_CODE, @UNIT,@LOCATION,@QTY ELSE UPDATE STOCK_INFO SET QTY_IN=QTY_IN+@QTY WHERE @ITEM_CODE=ITEM_CODE AND @UNIT=UNIT AND @LOCATION =LOCATION
fetch next from YB02 into @WAY_NO,@ITEM_CODE,@UNIT, @LOCATION ,@IS_IN ,@QTY
end
close YB02
deallocate YB02
END
----------------------------------------------
不是高手,但要努力成为高手!
不要用游标,直接更新或者插入。供参考。 --先更新 UPDATE STOCK_INFO SET QTY_IN=a.QTY_IN+b.QTY from STOCK_INFO a, inserted b WHERE a.ITEM_CODE=b.ITEM_CODE AND a.UNIT=b.UNIT AND a.LOCATION =b.LOCATION --再插入 insert into STOCK_INFO ( ITEM_CODE, UNIT, LOCATION,QTY_IN) select a.ITEM_CODE,a.UNIT,a.LOCATION,a.QTY from INSERTED a left join STOCK_INFO b on a.ITEM_CODE=b.ITEM_CODE AND a.UNIT=b.UNIT AND a.LOCATION =b.LOCATION where b.item_code is null
----------------------------------------------
Delphi爱好者。