为什么在没有数据要插入时调用插入触发器? [英] Why is insert trigger called when there is no data to insert?

问题描述

我注意到一种非常奇怪的行为.如果我执行插入选择语句并且选择部分不返回任何数据，则插入部分仍然会执行，甚至会调用插入触发器(在插入触发器中，插入的伪表中有 0 行).

I noticed a behavior which comes to me as a very wierd one. If I make an insert-select statement and the select part returns no data, the insert part still executes and even insert trigger is called (in insert trigger there is 0 rows in inserted pseudotable).

示例:

insert into table1 column1
select column1 from table2 where condition = 'Never met'

输出:

(0 row(s) affected) // 0 rows inserted
(1 row(s) affected) // log from trigger

可能是由通用"触发器(立即声明为 FOR INSERT、UPDATE、DELETE)引起的吗?我知道这更像是假设性问题，我必须接受这种行为.但为什么会这样呢?对我来说完全是无稽之谈.我使用的是 SQL Server 2005.

May it be caused by 'universal' trigger (declared FOR INSERT, UPDATE, DELETE at once)? I know that this is more like hypothetical question and I have to accept this behavior. But why is this happening? It makes no sense to me. I am using SQL Server 2005.

-- kwitee

推荐答案

因为它们是 记录要这样做吗?

Because they're documented to do so?

DML 触发器在用户尝试通过数据操作语言 (DML) 事件修改数据时执行.DML 事件是表或视图上的 INSERT、UPDATE 或 DELETE 语句.这些触发器会在触发任何有效事件时触发，无论是否有任何表行受到影响.

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

(我的重点)

也许这没什么意义，但也许微软会花费更多的时间和开发精力来创建一个特殊的代码路径，当结果证明没有受影响的行时抑制触发器.

Maybe it makes little sense, but maybe it would cost more time and development effort for microsoft to create a special code path that suppresses the triggers when it turns out that there are no rows affected.

这只是另一个示例，您需要仔细设计触发器以处理可能包含 0、1 或多行的 inserted 和 deleted.

It's just another example where you need to design triggers carefully to deal with inserted and deleted potentially containing 0, 1 or many rows.

(另外，从关系的角度来看，不包含元组的集合有时仍然很有趣)

(Also, from a relational point of view, sets containing no tuples can still be interesting at times)

这篇关于为什么在没有数据要插入时调用插入触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！