How to write a recursive trigger

Question

I have written an update trigger, that does an update that could potentially call itself (which is the behavior I want).

Unfortunately, The trigger does not call itself, and I cant see why. This trigger is simply changing a value of any child records (based on a Parent key), and then I expect on that child being updated the trigger should be fired again, and any child record for that child be updated, etc, etc.

I can manually run the same update the trigger successfully completes, and the child records get set as I expect.

I have run EXEC sp_configure 'nested triggers' and that has returned

minimum:0, maximum:1, config_value:1, run_value:1, so recursion seems to be turned on.

ALTER TRIGGER [dbo].[DataInstance_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               

END

Answer 1

Apart from the server-wide config, you can also disable triggers per database:

ALTER DATABASE databasename SET RECURSIVE_TRIGGERS ON | OFF

Or through Database Properties -> Options -> Miscellaneous -> Recursive Triggers Enabled.

If that checks out, verify that your first trigger actually updates something. I usually use a log table:

insert into dbo.LogTable (txt) values ('Updated ' + 
    cast(IsNull(@@ROWCOUNT,-1) as varchar(25)) + ' rows.')

For debugging, you can also use print:

print 'Updated ' + cast(IsNull(@@ROWCOUNT,-1) as varchar(25)) + ' rows.'