deliverymaster_insert.sql

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

CREATE TRIGGER DeliveryMaster_Insert
ON DeliveryMaster
FOR INSERT
AS
/*如果变动记录是0,就不执行*/
IF @@ROWCOUNT = 0 RETURN
/*定义变量
@DeliveryID : 出货单号
@DeliveryDate : 出货日期
@DeliveryProperty : 出货属性
@CustomerID : 客户编号
@Amount : 出货单金额
@PayDays : 付款日数
*/
DECLARE @DeliveryID Char(10)
DECLARE @DeliveryDate Datetime
DECLARE @DeliveryProperty Char(10), @CustomerID Char(10)
DECLARE @Amount Decimal(12, 0)
DECLARE @PayDays Int
/*找出 出货单号, 出货日期, 客户编号, 出货属性, 出货单金额 */
SELECT @DeliveryID = DeliveryID, @DeliveryDate = DeliveryDate,
@CustomerID = CustomerID, @DeliveryProperty = DeliveryProperty,
@Amount = Amount
FROM inserted
/*找出客户的付款日数*/
SELECT @PayDays = PayDays 
FROM Customer
WHERE CustomerID = @CustomerID
/*计算应收帐款与更新出货单的应收帐款与应收帐款截止日
及客户的信用额度余额与最近出货日(仅出货)*/
IF @DeliveryProperty = '1'
  BEGIN
    /* 出货:应收帐款=出货单金额 */
    UPDATE DeliveryMaster SET AccountReceivable = @Amount,
    LimitDate = (SELECT DATEADD(Day, @PayDays, @DeliveryDate))
    WHERE DeliveryID = @DeliveryID
    /* 出货:信用余额减少 */
    UPDATE Customer SET CreditBalance = CreditBalance - @Amount,
    LastDeliveryDate = @DeliveryDate
    WHERE CustomerID = @CustomerID
  END  
ELSE
  BEGIN
    /* 出货退回:应收帐款=出货单金额*-1 */
    UPDATE DeliveryMaster SET AccountReceivable = @Amount * -1,
    LimitDate = (SELECT DATEADD(Day, @PayDays, @DeliveryDate))
    WHERE DeliveryID = @DeliveryID
    /* 出货退回:信用余额增加 */
    UPDATE Customer SET CreditBalance = CreditBalance + @Amount
    WHERE CustomerID = @CustomerID
  END