Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


@@rowcount Returns 0


@@rowcount Returns 0

Author
Message
JuanBob
JuanBob
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 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


David Webb-CDS
David Webb-CDS
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 8584

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
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5852 Visits: 11417
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)
Paul Mu
Paul Mu
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 391

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.


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45494 Visits: 39948

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45494 Visits: 39948

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47435 Visits: 44405

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


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45494 Visits: 39948

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JuanBob
JuanBob
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 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 */


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45494 Visits: 39948

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search