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

Question

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

Answer 1

Trigger definitions should always handle multiple rows.

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

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

Answer 2

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.

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

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.

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

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