purchasemaster_insert.sql

通用进销存-visual basic编写

CREATE TRIGGER PurchaseMaster_Insert
ON PurchaseMaster
FOR INSERT
AS
IF @@ROWCOUNT = 0 RETURN
/*定义变量
@PurchaseID : 进货单号
@PurchaseDate : 进货日期
@PurchaseProperty : 进货属性
@SupplierID : 供应商编号
@Amount : 进货单金额
@PayDays : 付款日数
*/
DECLARE @PurchaseID Char(10)
DECLARE @PurchaseDate Datetime
DECLARE @PurchaseProperty Char(10), @SupplierID Char(10)
DECLARE @Amount Decimal(12, 0)
DECLARE @PayDays Int
/*找出 进货单号, 进货日期, 供应商编号, 进货属性, 进货单金额 */
SELECT @PurchaseID = PurchaseID, @PurchaseDate = PurchaseDate,
@SupplierID = SupplierID, @PurchaseProperty = PurchaseProperty,
@Amount = Amount
FROM inserted
WHERE PurchaseProperty IN ('1', '2')
IF @@ROWCOUNT = 0 
  BEGIN
    ROLLBACK TRANSACTION
    RAISERROR('进货单属性必须为「1」或「2」。', 16, 1)
    RETURN
  END
/*找进供应商的付款日数*/
SELECT @PayDays = PayDays 
FROM Supplier
WHERE SupplierID = @SupplierID
/*计算应收帐款与更新进货单的应收帐款与应收帐款截止日
  进货再更新最近进货日*/
IF @PurchaseProperty = '1'
  BEGIN
    /* 进货:应收帐款=进货单金额 */
    UPDATE PurchaseMaster SET AccountPayable = @Amount,
    LimitDate = (SELECT DATEADD(Day, @PayDays, @PurchaseDate))
    WHERE PurchaseID = @PurchaseID
    UPDATE Supplier SET LastPurchaseDate = @PurchaseDate
    WHERE SupplierID = @SupplierID
  END  
ELSE
  BEGIN
  /* 进货退回:应收帐款=进货单金额*-1 */
    UPDATE PurchaseMaster SET AccountPayable = @Amount * -1,
    LimitDate = (SELECT DATEADD(Day, @PayDays, @PurchaseDate))
    WHERE PurchaseID = @PurchaseID
  END