how to debug a recursive trigger

Question

I have a recursive trigger, that seems to do exactly what I want it to with no recursion, however when I turn recursion on I get the error: "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)"

This should not happen, as I expect 2 or maybe 3 levels of nesting, so I need to debug the trigger and work out what exactly is going on. I added a print statement, but that does not work...

How do you go about debugging a recursive trigger?

ALTER TRIGGER [dbo].[DataInstance2_Trigger] 
   ON  [dbo].[DataInstance]  
AFTER UPDATE
AS
BEGIN 
  UPDATE DataInstance
  SET
    DataInstance.IsCurrent = i.IsCurrent
    FROM DataInstance di
    Inner join DataContainer dc on
        di.DataContainerId = dc.DataContainerId
    Inner join Inserted i on
        dc.ParentDataContainerId = i.DataContainerId    
    WHERE di.IsCurrent != i.IsCurrent     

    declare @x int
    SET @X = (select max(DataContainerId) from Inserted)
    print @X

END

Answer 1

You can include a SELECT statement in your trigger (I've just tried this - try SELECT * FROM DataInstance prior to the UPDATE in the trigger).

I've repro'ed the problem - the UPDATE in the trigger is causing the trigger to fire again even if there are no rows updated. A suitable fix would be to wrap the UPDATE statement within the trigger in an IF (SELECT COUNT(*) FROM INSERTED) <> 0 BEGIN ... END block.