raul garcia zamarra (ra****@ozu.es) writes:
I activate execution plan and
this window write nine updates
on rows when I update the same
table in trigger after update.
I'm not really sure what you mean here, but it does not sound like anything
to worry about. Since you have not given a full repro for your case, it
is a little difficult to run an example myself, to see what you are
talking about. But at least, that is not a token of a recursive trigger.
I don`t change recursive_trigg ers to false
because this change affect all triggers in
my database sql server.
Just to clear things out: there are *two* triggers settings in SQL
Server.
One is *nested triggers*. This is a server-wide configuration option
that is *on* by default. This option says that if you in an trigger
perform an INSERT/DELETE/TABLE on *another* table, any triggers on
that table will fire. This setting does not control what happens
if you update the table to which the trigger belongs.
The other setting is RECURSIVE_TRIGG ERS, and this is a database-wide
setting, which is *off* by default. When this setting is active,
if you update the same table in a trigger, the trigger will fire
anew.
Do you really have RECURSIVE_TRIGG ERS on? And do you really need to?
Recursive triggers are quite and advanced concept, as you must have
a way to stop recursion. There is a simple way though, if you want a
particular trigger to be non-recursive:
if trigger_nestlev el(@@procid) > 1
return
--
Erland Sommarskog, SQL Server MVP,
so****@algonet. se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
