• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1151
  • Last Modified:

MSSQL 2005: Using OUTPUT keyword with table that has triggers

Hi experts,
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.
Thuannguy.
0
thuannguy
Asked:
thuannguy
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Could you please tell me there is any way to use OUTPUT in table that has triggers?
as you read by the docs, this is a limitation, where you have no choice than to use the workaround.

note: you should use the scope_identity() instead of @@identity, especially as you have triggers:

SET NOCOUNT ON
insert into MyTable values(...)
select scope_identity() as id

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>However, the "select" version is a little slower than the OUTPUT one.
do you have actually an index on the id field?
0
 
thuannguyAuthor Commented:
Yes, I do. I used the MSSQL Profiler to evaluate the performance and saw that it took more READs...
Btw, thank you for your help :)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
with my above suggestion, it should generate even less reads :-)
0
 
thuannguyAuthor Commented:
yeah, I'll take a try tomorrow :D
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now