accountpayabledetail_insert.sql

来自「用vb.net写的基于c/s结构的进销存程序设计源代码」· SQL 代码 · 共 39 行

CREATE TRIGGER AccountPayableDetail_Insert
ON AccountPayableDetail
FOR INSERT 
AS
/* 如果修改记录数是0就结束触发程序 */ 
IF @@ROWCOUNT = 0 RETURN
/* 定义变量 
@PurchaseID : 进货单号
@SupplierID : 供应商编号
@Balance : 冲款金额
*/
DECLARE @PurchaseID Char(10)
DECLARE @SupplierID Char(8)
DECLARE @Balance Decimal(12, 0)
/* 找进新增的进货单号与供应商编号及冲款金额 */
SELECT @PurchaseID = I.PurchaseID, @SupplierID = M.SupplierID,
@Balance = 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
/*更新进货单的已付帐款*/
UPDATE PurchaseMaster SET Paid = Paid + @Balance
WHERE PurchaseID = @PurchaseID