SQL Server 2005 - Trigger Loop?

Question

I am using triggers for the first time.

If I update a field in a table by an update trigger on the same table, with this spark a loop? Does sql server guard against this recursive behavior?

Thanks

Answer 1

This page (search for RECURSIVE_TRIGGERS) describes some of the database settings you can use to modify this behavior. Also, one way to safeguard your procedures is to use either the UPDATE() function or the COLUMNS_UPDATED() function.

If, for example, you have a table with columns A, B, and C, and you want the value of C to change automagically when the value in column B is updated, you can protect the call in the trigger:

CREATE TRIGGER Whatever ON TableName AFTER UPDATE
AS
BEGIN
    IF UPDATE(B)
    BEGIN
        /* Update column C here */
    END
END

This way you avoid calling the trigger recursively when column C is updated by your trigger. COLUMNS_UPDATED() is also useful, but I find it to be fragile (relies on position of column instead of column name).

Answer 2

You can control recursion of triggers at the DB level via the RECURSION_TRIGGER option; it's turned off by default. Even if this option is turned on, there is a limit of 32 nested levels of triggers; all changes will be rolled back if your exit condition didn't stop the recursion before reaching the limit of 32 levels.