accountreceivabledetail_insert.sql

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

CREATE TRIGGER AccountReceivableDetail_Insert
ON AccountReceivableDetail
FOR INSERT 
AS
/* 如果修改记录数是0就结束触发程序 */ 
IF @@ROWCOUNT = 0 RETURN
/* 定义变量 
@DeliveryID : 出货单号
@CustomerID : 客户编号
@Balance : 冲款金额
*/
DECLARE @DeliveryID Char(10)
DECLARE @CustomerID Char(8)
DECLARE @Balance Decimal(12, 0)
/* 找出新增的出货单号与客户编号及冲款金额 */
SELECT @DeliveryID = I.DeliveryID, @CustomerID = M.CustomerID,
@Balance = 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, 0)
    RETURN
  END
/*更新出货单的已收帐款*/
UPDATE DeliveryMaster SET Received = Received + @Balance
WHERE DeliveryID = @DeliveryID
/*更新客户的信用余额*/
UPDATE Customer SET CreditBalance = CreditBalance + @Balance
WHERE CustomerID = @CustomerID