Recursive Update trigger issue in SQL 2005

Question

Below is the code snippet with comments which describes the problem statement. We have an update trigger which internally calls another update trigger on the same table inspite of Recursive Trigger Enabled Property Set to false.

Would like to understand the reason for this as this is causing a havoc in my applications.

/* Drop statements for the table and triggers*/  

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].   [t_upd_TestTrigger_002]'))
    DROP TRIGGER [dbo].[t_upd_TestTrigger_002]
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[t_upd_TestTrigger_002]'))
    DROP TRIGGER [dbo].[t_upd_TestTrigger_001]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTrigger]') AND type in (N'U'))
    DROP TABLE [dbo].[TestTrigger]


CREATE TABLE [dbo].[TestTrigger] /*Creating a test table*/
(
    [InternalKey] INT  NOT NULL,
    [UserModified] varchar(50) DEFAULT SUSER_SNAME()
) 


/* Please run the snippet below as seperate batch, else you will get 
   an error that 'CREATE TRIGGER' must be the first statement in a 
   query batch.

   CREATING A UPDATE TRIGGER FOR THE TEST TABLE
*/

CREATE TRIGGER [t_upd_TestTrigger_001] ON [dbo].[TestTrigger]
FOR UPDATE
AS
BEGIN
    --This trigger has some business logic which gets executed 
    print 'In Trigger 001 '
END

/* Please run the snippet below as separate batch, else you will 
   get an error that 'CREATE TRIGGER' must be the first statement 
   in a query batch.

   CREATING Another UPDATE TRIGGER FOR THE TEST TABLE 

   This trigger updates the audit fields in the table and it has to be 
   a separate trigger, We cannot combine this with other update triggers - 
   So table TestTrigger will have two FOR UPDATE triggers
*/


CREATE TRIGGER [t_upd_TestTrigger_002] ON [dbo].[TestTrigger]
FOR UPDATE
AS
    print 'bad guy starts'
UPDATE SRC
    SET UserModified = SUSER_SNAME()
    FROM inserted AS INS
    INNER JOIN dbo.[TestTrigger] AS SRC
        ON INS.InternalKey = SRC.InternalKey
        print 'bad guy ends'

/* INSERTING TEST VALUE IN THE TEST TRIGGER TABLE*/

INSERT INTO dbo.[TestTrigger](InternalKey,UserModified)
SELECT 1 ,'Tester1'  UNION ALL
SELECT 2,'Tester2' UNION ALL 
SELECT 3 ,'Tester3'

/* TestTrigger table has 3 records, we will update the InternalKey 
   of first record from 1 to 4.  We would expect following actions
   1) [t_upd_TestTrigger_001] to be executed once
   2) [t_upd_TestTrigger_002] to be executed once
   3) A message that (1 row(s) affected) only once.

   On Execution, i find that [t_upd_TestTrigger_002] internally triggers 
   [t_upd_TestTrigger_001].

   Please note Database level property Recursive Triggers enabled is 
   set to false.
*/

/*UPDATE THE TABLE  SEE THE MESSAGE IN RESULT WINDOW*/
UPDATE dbo.[TestTrigger]
SET InternalKey = 4
WHERE InternalKey = 1

Answer 1

"Recursive Triggers enabled" does not affect transitive triggers.

Which means that if trigger A updates a table in a manner that activates trigger B, and trigger B updates the same table, so that trigger A is run again, SQL Server has no way of detecting and inhibiting this endless loop. Especially since trigger B can update other tables, and a trigger on them could update the original table again - this could become as complex as you like.

Eventually, the trigger nesting level limit will be reached, and the loop stops.

I suspect that both of your triggers update the source table in some way. SQL Server can only detect recursive triggers if a trigger is activating itself. I suppose that's not the case for you. Restructuring the triggers is the only clean way out.

As a (hackery) idea: You could append a field to the table (data-type and value is irrelevant) that is updated by no operation but by triggers. Then change your second-order triggers to update that field. Add an IF UPDATE() check for that field to your first-order trigger. Prevent the now redundant update if the field has been set. If that makes sense. ;-)

MSDN: Using Nested Triggers, see sections "Direct recursion" and "Indirect recursion".

Answer 2

You can use IF UPDATE(), as Tomalak described, to skip trigger logic if UserModified is being updated.

Another possibility is to move the UserModified column to a separate table to avoid recursion.