【SQL开源代码栏目提醒】:网学会员,鉴于大家对SQL开源代码十分关注,论文会员在此为大家搜集整理了“DeliveryMaster_Update.sql”一文,供大家参考学习!
CREATE TRIGGER DeliveryMaster_Update
ON DeliveryMaster
FOR UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
DECLARE @DeliveryID Char(10), @DeliveryProperty Char(10)
DECLARE @NewAmount Decimal(12, 0), @OldAmount Decimal(12, 0)
DECLARE @CustomerID Char(8)
SELECT @DeliveryID = DeliveryID, @NewAmount = Amount,
@DeliveryProperty = DeliveryProperty, @CustomerID = CustomerID
FROM inserted
IF EXISTS(SELECT SourceID FROM VoucMaster
WHERE SourceID = @DeliveryID) AND (UPDATE(Amount))
BEGIN
RAISERROR('本张出货单已转入支票, 不可修改。', 16, 1)
ROLLBACK
RETURN
END
SELECT @OldAmount = Amount
FROM deleted
IF @DeliveryProperty = '1'
BEGIN
UPDATE DeliveryMaster SET AccountReceivable = @NewAmount
WHERE DeliveryID = @DeliveryID
UPDATE Customer SET CreditBalance = CreditBalance + @NewAmount - @OldAmount
WHERE CustomerID = @CustomerID
END
ELSE
BEGIN
UPDATE DeliveryMaster SET AccountReceivable = @NewAmount * -1
WHERE DeliveryID = @DeliveryID
UPDATE Customer SET CreditBalance = CreditBalance - @NewAmount + @OldAmount
WHERE CustomerID = @CustomerID
END