This might seem really simple or silly.....

If I set up an UPDATE trigger against a table (eg. Table A) and the T-SQL
code goes off to find some value from another table (eg. Table B), which is
used to update a field  in the original table (Table A), does the trigger
get fired again when the second update occurs?

If so I can see a vicious loop occuring.

Hope this makes sense.

TIA

Tim

Tim Bryant wrote:
    This depends on the server option called 'recursive triggers'. I think
it is off by default, and can be set with sp_dpoption.
    In any case, you can always perform a check against a specific column
being updated inside the trigger with IF UPDATE (col_name).

The default is ON. Perhaps you want to check the TRIGGER_NESTLEVEL()
Function to see where you are stuck in the trigger.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

If it's an instead of update trigger, then definitely no.  If it's a for
update or an after update trigger, then it depends on the recursive triggers
option.  I prefer to use instead of triggers whenever possible because you
can cause the table to be updated only once per update statement, and you
can prevent changes to rowversions for an update that doesn't actually
change anything, which reduces the number of exceptions that have to be
handled at the client.  For example, if an update sets a value to be the
same as the original value (if deleted and inserted are identical), then
there's often no need to bump the rowversion.  If the rowversion is
different, then it appears to the client that the data has changed, and the
client must then perform recovery to deal with that.  Obviously, this
shouldn't happen unless the data has really changed, so preventing updates
that don't actually change anything is a good thing.


Show quoteHide quote

Optimal storage of a lot of text records
T Sql Help
Top N worst groups SQL problem
SQL Server 2000 Automatic Setup Script.
Which primary key