accountreceivabledetail_update.sql

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

CREATE TRIGGER AccountReceivableDetail_Update
ON AccountReceivableDetail
FOR UPDATE
AS
/* 如果修改记录数是0就结束触发程序 */ 
IF @@ROWCOUNT = 0 RETURN
/* 定义变量 
@CustomerID : 客户编号
@NewDeliveryID : 新的出货单号
@OldDeliveryID : 旧的出货单号
@NewBalance : 新的冲款金额
@OldBalance : 旧的冲款金额
*/
DECLARE @CustomerID Char(8)
DECLARE @NewDeliveryID Char(10), @OldDeliveryID Char(10)
DECLARE @NewBalance Decimal(12, 0), @OldBalance Decimal(12, 0)
/* 找出修改后的出货单号与客户编号及冲款金额 */
SELECT @NewDeliveryID = I.DeliveryID, @CustomerID = M.CustomerID,
@NewBalance = I.Balance
FROM Inserted I, AccountReceivableMaster M, DeliveryMaster V
WHERE I.ReceiveID = M.ReceiveID 
AND I.DeliveryID = V.DeliveryID
AND M.CustomerID = V.CustomerID
if @@ROWCOUNT = 0 
  BEGIN
    ROLLBACK TRANSACTION
    RAISERROR('出货单错误。', 16, 	1)
    RETURN
  END
/* 找出修改前的出货单号与冲款金额 */
SELECT @OldDeliveryID = DeliveryID, @OldBalance = Balance
FROM deleted 
/*更新出货单的已收帐款*/
/*先减去原来的*/
UPDATE DeliveryMaster SET Received = Received - @OldBalance
WHERE DeliveryID = @OldDeliveryID
/*再加上新的*/
UPDATE DeliveryMaster SET Received = Received + @NewBalance
WHERE DeliveryID = @NewDeliveryID
/*更新客户的信用余额*/
UPDATE Customer SET CreditBalance = CreditBalance + @NewBalance - @OldBalance
WHERE CustomerID = @CustomerID