purchasedetail_insert.sql

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

CREATE TRIGGER PurchaseDetail_Insert
ON PurchaseDetail
FOR INSERT
AS
/* 如果新增记录数是0就结束触发程序 */ 
IF @@ROWCOUNT = 0 RETURN
/* 定义变量 
@PurchaseID : 进货单号
@PurchaseProperty : 进货单属性
@PurchaseDate : 进货日期
@ProductID : 商品编号
@Quantity : 数量
*/
DECLARE @PurchaseID Char(10), @PurchaseProperty Char(1), @ProductID Char(10)
DECLARE @Quantity Decimal(12, 0)
DECLARE @PurchaseDate Datetime
/* 找出新增的进货单号给DeliveryID, 商品编号给ProductID, 数量给Quantity */
SELECT @PurchaseID = PurchaseID, @ProductID = ProductID, 
@Quantity = PurchaseQuantity
FROM inserted
/*找出这张进货单的进货单属性, 进货日期*/
SELECT @PurchaseProperty = PurchaseProperty, @PurchaseDate = PurchaseDate 
FROM PurchaseMaster 
WHERE PurchaseID = @PurchaseID
/*更新库存量*/
IF @PurchaseProperty = '1' 
  /*进货:库存量增加, 更新最近进货日期*/
  BEGIN
    UPDATE Product SET Quantity = Quantity + @Quantity,
		       LastPurchaseDate = @PurchaseDate 	                       
    WHERE ProductID = @ProductID
  END
ELSE	
  /*进货退出:库存量减少, 但不去异动最近进货日期*/
  BEGIN
    UPDATE Product SET Quantity = Quantity - @Quantity
    WHERE ProductID = @ProductID
  END