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

My kindergarten SQL Server taught me that a trigger may be fired with multiple rows in the inserted and deleted pseudo tables. I mostly write my trigger code with this in mind, often resulting in some cursor based cludge. Now I'm really only able to test them firing for a single row at a time. How can I generate a multirow trigger and will SQL Server actually ever send a multirow trigger? Can I set a flag so that SQL Server will only fire single row triggers??

我的幼儿园SQL Server告诉我，在插入和删除的伪表中可能会触发多行触发器。考虑到这一点，我主要编写我的触发器代码，经常导致一些基于游标的cludge。现在我真的只能测试它们一次射击一行。如何生成多行触发器并且SQL Server实际上是否会发送多行触发器？我可以设置一个标志，以便SQL Server只会触发单行触发器吗？

5 个解决方案

#1

Yes, if a statement affects more than one row, it should be handled by a single trigger call, as you might want to revert the whole transaction. It is not possible to split it to separate trigger calls logically and I don't think SQL Server provides such a flag. You can make SQL Server call your trigger with multiple rows by issuing an UPDATE or DELETE statement that affects multiple rows.

是的，如果语句影响多行，则应由单个触发器调用处理，因为您可能希望还原整个事务。它不可能在逻辑上将它拆分为单独的触发器调用，我不认为SQL Server提供这样的标志。通过发出影响多行的UPDATE或DELETE语句，可以使SQL Server通过多行调用触发器。

#2

Trigger definitions should always handle multiple rows.

触发器定义应始终处理多行。

Taken from SQLTeam:

取自SQLTeam：

-- BAD Trigger code following:

CREATE TRIGGER trg_Table1 
ON Table1 
For UPDATE
AS
DECLARE @var1 int, @var2 varchar(50)

SELECT @var1 = Table1_ID, @var2 = Column2
FROM inserted

UPDATE Table2
SET SomeColumn = @var2
WHERE Table1_ID = @var1

The above trigger will only work for the last row in the inserted table.

以上触发器仅适用于inserted表中的最后一行。

This is how you should implement it:

这是你应该如何实现它：

CREATE TRIGGER trg_Table1 
ON Table1 
FOR UPDATE
AS

UPDATE t2
SET SomeColumn = i.SomeColumn
FROM Table2 t2
INNER JOIN inserted i
ON t2.Table1_ID = i.Table1_ID

#3

First it concerns me that you are making the triggers handle multiple rows by using a cursor. Do not do that! Use a set-based statment instead jioining to the inserted or deleted pseudotables. Someone put one of those cursor based triggerson our database before I came to work here. It took over forty minutes to handle a 400,00 record insert (and I often have to do inserts of over 100,000 records to this table for one client). Changing it to a set-based solution changed the time to less than a minute. While all triggers must be capable of handling multiple rows, you must not do so by creating a performance nightmare.

首先，我担心你使用游标使触发器处理多行。不要那样做！使用基于集合的语句，而不是jioining到插入或删除的pseudotables。在我来这里工作之前，有人将这些基于光标的触发器之一放在我们的数据库中。处理400,00记录插入花了40多分钟（我经常需要为一个客户端插入超过100,000条记录到此表）。将其更改为基于集合的解决方案会将时间更改为不到一分钟。虽然所有触发器必须能够处理多行，但您不能通过创建性能噩梦来实现。

If you can write a select statment for the cusor, you can write an insert, update or delete based on the same select statment which is set-based.

如果您可以为cusor编写选择语句，则可以根据基于集合的相同选择语句编写插入，更新或删除。

#4

I've always written my triggers to handle multiple rows, it was my understanding that if a single query inserted/updated/deleted multiple rows then only one trigger would fire and as such you would have to use a cursor to move through the records one by one.

我总是写我的触发器来处理多行，我的理解是，如果单个查询插入/更新/删除了多行，那么只有一个触发器会触发，因此你必须使用游标来移动记录一个一个人。

#5

One SQL statement always invokes one trigger execution - that's part of the definition of a trigger. (It's also a circumstance that seems to at least once trip up everyone who writes a trigger.) I believe you can discover how many records are being affected by inspecting @@ROWCOUNT.

一个SQL语句总是调用一个触发器执行 - 这是触发器定义的一部分。（这也是一种情况，似乎至少一次绊倒每个写入触发器的人。）我相信你可以通过检查@@ ROWCOUNT来发现有多少记录受到影响。

#1

#2