mssql触发器更新

 

create trigger OA_Change_Stock
on OA_GoodsDetail for UPDATE
as
begin
if(UPDATE (GoodsCount))
begin
 DECLARE @before_Record numeric(12,2)--修改之前的
 DECLARE @after_Record numeric(12,2)--修改之后���
 DECLARE @ItemID varchar(10)--物资id
 DECLARE @DonateID varchar(50)--来源id
 DECLARE @result numeric(12,2)--两个值的差 @after_Record-@before_Record

 select @after_Record = GoodsCount from inserted --从更新后的副本表(临时表)里面 获得要修改后的状态
 select @ItemID = ItemID,@DonateID=DonateID,@before_Record = GoodsCount from deleted --从之前删掉的临时表里面获取原来的值
 if @before_Record is null
 begin
  set @before_Record=0
 end;
 if @after_Record is null
 begin
  set @after_Record=0
 end;
 set @result = @after_Record - @before_Record
 update OA_GoodStock set GoodsCount = GoodsCount + @result where ItemID=@ItemID and DonateID=@DonateID
end;
if(UPDATE (WareHouseID))
begin
 DECLARE @WH_ID int--修改之后
 DECLARE @ItemID1 varchar(10)--物资id
 DECLARE @DonateID1 varchar(50)--来源id
 --select @WH_ID = WareHouseID from inserted --从更新后的副本表(临时表)里面 获得要修改后的状态
 select @ItemID1 = ItemID,@DonateID1=DonateID,@WH_ID = WareHouseID from inserted --从更新后的副本表(临时表)里面 获得要修改后的状态
 update OA_GoodStock set WareHouseID = @WH_ID where ItemID=@ItemID1 and DonateID=@DonateID1
end;
end;