Trigger Updating Same Table

[jfkuser]

Hi,

I have a Insert / Update Trigger on Table called TBL_A. Inside the trigger I am updating the same table TBL_A.

Will this cause recursion of the trigger? And will it keep on firing again and again?

Thanks in advance ...jfk

[MAK]

Nope. just runs one time.
example

create table x (id int, name char(10))

create trigger ins_tr on x for insert,update as
update a set a.name = b.name from x a,inserted b
where a.id =b.id
print 'inserted'

[Mike John]

Unless the database is configured with recursive triggers turned ON. ALTER DATABASE SET RECURSIVE_TRIGGERS. Default is off, so the answer is "it depends"!

[MAK]

Nope.

create table x (id int, name char(10))

alter trigger ins_tr on x for insert,update as
update a set a.name = b.name from x a,inserted b
where a.id =b.id
print 'inserted'


alter database tempdb set RECURSIVE_TRIGGERS OFF
insert into x select 110,'d'

alter database tempdb set RECURSIVE_TRIGGERS ON
insert into x select 110,'d'


Both cases trigger gets executed only once. if it is inserting in another table which has trigger - it will fire.