SQL Server 用于处理多行插入的触发器 [英] SQL Server A trigger to work on multiple row inserts

问题描述

我正在维护一些代码，这些代码在表上有一个触发器来增加一列.然后该列由第 3 方应用程序 A 使用.假设该表名为 test，有两列 num1 和 num2.触发器在 test 中的每个 num1 插入时运行.以下是触发器:

I am maintaining some code that has a trigger on a table to increment a column. That column is then used by a 3rd party application A. Lets say that the table is called test with two columns num1 and num2. The trigger runs on each insert of num1 in test. Following is the trigger:

USE [db1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TEST_MYTRIG] ON [dbo].[test]
FOR INSERT AS
begin
SET NOCOUNT ON
DECLARE @PROC_NEWNUM1 VARCHAR (10)
DECLARE @NEWNUM2 numeric(20)

SELECT @PROC_NEWNUM1 = num1 FROM INSERTED
select @NEWNUM2 = MAX(num2) from TEST
if @NEWNUM2 is null
Begin
set  @NEWNUM2  = 0
end
set @NEWNUM2 = @NEWNUM2 + 1
UPDATE TEST SET num2 = @NEWNUM2 WHERE num1 = @PROC_NEWNUM1
SET NOCOUNT OFF
End

这在简单的基于行的插入中工作正常，但还有另一个 3rd 方应用 B(叹气)有时会在这个表上进行多次插入，但不完全是这样:

This works fine in simple row based inserts, but there is another 3rd party app B (sigh) that sometimes does multiple inserts on this table something like this but not exactly:

INSERT INTO [db1].[dbo].[test]
           ([num1])

   Select db1.dbo.test.num1 from [db1].[dbo].[test]
GO

这会导致触发器行为不正常...

This causes the trigger to behave erratically...

现在我无法访问应用程序A 或B 的源代码，只能控制数据库和触发器.是否可以使用触发器完成任何操作，以便在多次插入的情况下对 num2 所做的更新是正确的?

Now I don't have access to the source of app A or B and only control the database and the trigger. Is there anything that can be done with the trigger so that the updates done to num2 are correct in case of multiple inserts?

解决方案:

以下是基于affan代码的解决方案:

Following is the solution based on affan's code:

 DECLARE @PROC_NEWNUM1 VARCHAR (10)
 DECLARE @NEWNUM2 numeric(20)
 DECLARE my_Cursor CURSOR FAST_FORWARD FOR SELECT num1 FROM INSERTED;

 OPEN my_Cursor 
 FETCH NEXT FROM my_Cursor into @PROC_NEWNUM1

 WHILE @@FETCH_STATUS = 0 
 BEGIN 

 select @NEWNUM2 = MAX(num2) from TEST
 if @NEWNUM2 is null
 Begin
    set  @NEWNUM2  = 0
 End
 set @NEWNUM2 = @NEWNUM2 + 1
 UPDATE TEST SET num2 = @NEWNUM2  WHERE num1 = @PROC_NEWNUM1
 FETCH NEXT FROM my_Cursor into @PROC_NEWNUM1  
 END

CLOSE my_Cursor
DEALLOCATE my_Cursor

在此处查看基于集合的方法:SQL Server - 重写触发器以避免基于游标的方法

Check here for a set based approach: SQL Server - Rewrite trigger to avoid cursor based approach

推荐答案

您只需要在 INSERTED 上打开一个游标，并针对 @PROC_NEWNUM1 对其进行迭代，然后将其余代码放入该循环中.例如

You just have to open a cursor on INSERTED and iterate it for @PROC_NEWNUM1 and put your rest of code that loop. e.g

 DECLARE @PROC_NEWNUM1 VARCHAR (10)
 DECLARE @NEWNUM2 numeric(20)
 DECLARE my_Cursor CURSOR FOR SELECT num1 FROM INSERTED; 
 OPEN my_Cursor; 

 FETCH NEXT FROM @PROC_NEWNUM1; 


 WHILE @@FETCH_STATUS = 0 
 BEGIN FETCH NEXT FROM my_Cursor 
 select @NEWNUM2 = MAX(num2) from TEST
 if @NEWNUM2 is null
 Begin
  set  @NEWNUM2  = 0
 end
 set @NEWNUM2 = @NEWNUM2 + 1
 UPDATE TEST SET num2 = @NEWNUM2 WHERE num1 = @PROC_NEWNUM1

 END; 

CLOSE my_Cursor; DEALLOCATE my_Cursor;

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