触发插入或更新 [英] Trigger to Insert or Update

问题描述

在插入记录时在表AccountMaster上写一个插入触发器，必须在余额表中更新该特定AccCode的BalanceAmount = Sum(DebitAmount-CreditAmount)值.我还需要检查插入的AcctCode是否存在记录，如果存在，则更新BalanceAmount Else Inset新条目.

这是我的代码.这些值将插入到AccountMaster中，但不会插入到余额"表中.我该怎么办?

Write an insert trigger on table AccountMaster while inserting the records, Value for BalanceAmount = Sum(DebitAmount-CreditAmount) for that particular AccCode must be updated in the Balance table. I also need to check whether record is present for the inserted AcctCode , If present then update the BalanceAmount Else Inset the new entry.

This is my code. The values are inserted in AccountMaster but not in Balance table. What do i do?

CREATE TRIGGER tri_InsertBalance ON AccountMaster
FOR INSERT, UPDATE
AS

DECLARE @AccountCode VARCHAR(100)
SELECT @AccountCode = [AccountCode]
FROM Inserted

DECLARE @BalanceAmount NUMERIC(19,4)
SELECT @BalanceAmount = SUM(DebitAmount - CreditAmount)
FROM Inserted

BEGIN
IF EXISTS (SELECT 1 FROM AccountMaster WHERE AccountCode = @AccountCode )
	BEGIN 
	UPDATE Balances SET BalanceAmount = @BalanceAmount
	WHERE AccountCode = @AccountCode 
	END
ELSE
	BEGIN 
	INSERT INTO Balances (AccountCode, BalanceAmount)
	SELECT AccountCode, @BalanceAmount FROM Inserted
	END
END

推荐答案

此触发器将在AccountMaster表上执行INSERT/UPDATE之后执行.所以条件

Hi,

this trigger will be executed after the INSERT/UPDATE on AccountMaster table. So condition

IF EXISTS (SELECT 1 FROM AccountMaster WHERE AccountCode = @AccountCode )

返回始终为true.因此将其更改为

returns always true. So change this to

IF EXISTS (SELECT  1  FROM Balances WHERE AccountCode = @AccountCode )

所以触发是...

So the trigger is...

ALTER TRIGGER tri_InsertBalance ON AccountMaster
FOR INSERT, UPDATE
AS
 
DECLARE @AccountCode VARCHAR(100)
SELECT @AccountCode = [AccountCode]
FROM Inserted
 
DECLARE @BalanceAmount NUMERIC(19,4)
SELECT @BalanceAmount = SUM(DebitAmount - CreditAmount)
FROM Inserted


BEGIN
IF EXISTS (SELECT 1 FROM Balances WHERE AccountCode = @AccountCode )
	BEGIN 
	UPDATE Balances SET BalanceAmount = @BalanceAmount
	WHERE AccountCode = @AccountCode 
	END
ELSE
	BEGIN 
	INSERT INTO Balances (AccountCode, BalanceAmount)
	SELECT AccountCode, @BalanceAmount FROM Inserted
	END
END

希望这符合您的要求.谢谢

I hope this suits your requirement.. Thank you

您正在检查AccountMaster表，应检查余额表
将代码更改为


如果存在(从 Balances WHERE AccountCode = @AccountCode中选择1)

You are checking the AccountMaster table ,You should check Balances table
change the code to


IF EXISTS (SELECT 1 FROM Balances WHERE AccountCode = @AccountCode )

我已经解决了删除触发器的问题.

I''ve solved the same for a delete trigger.

CREATE TRIGGER tri_DeleteAccountMaster ON AccountMaster
FOR DELETE
AS

DECLARE @AccountCode VARCHAR(100)
SELECT @AccountCode = [AccountCode]
FROM Deleted

DECLARE @RowID INT
SELECT @RowID = [RowID]
FROM Deleted

DECLARE @BalanceAmount NUMERIC(19,4)
SELECT @BalanceAmount = SUM(DebitAmount - CreditAmount)
FROM AccountMaster
WHERE AccountCode = @AccountCode

BEGIN
INSERT INTO AccountMasterHistory (AccountCode, AccountDate, DebitAmount, CreditAmount, RowId)
SELECT AccountCode, AccountDate, DebitAmount, CreditAmount, RowId FROM Deleted
WHERE AccountCode = @AccountCode

UPDATE Balances SET BalanceAmount = @BalanceAmount  
WHERE AccountCode = @AccountCode AND RowID = @RowID

END

这篇关于触发插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！