How many rows will be returned?

  • Subhash Kr Singh

    SSC-Addicted

    Points: 481

    Comments posted to this topic are about the item How many rows will be returned?

  • twin.devil

    SSC-Insane

    Points: 22208

    Very nice question. Thanks for share Subhash.

  • Koen Verbeeck

    SSC Guru

    Points: 258907

    Great question, thanks!

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

  • Carlo Romagnano

    SSC-Insane

    Points: 21712

    Triggers on tables execute only if at least ONE row is affected. So, explanation is wrong.

  • Iulian -207023

    SSCertifiable

    Points: 7507

    Good question , thank you.

    Iulian

  • Mighty

    SSCrazy Eights

    Points: 8398

    Carlo Romagnano (9/23/2014)


    Triggers on tables execute only if at least ONE row is affected. So, explanation is wrong.

    Hi Carlo,

    you are wrong in your assumption. And I guess the idea behind this question was to show that a trigger fires whether or not any rows are affected.

    Check e.g. the note on this MSDN page for SQL 2005. And it didn't change since.

    These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.This is by design.

  • PRAMANA.DBA

    SSCertifiable

    Points: 5507

    Good Question Subhash.....

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286957

  • Divine Flame

    SSCoach

    Points: 15941

    Mighty (9/23/2014)


    Carlo Romagnano (9/23/2014)


    Triggers on tables execute only if at least ONE row is affected. So, explanation is wrong.

    Hi Carlo,

    you are wrong in your assumption. And I guess the idea behind this question was to show that a trigger fires whether or not any rows are affected.

    Check e.g. the note on this MSDN page for SQL 2005. And it didn't change since.

    These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.This is by design.

    +1


    Sujeet Singh

  • SqlMel

    SSCrazy

    Points: 2891

    Good question.

    This is something to consider when using triggers. If you need to execute the code inside the trigger only if it affects some rows, you can use the @@ROWCOUNT variable.

    The following code will only insert 1 row in ABC_T1:

    USE tempdb

    GO

    CREATE TABLE ABC_TriggerTest

    (

    ID INT NOT NULL

    IDENTITY(1, 1)

    , [Name] NVARCHAR(10)

    )

    GO

    CREATE TABLE ABC_T1 ( ID INT )

    GO

    CREATE TRIGGER Trg_Test ON ABC_TriggerTest

    FOR INSERT, UPDATE

    AS

    BEGIN

    if @@ROWCOUNT > 0

    BEGIN

    INSERT INTO ABC_T1

    VALUES ( '1' )

    END

    END

    GO

    INSERT INTO ABC_TriggerTest

    SELECT 'First'

    GO

    INSERT INTO ABC_TriggerTest

    SELECT [Name]

    FROM ABC_TriggerTest

    WHERE 1 = 2

    --How many rows will be there for below statement

    SELECT *

    FROM ABC_T1

    ---------------
    Mel. 😎

  • SqlMel

    SSCrazy

    Points: 2891

    Carlo Romagnano (9/23/2014)


    Triggers on tables execute only if at least ONE row is affected. So, explanation is wrong.

    Ummm..... Nope, that's not true.

    I think the question reflects exactly how that isn't true.

    ---------------
    Mel. 😎

  • This was removed by the editor as SPAM

  • TomThomson

    SSC Guru

    Points: 104763

    SqlMel (9/23/2014)


    Good question.

    This is something to consider when using triggers. If you need to execute the code inside the trigger only if it affects some rows, you can use the @@ROWCOUNT variable.

    Definitely a good question.

    I have always had DML triggers whose action depends on the contents of whichever is appropriate of the inserted and deleted virtual tables, rather than @@ROWCOUNT. Usually I want a trigger to take different actions is no rows are affected from the nomal (at least one row affected) action, but there are exceptions: some tracking triggers just stick for example "n rows deleted from table tablename by login xyz at dtime on date" in a log table and that is the same action whether n is 0 or not.

    Tom

  • Sean Lange

    SSC Guru

    Points: 286408

    Great question. I pretty much knew what was being tested part way through and skipped the first insert. Guess I should wait until after I finish my first cup of coffee. 😛

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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