在更新语句中使用output子句时出现奇怪的错误?

Firstly, a lot of people are unfamiliar with output in my experience. If so, this link is very handy: Hidden Features of SQL Server

首先，在我的经历中，很多人对输出不熟悉。如果是这样，这个链接非常方便:SQL Server的隐藏特性

I have the following update statement:

我有以下更新声明:

UPDATE  lease_deal.lease_budget
SET     change_type = NULL 
OUTPUT  inserted.*
WHERE   ISNULL(change_type, '') = ''

Although I thought this would return the updated records for me I'm receiving the following error:

虽然我认为这将为我返回更新的记录，但我收到了以下错误:

Msg 334, Level 16, State 1, Line 9 The target table 'lease_deal.lease_budget' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Msg 334，第16级，状态1，第9行目标表的lease_deal。如果语句包含没有INTO子句的输出子句，则DML语句的lease_budget'不能有任何启用的触发器。

I know I can successfully create a temporary table and redirect the updated records there using the output statement, but why can't I return it to the IDE? I'm sure (certain) I've been able to do this before but can't seem to find a suitable example anywhere to help me understand what I'm doing wrong. Is this simply not possible when you have triggers on a column that you're updating?

我知道我可以使用output语句成功地创建一个临时表并重定向更新的记录，但是为什么我不能将它返回到IDE呢?我确信(肯定)我以前就能做到这一点，但似乎找不到一个合适的例子来帮助我理解我做错了什么。当您正在更新的列上有触发器时，这是不可能的吗?

1 个解决方案

#1

This

这

http://msdn.microsoft.com/en-au/library/ms177564.aspx

http://msdn.microsoft.com/en-au/library/ms177564.aspx

says this

说,这

If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.

如果指定了OUTPUT子句，但没有指定INTO关键字，则DML操作的目标不能为给定的DML操作在其上定义任何启用的触发器。例如，如果在UPDATE语句中定义了OUTPUT子句，则目标表不能有任何启用的UPDATE触发器。