[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSSQL 2005: Using OUTPUT keyword with table that has triggers

Posted on 2007-07-31
5
Medium Priority
?
1,142 Views
Last Modified: 2013-11-07
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
Comment
Question by:thuannguy
  • 3
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 19600911
>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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19600916
>However, the "select" version is a little slower than the OUTPUT one.
do you have actually an index on the id field?
0
 
LVL 6

Author Comment

by:thuannguy
ID: 19602006
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19602104
with my above suggestion, it should generate even less reads :-)
0
 
LVL 6

Author Comment

by:thuannguy
ID: 19602297
yeah, I'll take a try tomorrow :D
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question