MSSQL 2005: Using OUTPUT keyword with table that has triggers
Posted on 2007-07-31
Here is a citation from MSDN about using OUTPUT for table which has triggers:
"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."
And here is my problem: I have a table, called MyTable, which has an UPDATE trigger and an INSERT trigger.
When I run an insert query to my table:
insert into MyTable OUTPUT inserted.Id values(...) -- ID is a identity column
I get this error message (of course :-( ): "The target table 'Resource' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause."
I can work around by re-writing the query:
insert into MyTable values(...) select id from MyTable where id = @@identity
However, the "select" version is a little slower than the OUTPUT one.
Could you please tell me there is any way to use OUTPUT in table that has triggers?
Thank you in advance.