3

Firstly, a lot of people are unfamiliar with output in my experience. If so, this link is very handy: Hidden Features of 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.

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?

Community
  • 1
  • 1
Michael A
  • 9,480
  • 22
  • 70
  • 114

1 Answers1

5

This

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.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91