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.
目标表上有两个after触发器(一个用于插入，一个用于更新)。现在，如果我在目标表上执行merge，触发器只执行一次。尽管merge语句执行了大约300个更新和200个插入。
I checked it with print statements in each trigger, right after getting data from deleted, inserted record into variables.
我在每个触发器中使用print语句检查它，在从删除数据中获取数据之后，将记录插入到变量中。
How come? Is this a bug?
如何来吗?这是一个错误吗?
I have SQL Server 2008 sp1 std (part of the SBS2k8).
我有SQL Server 2008 sp1 std (SBS2k8的一部分)。
1 个解决方案
#1
6
A trigger runs per single action. Not "per row"
每个动作都运行一个触发器。不是“每一行”
You have one insert of 200 rows and one update for 300 rows.
一个插入200行，一个更新300行。
So, the trigger runs once if for insert and update, or the separate triggers run once each
因此，如果要插入和更新，触发器将运行一次，或者每个触发器运行一次
Edit:
编辑:
- From MSDN, "Multirow Considerations for DML Triggers"
- 从MSDN，“DML触发器的多行考虑”
- From Brent Ozar, "Triggers Need to Handle Multiple Records"
- 布伦特·厄扎尔(Brent Ozar)说，“触发器需要处理多个记录”
#1
6
A trigger runs per single action. Not "per row"
每个动作都运行一个触发器。不是“每一行”
You have one insert of 200 rows and one update for 300 rows.
一个插入200行，一个更新300行。
So, the trigger runs once if for insert and update, or the separate triggers run once each
因此，如果要插入和更新，触发器将运行一次，或者每个触发器运行一次
Edit:
编辑:
- From MSDN, "Multirow Considerations for DML Triggers"
- 从MSDN，“DML触发器的多行考虑”
- From Brent Ozar, "Triggers Need to Handle Multiple Records"
- 布伦特·厄扎尔(Brent Ozar)说，“触发器需要处理多个记录”