accountreceivabledetail_delete.sql

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

CREATE TRIGGER AccountReceivableDetail_Delete
ON AccountReceivableDetail
FOR DELETE
AS
/* 如果修改记录数是0就结束触发程序 */ 
IF @@ROWCOUNT = 0 RETURN
/* 定义变量 
@DeliveryID : 出货单号
@CustomerID : 客户编号
@Balance : 冲款金额
*/
DECLARE @DeliveryID Char(10)
DECLARE @CustomerID Char(8)
DECLARE @Balance Decimal(12, 0)
/* 找出删除的出货单号与客户编号及冲款金额 */
SELECT @DeliveryID = D.DeliveryID, @CustomerID = M.CustomerID,
@Balance = D.Balance
FROM deleted D, AccountReceivableMaster M
WHERE D.ReceiveID = M.ReceiveID 
/*更新出货单的已收帐款*/
UPDATE DeliveryMaster SET Received = Received - @Balance
WHERE DeliveryID = @DeliveryID
/*更新客户的信用余额*/
UPDATE Customer SET CreditBalance = CreditBalance - @Balance
WHERE CustomerID = @CustomerID