Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

@@rowcount Returns 0 Expand / Collapse
Author
Message
Posted Tuesday, February 6, 2007 4:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 8, 2012 7:21 PM
Points: 234, Visits: 269

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

Post #342959
Posted Tuesday, February 6, 2007 5:11 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:12 PM
Points: 867, Visits: 7,563

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
Post #342964
Posted Tuesday, February 6, 2007 5:14 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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)
Post #342965
Posted Tuesday, February 6, 2007 5:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:02 AM
Points: 339, Visits: 390

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.

Post #342966
Posted Tuesday, February 6, 2007 9:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:11 PM
Points: 35,769, Visits: 32,432

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #342985
Posted Tuesday, February 6, 2007 9:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:11 PM
Points: 35,769, Visits: 32,432

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #342986
Posted Tuesday, February 6, 2007 11:47 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053

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 2008, MVP
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

Post #343001
Posted Wednesday, February 7, 2007 7:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:11 PM
Points: 35,769, Visits: 32,432

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #343094
Posted Wednesday, February 7, 2007 10:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 8, 2012 7:21 PM
Points: 234, Visits: 269

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

Post #343169
Posted Wednesday, February 7, 2007 4:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:11 PM
Points: 35,769, Visits: 32,432

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #343284
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse