3

I have two after triggers on target table (one for insert and one for update). Now if I execute merge on the target table, the triggers are executed only once. Although the merge statement executes around 300 updates, and 200 inserts.

I checked it with print statements in each trigger, right after getting data from deleted, inserted record into variables.

How come? Is this a bug?

I have SQL Server 2008 sp1 std (part of the SBS2k8).

benRollag
  • 1,219
  • 4
  • 16
  • 21
pierre
  • 31
  • 1
  • 2
  • As @gbn's answer indicates, triggers run once for the entire statement, so "right after getting data from deleted, inserted record into variables." means you're doing it wrong. There can be *multiple* rows in the `inserted` and `deleted` tables. This is as true for `INSERT`, `UPDATE` and `DELETE` statements as it is for `MERGE`. – Damien_The_Unbeliever Dec 13 '11 at 09:48
  • So i must check number of records in the inserted field, and go through with a cursor to insert each change to a log table? Or is there any other solution? – pierre Dec 13 '11 at 09:55
  • @pierre: no, just work on sets – gbn Dec 13 '11 at 10:00

1 Answers1

6

A trigger runs per single action. Not "per row"

You have one insert of 200 rows and one update for 300 rows.

So, the trigger runs once if for insert and update, or the separate triggers run once each

Edit:

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Is this still the case? Is there a workaround? Thanks – J. Lavoie Aug 10 '17 at 16:59
  • The link to Brent Ozar's blog seems to be broken. This article seems to cover the same topic but it was written years later according to its date: https://www.brentozar.com/archive/2019/05/the-silent-bug-i-find-in-most-triggers/ – Riley Major Apr 15 '22 at 20:16