December 15, 2003 at 3:46 pm
The @@rowcount is not being set when I set the nocount option on. I have a trigger that contains the following code in the beginning and this causes the main body of the trigger to be skipped.
select @numrows = @@rowcount
if @numrows = 0
return
Can anyone explain what is wrong? According to BOL, "The @@ROWCOUNT function is updated even when SET NOCOUNT is ON."
December 15, 2003 at 5:34 pm
The @@rowcount will not have a value at that beginning stage of your trigger (ie. it IS set by statement that fired the trigger, but is not visible within the trigger).
You'll have to do a select count(*) from inserted (or deleted) in order to make your decision to bypass.
Cheers,
- Mark
Cheers,
- Mark
December 15, 2003 at 9:07 pm
Just an aside. You would have to have some roles affected for the trigger to be fired, would you not? You could never have a count(*) = 0 from both inserted and deleted.
December 15, 2003 at 10:48 pm
No, the trigger will still fire if no rows are affected. Eg.
create table test1 (id int)
go
create trigger trtest1
on test1
for delete, insert, update
as
begin
declare @i int
select @i = count(*) from inserted
raiserror ('rows inserted/updated = %d', 0, 1, @i)
end
go
update test1 set id = id where 1 = 2
result from the above is:
rows inserted/updated = 0
(0 row(s) affected)
Cheers,
- Mark
Cheers,
- Mark
December 16, 2003 at 9:26 am
Mark
quote:
The @@rowcount will not have a value at that beginning stage of your trigger (ie. it IS set by statement that fired the trigger, but is not visible within the trigger).
Are you sure? I've alwyas understood that @@rowcount is available at the beginning of a trigger, and will be set to the number of rows affected by the underlying Insert, Update or Delete statement.
I've always used @@rowcount to exit from the trigger if nothing has been done, and haven't had any problems.
As soon as you do any other command, even a simple assignment of a local variable, @@rowcount is reset accordingly.
December 16, 2003 at 7:24 pm
Ian,
You are right.
Linda
My mistake and apologies. In your trigger, are there any statements prior to your checking of @@rowcount? Most statements affect it.
Cheers,
- Mark
Cheers,
- Mark
December 22, 2003 at 7:43 am
The "select @numrows = @@rowcount" is the second statement in the trigger. "SET NOCOUNT ON" is the first statement. Is it happening because the SET NOCOUNT ON is executed inside the trigger?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply