When a trigger on one table updates another table, does it activate
the update trigger on the updated table?

For ex:

1. Table A has a trigger for update
2. Table B has a trigger for update that updates table A

Will the trigger no table A run when table B trigger updates table A?

Thanks

Hi

If you update table B the trigger will update table A and the trigger on
table A will be activated. You will have to be careful that the actions you
perform within the do what you expect. There are 32 levels of recursion.

Check out Using Nested Triggers in Books Online.

John
"Yariv" <yariva@mll.co.il> wrote in message
news:b4e534ce.0310080112.14d7aa18@posting.google.c om...
> When a trigger on one table updates another table, does it activate
> the update trigger on the updated table?
>
> For ex:
>
> 1. Table A has a trigger for update
> 2. Table B has a trigger for update that updates table A
>
> Will the trigger no table A run when table B trigger updates table A?
>
> Thanks

John Bell (jbellnewsposts@hotmail.com) writes:
> If you update table B the trigger will update table A and the trigger on
> table A will be activated. You will have to be careful that the actions
> you perform within the do what you expect. There are 32 levels of
> recursion.

I'll add here, that is depends on the configuration option "nested
triggers". By defualt it is on. However, it is a little too easy to
turn it off in the Enterprise Manager. (Been there, done that.)

Note that if the trigger updates the table the trigger belongs to,
the default is that the trigger is *not* re-fired. This depends on
a database setting, RECURSIVE_TRIGGERS.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp