Click here to Skip to main content
15,567,221 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
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?

SQL
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
Posted
Comments
Varun Sareen 9-Oct-12 6:51am    
please give more thing on this may be your table structure; so that we can make the same instance on our database in order to check whether your trigger in working correctly or not.

Hi,

this trigger will be executed after the INSERT/UPDATE on AccountMaster table. So condition
SQL
IF EXISTS (SELECT 1 FROM AccountMaster WHERE AccountCode = @AccountCode )

returns always true. So change this to
SQL
IF EXISTS (SELECT  1  FROM Balances WHERE AccountCode = @AccountCode )


So the trigger is...
SQL
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
 
Share this answer
 
v2
Comments
bolshie6 9-Oct-12 7:48am    
It works, Thank you.
bolshie6 9-Oct-12 7:52am    
A similar one for delete. I need to Insert all the deleted rows (From AccountMaster) into AccountMasterHistory Table and Update the Balances Table so that the Sum(DebitAmount-CreditAmount) does not include the deleted rows for that particular AccCode

CREATE TRIGGER tri_DeleteAccountMaster ON AccountMaster
FOR DELETE
AS

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

DECLARE @BalanceAmount NUMERIC(19,4)
SELECT @BalanceAmount = SUM(DebitAmount - CreditAmount)
FROM Deleted
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

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


IF EXISTS (SELECT 1 FROM Balances WHERE AccountCode = @AccountCode )
 
Share this answer
 
Comments
bolshie6 9-Oct-12 7:53am    
Thank you
I've solved the same for a delete trigger.

SQL
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900