Trigger For a bulk insert

Question

4.00/5 (1 vote)

See more:

There is trigger that will be triggered when i do an insert in the Account table. On inserting values into that table, i need to update the balance table.
Instead of a single insert query, i need to do a bulk insert. And for all the records inserted in Account table, The balance is to be updated in the Balance table.

I'm half way through this. This is the code that i've written so far.

Note : I need to do it using a table variable.

SQL

CREATE TRIGGER tri_InsertBalanceBulk ON AccountMaster
FOR INSERT
AS

BEGIN

	DECLARE 
		@AccountCode VARCHAR(100),
		@BalanceAmount NUMERIC(19,4)
		
	SELECT 
		@AccountCode = [AccountCode],
		@BalanceAmount = DebitAmount - CreditAmount
	FROM 
		Inserted
	
	DECLARE	@TableVariable TABLE (AccountCode VARCHAR(100), BalanceAmount NUMERIC(19,4))
	
	INSERT INTO @TableVariable (AccountCode, BalanceAmount)
	SELECT AccountCode, SUM(DebitAmount - CreditAmount)
	FROM Inserted
	GROUP BY AccountCode
		
	IF NOT EXISTS (SELECT 1 FROM Balances WHERE AccountCode = @AccountCode )
	BEGIN 
		INSERT INTO Balances (AccountCode, BalanceAmount)
		SELECT AccountCode,BalanceAmount FROM @TableVariable
	END
	ELSE
	BEGIN
		UPDATE 
			Balances 
		SET 
			BalanceAmount = (SELECT Balance FROM @TableVariable T
		WHERE Balances.AccountCode = T.AccountCode)
			
		WHERE 
			AccountCode IN ( SELECT AccountCode FROM @TableVariable)
	END
END

Posted 10-Oct-12 2:01am

bolshie6

Updated 10-Oct-12 2:02am

v2

Add a Solution

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-10T23:38:00

We can use MERGER directly without using table variable. Try the following code block..

SQL

ALTER TRIGGER tri_InsertBalanceBulk ON AccountMaster
FOR INSERT
AS
BEGIN
 	
    MERGE INTO Balances As Tgt
    USING INSERTED As Src
    ON Src.AccountCode = Tgt.AccountCode
    WHEN MATCHED THEN
     UPDATE Set	Tgt.BalanceAmount = Tgt.BalanceAmount +Src.DebitAmount - 
     Src.CreditAmount
    WHEN NOT MATCHED THEN 
     INSERT (AccountCode,BalanceAmount) VALUES (Src.AccountCode,Src.DebitAmount -   
     Src.CreditAmount);
	
END

Thank you...

Corporal Agarn · Accepted Answer · 2012-10-10T03:13:00

Solution 1

Can you use a MERGE command? Not sure if they can be used in a trigger.

MERGE (TSQL)[^]

Posted 10-Oct-12 3:13am

Corporal Agarn

Updated 10-Oct-12 3:20am

v2

Comments

bolshie6 10-Oct-12 9:15am

yes, a join can be used

Corporal Agarn 10-Oct-12 9:18am

The join I know can be used but what about the MERGE command?

bolshie6 · Accepted Answer · 2012-10-10T20:42:00

Here's the solution.

SQL

CREATE TRIGGER tri_InsertBalanceBulk ON AccountMaster
FOR INSERT
AS

BEGIN

	DECLARE	@TableVariable TABLE 
		(
		AccountCode VARCHAR(100),
		BalanceAmount NUMERIC(19,4)
		)
	
	INSERT INTO @TableVariable (AccountCode, BalanceAmount)
	SELECT 
		AccountCode, 
		SUM(DebitAmount - CreditAmount)
	FROM 
		Inserted
		
	GROUP BY 
		AccountCode
			
	UPDATE Balances
	SET BalanceAmount = Balances.BalanceAmount + AccMaster.BalanceAmount
	FROM Balances 
		INNER JOIN @TableVariable AccMaster
			ON Balances.AccountCode = AccMaster.AccountCode
						
	INSERT INTO Balances(AccountCode, BalanceAmount)
	SELECT AccMaster.AccountCode, AccMaster.BalanceAmount
	FROM @TableVariable AccMaster
	WHERE AccMaster.AccountCode NOT IN (SELECT AccountCode FROM Balances)

END

Trigger For a bulk insert

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0