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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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