Are there any limitations or gotchas to updating the same table which
fired a trigger from within the trigger?

Some example code below. Hmmm.... This example seems to be working
fine so it must be something with my specific schema/code. We're
working on running a SQL trace but if anybody has any input, fire
away.

Thanks!


create table x
(
Id int,
Account varchar(25),
Info int
)
GO

insert into x values ( 1, 'Smith', 15);
insert into x values ( 2, 'SmithX', 25);

/* Update trigger tu_x for table x */
create trigger tu_x
on x
for update
as
begin
declare @TriggerRowCount int
set @TriggerRowCount = @@ROWCOUNT

if ( @TriggerRowCount = 0 )
return

if ( @TriggerRowCount > 1 )
begin
raiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT
[quoted text, click to view]
return
end

update x
set
Account = left( i.Account, 24) + 'X',
Info = i.Info
from deleted, inserted i
where x.Account = left( deleted.Account, 24) + 'X'
end

Your code will work fine, the UPDATE statement inside your trigger will not
cause the triggers to re-fire & hence you will see the data updated without
any problem.

However, you have to check & make sure your database property for
RECURSIVE_TRIGGERS is set to off (which is the default) so that it cannot be
fired recursively. The status of this option can be determined by examining
the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.
Refer to SQL Server Books Online for more details.

--
- Anith
( Please reply to newsgroups only )