@@ROWCOUNT = 0 in a trigger

I'm going over some triggers in an old database we have and have come
accross the following...
...
IF (@.ROWCOUNT = 0)
RETURN
...
Can a trigger ever fire if zero rows were affected? Should I remove this
from the trigger?
Thanks.>> Can a trigger ever fire if zero rows were affected?
Yes, the trigger will fire once the corresponding DML is invoked. The number
of rows has no impact on its execution.
As a recommended practice, you should not.
Anith|||> Can a trigger ever fire if zero rows were affected? Should I remove this
> from the trigger?
Yes it will be fired. Just be careful using "set nocount on" before
inquiring for @.@.rowcount.
Example:
create table t1 (
c1 int not null default(0)
)
go
create trigger tr_t1_ins on t1
for insert
as
set nocount on
print '(' + ltrim(@.@.rowcount) + ' row(s) inserted' + ')'
go
create trigger tr_t1_ins_1 on t1
for insert
as
print '(' + ltrim(@.@.rowcount) + ' row(s) inserted' + ')'
go
create trigger tr_t1_upd on t1
for update
as
print '(' + ltrim(@.@.rowcount) + ' row(s) updated' + ')'
go
create trigger tr_t1_del on t1
for delete
as
print '(' + ltrim(@.@.rowcount) + ' row(s) deleted' + ')'
go
insert into t1 default values
go
update t1
set c1 = 1
where c1 = 2
go
delete t1
where c1 = 2
go
drop table t1
go
AMB
"C-W" wrote:

> I'm going over some triggers in an old database we have and have come
> accross the following...
> ...
> IF (@.ROWCOUNT = 0)
> RETURN
> ...
> Can a trigger ever fire if zero rows were affected? Should I remove this
> from the trigger?
> Thanks.
>
>|||Thanks for the information,
Chris
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:u25d8jPnFHA.3480@.TK2MSFTNGP10.phx.gbl...
> I'm going over some triggers in an old database we have and have come
> accross the following...
> ...
> IF (@.ROWCOUNT = 0)
> RETURN
> ...
> Can a trigger ever fire if zero rows were affected? Should I remove this
> from the trigger?
> Thanks.
>|||As others mentioned, the trigger will fire once per the corresponding
statement. This includes 0, 1, >1 affected rows.
In some cases you may want to apply different logic depending on the number
of affected rows. @.@.rowcount is your best tool to achieve this:
IF @.@.rowcount - 0 RETURN;
IF @.@.rowcount = 1
BEGIN -- 1 affected row logic
..
END
ELSE
BEGIN -- >1 affected rows logic
..
END
BG, SQL Server MVP
www.SolidQualityLearning.com
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:u25d8jPnFHA.3480@.TK2MSFTNGP10.phx.gbl...
> I'm going over some triggers in an old database we have and have come
> accross the following...
> ...
> IF (@.ROWCOUNT = 0)
> RETURN
> ...
> Can a trigger ever fire if zero rows were affected? Should I remove this
> from the trigger?
> Thanks.
>