A Questionable Trigger

  • Andy Warren

    SSC Guru

    Points: 119684

    Comments posted to this topic are about the item A Questionable Trigger

  • dawryn

    SSCarpal Tunnel

    Points: 4675

    Tricky again :doze: Some important information missing

    4 obvious choices and then you have to :Whistling: guess which one of remaining correct options was taken for the right one :pinch:

  • free_mascot

    One Orange Chip

    Points: 27168

    Day by day QotD becomes difficult.

    While executing update we didn't receive any result set; we got message only!!!

    I guess there is difference it result set and message.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • free_mascot

    One Orange Chip

    Points: 27168

    dawryn (4/2/2014)


    Tricky again :doze: Some important information missing

    4 obvious choices and then you have to :Whistling: guess which one of remaining correct options was taken for the right one :pinch:

    +1

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Carlo Romagnano

    SSC-Insane

    Points: 21965

    In sqlserver 2008 r2 the update doesn't return any resultset and no error message.

    (2 row(s) affected)

    (2 row(s) affected)

    What I'm missing?

  • BI_Baracus

    SSC-Addicted

    Points: 465

    4/5 isn't bad. especially when the last one was the trick question. There was no set showplan on the query and nothing to say that you were trying to bringing back the execution plan.

  • Carlo Romagnano

    SSC-Insane

    Points: 21965

    I do not like question too long. I have just few minute to answer.

    A lot of useless words and none of the main one.

  • Mighty

    SSCrazy Eights

    Points: 8785

    Carlo Romagnano (4/2/2014)


    In sqlserver 2008 r2 the update doesn't return any resultset and no error message.

    (2 row(s) affected)

    (2 row(s) affected)

    What I'm missing?

    You are missing nothing. The update works fine and it doesn't return a resultset.

    It can be however, that the software client used to test the update, can't handle the fact that the update send 2 messages with "(2 row(s) affected)". SQL Server Management Studio has no problem with that.

    And as stated by someone before, a message that is returned, is not a resultset.

  • This was removed by the editor as SPAM

  • free_mascot

    One Orange Chip

    Points: 27168

    Carlo Romagnano (4/2/2014)


    In sqlserver 2008 r2 the update doesn't return any resultset and no error message.

    (2 row(s) affected)

    (2 row(s) affected)

    What I'm missing?

    I have executed in SQL 2012 and got the same message. No error message.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Carlo Romagnano

    SSC-Insane

    Points: 21965

    free_mascot (4/2/2014)


    Carlo Romagnano (4/2/2014)


    In sqlserver 2008 r2 the update doesn't return any resultset and no error message.

    (2 row(s) affected)

    (2 row(s) affected)

    What I'm missing?

    I have executed in SQL 2012 and got the same message. No error message.

    Press ctrl+m before execute!

  • Mighty

    SSCrazy Eights

    Points: 8785

    Andy, can you display the results that you get, including your error and can you tell which tool and version you are using to execute the statements?

    As I said before, for me there are no errors, and there is no result set retuned, only two messages.

    If I would switch on the option to return the execution plan, than in fact, it is returning two result sets. The first for the initial update, the second for the update that happened inside the trigger.

  • twin.devil

    SSC-Insane

    Points: 22208

    Mighty (4/2/2014)


    Andy, can you display the results that you get, including your error and can you tell which tool and version you are using to execute the statements?

    As I said before, for me there are no errors, and there is no result set retuned, only two messages.

    If I would switch on the option to return the execution plan, than in fact, it is returning two result sets. The first for the initial update, the second for the update that happened inside the trigger.

    Error comes when the following is the setting

    sp_configure 'disallow results from triggers', 1

    reconfigure

    Include Actual Execution Plan (Ctrl+M), then you will able to see this message.

    Msg 524, Level 16, State 1, Procedure updateQuestions, Line 4

    A trigger returned a resultset and the server option 'disallow results from triggers' is true.

    By default it set to 0.

    For more details disallow results from triggers Server Configuration Option

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Sorry but this QotD was too confusing. 🙂

    Nowhere does its author states that he has execution plan options enabled.

    And an UPDATE command returns no resultset (unless we use the OUTPUT clause).

    By default, it returns only a message of rows affected when one does not use "SET NOCOUNT ON" as stated in one of the answers.

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Ed Wagner

    SSC Guru

    Points: 286982

    free_mascot (4/2/2014)


    dawryn (4/2/2014)


    Tricky again :doze: Some important information missing

    4 obvious choices and then you have to :Whistling: guess which one of remaining correct options was taken for the right one :pinch:

    +1

    +1. The result set was dependent on the execution plan and, while it generates a result set, it doesn't return one. The last one was a guess because of what was unsaid.

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

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