Post reply

@@rowcount Returns 0

  • February 6, 2007 at 4:51 pm

    #171330

    My Update/Insert trigger starts with "IF @@ROWCOUNT = 0 RETURN".  Regardless of whether I insert or update, the @rowcount seems to always be 0.  I tested this with raiserror and it sure does say that it is 0. 

    I've searched through and I cannot find anything wrong with the code.  It evens does this if I remove everything below it. 

    Anyone have any clues or ideas?

    Thanks in advance!

     

    MSSQL2000

  • February 6, 2007 at 5:11 pm

    #688171

    Just a guess.  @@rowcount is returning the rowcount from the current context (in this case, the trigger).

    How about using

    IF (select count(*) from INSERTED) = 0

     

    instead?

    And then again, I might be wrong ...
    David Webb

  • February 6, 2007 at 5:14 pm

    #688172

    IF EXISTS (select 1 from INSERTED)

    will perform faster.

    If your trigger is for delete as well use

    IF EXISTS (select 1 as One from INSERTED UNION select 1 as One from DELETED)

    _____________
    Code for TallyGenerator

  • February 6, 2007 at 5:16 pm

    #688173

    The @@rowcount system parameter indicates the number of rows affected by the LAST statement. Since you have no statements preceding the conditional 'IF' statement, it will always return zero.

    If you are interested in the rows updated/inserted in a table, have a look at the 'inserted' and 'deleted' special tables.

  • February 6, 2007 at 9:47 pm

    #688188

    Not true in a trigger folks... if @@ROWCOUNT is either the very first thing or the first thing after variable declarations, it will identify if any rows have been affected.

    The real key here is, why the heck would you want to use it at the beginning of a trigger to see if any rows were affected?  SOMETHING fired the trigger and I have a hard time believing that it would ever be something with zero rows...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 6, 2007 at 9:47 pm

    #688187

    Juan,

    Post your trigger code... we're only guessing until we see the code...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 6, 2007 at 11:47 pm

    #688201

    Something I discovered yesterday is that IF resets @@rowcount.

    The following code enters the if, but returns 0 as the rowcount.

    select

    * from sysobjects

    IF @@rowcount>0

     select @@rowcount

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 7, 2007 at 7:14 am

    #688273

    Yep... that would be correct and that would also be why most folks think it doesn't work in the trigger as capturing the number of rows inserted, updated, or deleted that fired the trigger.  The following does work in a trigger (as strange as it looks) if it's the very first thing in the trigger code...

    CREATE TRIGGER trigger_name

    ON { table | view }

    [ WITH ENCRYPTION ]

    {

        { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

            [ WITH APPEND ]

            [ NOT FOR REPLICATION ]

            AS

    DECLARE @Rows

    DECLARE ... any other variables you may need ...

        SET @Rows = @@ROWCOUNT

         IF @Rows = 0 RETURN

    ... other trigger code ...

    I'd still like to see the code that caused the original problem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 7, 2007 at 10:09 am

    #688333

    Thanks for looking at this!  I feel vulnerable to attacks pasting my code : ).  This is just an example with raiserror.  I assume the rest of my code is irrelevant since it does not get past the @@ROWCOUNT statement.

    I got the "IF @@ROWCOUNT = 0 RETURN" deal from looking at other examples and I've always wondered why it would be in the front since a trigger obviously means something was updated.  I'm guessing the code you pasted is from BOL, and that makes more sense to me.  I'm recoding it that way right now.

    /* begin code */

    SET ANSI_NULLS ON

    GO

    SET ANSI_WARNINGS ON

    GO

    ALTER  TRIGGER UPDATE_LOGS ON [dbo].[myTable]

    FOR INSERT, UPDATE

    AS

    IF @@ROWCOUNT = 0

    BEGIN

             RAISERROR('RowCount is 0', 16, 1)

             RETURN

    END

    /* end code */

  • February 7, 2007 at 4:13 pm

    #688422

    That should work but I haven't tried it that way...

    And, yep, the code I posted up to the "AS" was from Books OnLine... the rest is from some code that I had to write because the customer demanded such a thing... still can't figure that out but it does work.

    Understood about your posting the code... your example is a fine surrogate for that code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 8, 2007 at 12:03 am

    #688464

    Why feel 'vulnerable' when posting real code? Vulnerable to what? Without being cynical, show me an example.

  • February 8, 2007 at 6:32 am

    #688529

    Dunno about Juan, but some folks can be pretty tough on even unrelated code.  Lookup some of Celko's replies for an example of what I mean... the intentions are good but the methods used leave a little to be desired.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 8, 2007 at 9:49 am

    #688617

    "Or are you just a new, bad SQL programmer looking for some proprietary kludge to avoid the effort to learn to do it right?" 

    LOL, good lord!  This gentleman would toss me into Guantanamo!

    Anyway, I found the problem.  I was told, or so I have read, to set ANSI_NULLS and ANSI_WARNINGS after the AS.  I removed this when I posted my code as well as used your (Jeff) way of declaring and setting @rows, and it worked.  I suppose that those should not be set at that location.  Is this true even for distributed transactions (trigger that affects data on remote server)?

    Thanks again for the help and patience!

  • February 8, 2007 at 5:06 pm

    #688708

    Hi Jeff,

    To answer your question about why a trigger would fire if there were 0 records affected. If an INSERT/UPDATE/DELETE statement is run against a table that has no rows satisfying the WHERE clause, the trigger will still fire and there will be no rows in the inserted/deleted tables.

    I've seen this bite developers when they write code to pull individual values from the inserted tables and don't handle nulls. (I've also seen developers bitten when they assume there is only ever one row in INSERTED/DELETED, but I digress)

    Whenever I am unit testing trigger code, one test case is what happens if I execute 

    UPDATE myTable 
SET val = 'myValue'
WHERE 1=0

    SQL guy and Houston Magician

  • February 8, 2007 at 5:31 pm

    #688712

    Cool... Thanks for the info, Robert.  I never tested to see if a trigger fired on a zero row insert... they way I write most of my triggers, it probably wouldn't matter (I keep 'em very short and sweet).  Guess I have some testing to do. 

    I agree about the RBAR triggers... lot's of developers have been absolutely blown out of the water by that... most of them are folks used to programming in Oracle where all triggers are necessarily written as RBAR and then you add a FOR EACH ROW.  Very glad it's not that way in SQL Server...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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