Post reply

The OUTPUT clause and T-SQL Syntax

  • July 22, 2013 at 10:00 pm

    #295723

    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/

  • July 22, 2013 at 11:19 pm

    #1634754

    Good question. I learn something new. 🙂

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

  • July 22, 2013 at 11:44 pm

    #1634755

    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

  • July 23, 2013 at 12:23 am

    #1634764

    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/

  • July 23, 2013 at 1:04 am

    #1634771

    Very very very GOOD question.

    😀

  • July 23, 2013 at 1:11 am

    #1634773

    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

  • July 23, 2013 at 2:45 am

    #1634810

    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/

  • July 23, 2013 at 3:20 am

    #1634819

    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/

  • July 23, 2013 at 3:42 am

    #1634829

    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/

  • July 23, 2013 at 4:00 am

    #1634845

    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/

  • July 23, 2013 at 5:55 am

    #1634895

    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/

  • July 23, 2013 at 7:13 am

    #1634932

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

  • July 23, 2013 at 7:41 am

    #1634945

    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!

  • July 23, 2013 at 9:21 am

    #1635015

    Great Question and clean code.

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

  • July 23, 2013 at 10:20 am

    #1635061

    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