Trigger to Insert or Update

Question

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 9-Oct-12 0:16am

bolshie6

Add a Solution

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.

3 solutions

Add a Solution

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

damodara naidu betha · Accepted Answer · 2012-10-09T01:18:00

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

Niju1 · Accepted Answer · 2012-10-09T01:21:00

Solution 2

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

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

Posted 9-Oct-12 1:21am

Niju1

Comments

bolshie6 9-Oct-12 7:53am

Thank you

bolshie6 · Accepted Answer · 2012-10-09T02:09:00

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

Trigger to Insert or Update

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Trigger to Insert or Update

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us