customer_update.sql

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

CREATE TRIGGER Customer_Update
ON Customer
FOR Update
AS
/*如果变动记录是0,就不执行*/
IF @ROWCOUNT = 0 RETURN
/*定义变量
NewCreditLine : 新的信用额度
OldCreditLine : 旧的信用额度
*/
DECLARE @NewCreditLine Decimal(12, 0), @OldCreditLine Decimal(12, 0)
/*找出新的信用额度*/
SELECT @NewCreditLine = CreditLine FROM inserted 
/*找出旧的信用额度
SELECT @OldCreditLine = CreditLine FROM deleted
/*更新信用余额
新的信用余额=原信用余额+新的信用额度-旧的信用额度
*/
UPDATE Customer
SET CreditBalance = CreditBalance + @NewCreditLine- @OldCreditLine
WHERE CustomerID = (SELECT CustomerID FROM inserted)