deliverydetail_insert.sql

来自「用vb.net写的基于c/s结构的进销存程序设计源代码」· SQL 代码 · 共 41 行

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