Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

The OUTPUT clause and T-SQL Syntax Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 5:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
Hugo Kornelis (7/23/2013)
kapil_kk (7/23/2013)
Hugo Kornelis (7/23/2013)
kapil_kk (7/23/2013)
Can someone please throw more light on this statement-
DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause

Which part of the statement do you not understand?

Hi Hugo,
I am not getting by the statement part -
DML statement cannot have any enabled triggers..............

Ah, now I see the confusion. That looks as if a few words were cut off at the start (triggers are created for tables and views, not for statements. so I suspect that this sentence should have started with "The target of the DML statement ...
Or we can go with the explanation in Books Online:
"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".

In other words - if the statement causes a trigger to fire, it should have either no OUTPUT clause or an OUTPUT clause with an INTO keyword. Using the OUTPUT clause without INTO (so that the results of the OUTPUT specification go to the client) is not allowed when a trigger fires.

Thanks a lot Hugo.....
Learn new thing today



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1476520
Posted Tuesday, July 23, 2013 7:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Hugo Kornelis (7/23/2013)
...
In other words - if the statement causes a trigger to fire, it should have either no OUTPUT clause or an OUTPUT clause with an INTO keyword. Using the OUTPUT clause without INTO (so that the results of the OUTPUT specification go to the client) is not allowed when a trigger fires.


Thanks hugo for simple explanation.




Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1476559
Posted Tuesday, July 23, 2013 7:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:01 AM
Points: 1,878, Visits: 1,414
Danny Ocean (7/23/2013)
Hugo Kornelis (7/23/2013)
...
In other words - if the statement causes a trigger to fire, it should have either no OUTPUT clause or an OUTPUT clause with an INTO keyword. Using the OUTPUT clause without INTO (so that the results of the OUTPUT specification go to the client) is not allowed when a trigger fires.


Thanks hugo for simple explanation.


+1 As always, thanks for the question and thank you Hugo for the explanation.




Everything is awesome!
Post #1476573
Posted Tuesday, July 23, 2013 9:21 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:29 AM
Points: 337, Visits: 303
Great Question and clean code.
Your explanation is very brief but understandable considering the behavior of Triggers. Keep it Super Simple!
Post #1476653
Posted Tuesday, July 23, 2013 10:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:52 PM
Points: 483, Visits: 244
Good and tricky question on Output clause and how triggers work.

Thanks.
Post #1476705
Posted Tuesday, July 23, 2013 1:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:57 AM
Points: 3,323, Visits: 1,988
Thanks for the question Pramod. I had never considered how output would work with triggers.
Post #1476780
Posted Wednesday, July 24, 2013 12:47 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,371, Visits: 1,391
Nice...


Post #1476890
Posted Wednesday, July 24, 2013 12:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:38 AM
Points: 823, Visits: 154
Danny Ocean (7/22/2013)
Good question. I learn something new.

Yes...+1
Post #1476892
Posted Wednesday, July 24, 2013 1:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:53 AM
Points: 2,529, Visits: 2,402
Hugo Kornelis (7/23/2013)
...
In other words - if the statement causes a trigger to fire, it should have either no OUTPUT clause or an OUTPUT clause with an INTO keyword. Using the OUTPUT clause without INTO (so that the results of the OUTPUT specification go to the client) is not allowed when a trigger fires.


The question is WHY?
Technically, I do not see the connection or the hindrance of TRIGGER -> OUTPUT -> INTO.
Post #1476897
Posted Wednesday, July 24, 2013 1:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:36 PM
Points: 6,002, Visits: 8,267
Carlo Romagnano (7/24/2013)
Hugo Kornelis (7/23/2013)
...
In other words - if the statement causes a trigger to fire, it should have either no OUTPUT clause or an OUTPUT clause with an INTO keyword. Using the OUTPUT clause without INTO (so that the results of the OUTPUT specification go to the client) is not allowed when a trigger fires.


The question is WHY?
Technically, I do not see the connection or the hindrance of TRIGGER -> OUTPUT -> INTO.


<speculation>

OUTPUT without INTO sends data to the client.
A trigger might also send output to the client.

Maybe there were some test cases where the two outputs somehow interfered, and the team decided that fixing it would take too much resources???

</speculation>



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1476902
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse