Using RowCount and Return within a Trigger

Question

Using RowCount and Return within a Trigger

AVB

SSChampion

Points: 11418
More actions
November 3, 2009 at 2:07 pm

#96470

I was purusing thru an old book I had and it had the RETURN Statement and @@RowCount used within a Trigger. I've attempted to use both without any results. Below are a few examples used.
My trigger is a "FOR UPDATE" trigger.
If @@ROWCOUT = 0 BEGIN Print 'No Rows updated' RETURN END
or
IF @@RowCount > 1 BEGIN Print 'Mult-Row updates are not allowed.' RETURN END
If I Print @@RowCount it shows 0 although two rows were updated. Return also does nothing. If I don't include a Rollback statment the update still occurs.
Any ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

@@Rowcount only works on the last command within the scope of the code. Thus, unless the trigger itself is updating something, it will return 0. What you need to do to get the number of rows affected by the update that's causing the trigger to fire, is select the count from the "inserted" table.

select count(*)

from inserted;

All "Return" does is end the code in the trigger. It doesn't block the update that caused the trigger to fire. You are correct that you would have to use a rollback command to accomplish that.

Edit: After a second look, I think what you're looking for is something like this:

create trigger MyTrigger on dbo.MyTable

after update

as

set nocount on;

if (select count(*)

from inserted) > 0

begin

rollback;

raiserror('Multi-row updates are not allowed', 16, 1);

end;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

AVB SSChampion Points: 11418 More actions · Answer 2

GSquared,

Thanks for the reply. I figured I could achive it with the Count(*) I just didn't know if there was another way.

I actually had another question too...it's is off topic but related to triggers and this problem. I posted it here: http://www.sqlservercentral.com/Forums/Topic813349-8-1.aspx

I've already got a solution to this problem however utilizing this same logic what would you do?

GSquared SSC Guru Points: 260824 More actions · Answer 3

I posted a reply to that one in that thread (to keep it all in one place).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon