[ACCEPTED]-How to test for multiple row actions in a SQL Server trigger?-database

Accepted answer
Score: 10

Trigger definitions should always handle 2 multiple rows.

Taken from SQLTeam:

-- BAD Trigger code following:

ON Table1 
DECLARE @var1 int, @var2 varchar(50)

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

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 1 should implement it:

ON Table1 

SET SomeColumn = i.SomeColumn
FROM Table2 t2
INNER JOIN inserted i
ON t2.Table1_ID = i.Table1_ID
Score: 4

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

Score: 1

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

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

Score: 0

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

Score: 0

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

More Related questions