触发器用于批量插入 [英] Trigger For a bulk insert

问题描述

有一个触发器，当我在帐户"表中进行插入时将被触发.将值插入该表时，我需要更新余额表.
而不是单个插入查询，我需要进行批量插入.对于插入到帐户"表中的所有记录，将在余额"表中更新余额.

我已经完成了一半.这是我到目前为止编写的代码.

注意:我需要使用表变量来完成此操作.

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.

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

推荐答案

我们可以直接使用MERGER，而无需使用表变量.尝试以下代码块..

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

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...

可以使用MERGE命令吗?不确定是否可以在触发器中使用它们.

MERGE(TSQL) [

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

MERGE (TSQL)[^]

这是解决方案.

Here''s the solution.

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

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