SQL Server用于处理多行插入的触发器

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:

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

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

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:

这在基于行的简单插入中工作得很好，但还有另一个第三方应用程序B（叹气），有时在这个表上有多个这样的插入，但不完全相同：

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

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

This causes the trigger to behave erratically...

这会导致触发器表现不正常......

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?

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

Solution:

解：

Following is the solution based on affan's code:

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

 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

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

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

4 个解决方案

#1

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

你只需要在INSERTED上打开一个游标并为@ PROC_NEWNUM1迭代它并将你剩下的代码循环。例如

 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;

#2

Take a look at inserted pseudo table in your trigger as it will contain multiple rows during these operations. You should always handle multiple rows in your triggers anyway.

在触发器中查看插入的伪表，因为在这些操作期间它将包含多行。无论如何，您应始终在触发器中处理多行。

See here for more info:

有关详情，请参阅此处：

How to test for multiple row actions in a SQL Server trigger?

如何在SQL Server触发器中测试多行操作？

#3

Trigger needs to be rewriteen to handle multiple row inserts. Never write a trigger like that using variables. All triggers must alawys consider that someday someone is going to do a multi-row insert/update/delete.

需要重写触发器以处理多行插入。永远不要使用变量编写类似的触发器。所有触发器都必须考虑到有一天某人会进行多行插入/更新/删除。

You shouldn't be incrementing columns that way in a trigger either, if you need incremented column numbers why aren't you using an identity column?

您不应该在触发器中以这种方式递增列，如果您需要递增的列号，为什么不使用标识列？

#4

INSERT INTO dbo.media_queue (table_name, table_id, media_id, tunnel, sub_tunnel, event)
        SELECT
            'media_info'
            ,i.id
            ,i.session_id
            ,i.tunnel
            ,i.sub_tunnel
            ,NULL
        FROM INSERTED i;
END

#1