deliverymaster_update.sql

来自「通用进销存-visual basic编写」· SQL 代码 · 共 41 行

CREATE TRIGGER DeliveryMaster_Update
ON DeliveryMaster
FOR UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
/*定义变量
@DeliveryID : 出货单号
@DeliveryProperty : 出货属性
@NewAmount : 新的出货单金额
@OldAmount : 旧的出货单金额
@CustomerID : 客户编号
*/
DECLARE @DeliveryID Char(10), @DeliveryProperty Char(10)
DECLARE @NewAmount Decimal(12, 0), @OldAmount Decimal(12, 0)
DECLARE @CustomerID Char(8)
/*找出修改后的 出货单号, 出货单金额, 出货属性, 客户编号  */
SELECT @DeliveryID = DeliveryID, @NewAmount = Amount,
@DeliveryProperty = DeliveryProperty, @CustomerID = CustomerID
FROM inserted
/*找出修改前的 出货单金额 */
SELECT @OldAmount = Amount
FROM deleted
/*更新出货单的应收帐款与信用余额*/
IF @DeliveryProperty = '1'
  BEGIN
    /* 出货:应收帐款=出货单金额 */
    UPDATE DeliveryMaster SET AccountReceivable = @NewAmount
    WHERE DeliveryID = @DeliveryID
    /* 出货:信用余额=原信用余额+旧的出货金额-新的出货金额*/
    UPDATE Customer SET CreditBalance = CreditBalance + @NewAmount - @OldAmount
    WHERE CustomerID = @CustomerID
  END  
ELSE
  BEGIN
    /* 出货退回:应收帐款=出货单金额*-1 */
    UPDATE DeliveryMaster SET AccountReceivable = @NewAmount * -1
    WHERE DeliveryID = @DeliveryID
    /* 出货退回:信用余额=原信用余额-旧的出货金额+新的出货金额*/
    UPDATE Customer SET CreditBalance = CreditBalance - @NewAmount + @OldAmount
    WHERE CustomerID = @CustomerID
  END