The OUTPUT clause and T-SQL Syntax

  • 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/

  • Good question. I learn something new. 🙂

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

  • 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

  • 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/

  • Very very very GOOD question.

    😀

  • 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

  • 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/

  • 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/

  • 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/

  • 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/

  • 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/

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

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

  • Great Question and clean code.

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

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

    Thanks. 🙂

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

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