The OUTPUT clause and T-SQL Syntax

  • psingla

    Hall of Fame

    Points: 3840

    Comments posted to this topic are about the item The OUTPUT clause and T-SQL Syntax

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • Danny Ocean

    SSCertifiable

    Points: 6098

    Good question. I learn something new. 🙂

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Lokesh Vij

    SSChampion

    Points: 10836

    Good Question Pramod. But explanation could have been refined.

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • kapil_kk

    SSC-Insane

    Points: 21316

    Danny Ocean (7/22/2013)


    Good question. I learn something new. 🙂

    +1 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Carlo Romagnano

    SSC-Insane

    Points: 21794

    Very very very GOOD question.

    😀

  • Koen Verbeeck

    SSC Guru

    Points: 258942

    Very nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • kapil_kk

    SSC-Insane

    Points: 21316

    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

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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?


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • kapil_kk

    SSC-Insane

    Points: 21316

    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..............

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • kapil_kk

    SSC-Insane

    Points: 21316

    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/

  • Danny Ocean

    SSCertifiable

    Points: 6098

    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 !!!

  • Dana Medley

    SSCertifiable

    Points: 6764

    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!

  • PHYData DBA

    SSCertifiable

    Points: 7541

    Great Question and clean code.

    Your explanation is very brief but understandable considering the behavior of Triggers. Keep it Super Simple! 😉

  • zymos

    SSCommitted

    Points: 1960

    Good and tricky question on Output clause and how triggers work.

    Thanks. 🙂

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply