Triggers only handle one row at a time?

Transact-SQL
1

So I've got a silly question. Don't triggers only handle one row at a time?

For instance, I've got an "after update" trigger on a table. If I run a query that updates 20 records in one update statement for that table, it's treated as 20 separate transactions, and the trigger will run 20 separate times - once after each row is updated. It doesn't just run the trigger once for all 20 records, does it? If it does, then the inserted and deleted tables would be a lot harder to work with than I ever realized before.

The reason I'm asking is because I just ran across an "update after" trigger on our system that uses a cursor to go through the inserted table one record at a time. But if I'm understanding correctly, the inserted and deleted tables will never have more than one record, right? So that cursor just seems pointless to me.

I haven't worked with triggers that much in the past, but I thought I understood the basics pretty well, so this one had me confused.

2

For each update statement the trigger is only fired once. So if your update statement updated 20 rows, the INSERTED and DELETED virtual tables in the trigger will contain 20 rows.

Also, the update statement is a single transaction. i.e., the changes to all 20 rows will succeed, or no changes will be made to any row,

That said, in most cases, there may be better ways to handle the 20 rows in INSERTED/DELETED tables without using cursors.

3

I can confirm that. In SQL Server, there is not even the option to make SQL fire the trigger for each row separately. The trigger fires once per DELETE / INSERT / UPDATE (thus, MERGE can actually fire multiple triggers, but only because different actions are being done).

4

Actually you dont really need a cursor inside trigger on most occasions. Almost all logic can be implemented using set based methods in SQLServer and would be much better in performance compared to the cursor based solution.