How to test for multiple row actions in a SQL Server trigger?
Solution 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.
Solution 2
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
Solution 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.
Tom
Developer / Consultant doing everything. Building computers and stuff, managing servers and networks, application managment. Developing in Delphi, PHP, javascript, transact-sql, pl/sql, C Html, photoshop, illustrator Business consulting Drupal CMS, Ubercart Twitter: @sauce71
Updated on July 27, 2022Comments
-
Tom over 1 year
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??
-
Dave Markle over 15 yearsI'll go a bit further and say your triggers should ALWAYS take multiple rows into account, whether or not you expect them to be called that way.
-
Mitch Wheat over 15 years@Dave Markle: Totally agree. Would vote up your comment if I could!
-
dburges over 15 yearsDon't use a cursor in a trigger, use a set-based statement instead,
-
Tim Schmelter about 13 years
SET SomeColumn = t1.SomeColumn
seems to be wrong, where is the aliast1
defined? Nevertheless +1 because now i understand why my trigger was only fired for the last inserted row. -
Mitch Wheat about 13 years@Tim: sorry, it was a typo. Updated.
-
IngoB over 6 yearsI need to execute the same set of updates after a trigger fires, in 3 different places (triggers). For maintainability reasons I put all the updates into a SP and let the triggers just call the procedure. So I need a cursor here in case of a multi row update ... :|
-
dburges over 6 yearsYou can write a stored proc that uses a table variable as an input variable and performs set-based work. Under no circumstances use a cursor in a trigger.
-
dburges over 6 yearsFurther, performance is far more critical than maintainability. Maintainability should never trump performance.