accountpayabledetail_update.sql

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

CREATE TRIGGER AccountPayableDetail_Update
ON AccountPayableDetail
FOR UPDATE
AS
/* 如果修改记录数是0就结束触发程序 */ 
IF @@ROWCOUNT = 0 RETURN
/* 定义变量 
@SupplierID : 供应商编号
@NewPurchaseID : 新的进货单号
@OldPurchaseID : 旧的进货单号
@NewBalance : 新的冲款金额
@OldBalance : 旧的冲款金额
*/
DECLARE @SupplierID Char(8)
DECLARE @NewPurchaseID Char(10), @OldPurchaseID Char(10)
DECLARE @NewBalance Decimal(12, 0), @OldBalance Decimal(12, 0)
/* 找出修改后的进货单号与供应商编号及冲款金额 */
SELECT @NewPurchaseID = I.PurchaseID, @SupplierID = M.SupplierID,
@NewBalance = I.Balance
FROM Inserted I, AccountPayableMaster M, PurchaseMaster V
WHERE I.PaymentID = M.PaymentID 
AND I.PurchaseID = V.PurchaseID
AND M.SupplierID = V.SupplierID
if @@ROWCOUNT = 0 
  BEGIN
    ROLLBACK TRANSACTION
    RAISERROR('进货单错误。', 16, 	1)
    RETURN
  END
/* 找出修改前的进货单号与冲款金额 */
SELECT @OldPurchaseID = PurchaseID, @OldBalance = Balance
FROM deleted
/*更新进货单的已付帐款*/
/*先减去原来的*/
UPDATE PurchaseMaster SET Paid = Paid - @OldBalance
WHERE PurchaseID = @OldPurchaseID
/*再加上新的*/
UPDATE PurchaseMaster SET Paid = Paid + @NewBalance
WHERE PurchaseID = @NewPurchaseID