purchasedetail_update.sql

用vb.net写的基于c/s结构的进销存程序设计源代码

CREATE TRIGGER PurchaseDetail_Update
ON PurchaseDetail
FOR UPDATE 
AS
/* 如果修改记录数是0就结束触发程序 */ 
IF @@ROWCOUNT = 0 RETURN
/* 定义变量 
@PurchaseID : 进货单号
@PurchaseProperty : 进货单属性
@NewProductID : 新的商品编号
@OldProductID : 旧的商品编号
@NewQuantity : 新的数量
@OldQuantity : 旧的数量
*/
DECLARE @PurchaseID Char(10), @PurchaseProperty Char(1)
DECLARE @NewProductID Char(10), @OldProductID Char(10)
DECLARE @NewQuantity Decimal(12, 0), @OldQuantity Decimal(12, 0)
/* 找出修改的进货单号给PurchaseID, 修改后的商品编号给NewProductID, 修改后的数量给 NewQuantity */
SELECT @PurchaseID = PurchaseID, @NewProductID = ProductID, 
@NewQuantity = PurchaseQuantity
FROM inserted
/* 找出修改前的商品编号给NewProductID, 修改前的数量给 NewQuantity */
SELECT @OldProductID = ProductID, @OldQuantity = PurchaseQuantity
FROM deleted
/*找进这张进货单的进货属性*/
SELECT @PurchaseProperty = PurchaseProperty 
FROM PurchaseMaster 
WHERE PurchaseID = @PurchaseID
/*更新库存量*/
IF @PurchaseProperty = '1' 
  /*进货*/
  BEGIN
    /*新商品库存量增加*/
    UPDATE Product SET Quantity = Quantity + @NewQuantity                       
    WHERE ProductID = @NewProductID
    /*旧商品库存量减掉*/
    UPDATE Product SET Quantity = Quantity - @OldQuantity                       
    WHERE ProductID = @OldProductID
  END
ELSE	
  /*进货退出:*/
  BEGIN
    /*新商品库存量减少*/
    UPDATE Product SET Quantity = Quantity - @NewQuantity                       
    WHERE ProductID = @NewProductID
    /*旧商品库存量加回*/
    UPDATE Product SET Quantity = Quantity + @OldQuantity                       
    WHERE ProductID = @OldProductID
  END