Trigger Archaeology: Mapping Nested and Recursive Trigger Chains

2026-11-03 · by Hannah Vernon · in t-sql

You’re asked to add a column to an orders table in a database that predates most of the team. You check the procedures that write to the table, and they look fine. Then you notice a trigger on the table. It writes to an audit table, which has its own trigger, which writes somewhere else. Nobody on the team knows where the chain ends.

A woman archaeologist brushes soil from buried stone tablets joined by a glowing chain that runs into deeper layers, loops back on itself, and branches into undug earth.

I’ve covered reading legacy procedures in Understanding Unfamiliar Code: Reverse-Engineering Legacy Procedures and procedure dependencies in Mapping Stored Procedure Dependencies with AI. For the triggers, I built a small chain on two local instances, wrote a query that maps it from the catalog, and then checked what the map missed.

Two Settings Control How Far a Chain Goes

The server option nested triggers decides whether an AFTER trigger can fire another trigger. At 1, the default, AFTER triggers can cascade up to 32 levels. At 0, they can’t cascade at all. INSTEAD OF triggers can nest regardless of this setting.^[1] It’s an instance-wide setting, so changing it affects every database on the instance. I didn’t change it for this post, and I’d leave it alone on an inherited server until the map is done.

The database option RECURSIVE_TRIGGERS controls something narrower. When it’s OFF, SQL Server prevents direct recursion only: a trigger that writes to its own table doesn’t fire itself again. For indirect recursion, where trigger A fires trigger B and B fires A, the only setting that stops it is nested triggers = 0.^[2]^[3]

Here’s how I checked both without changing them:

USE [sat_trigger_chains];

SELECT
      [c].[name]
    , [c].[value_in_use]
FROM
    [sys].[configurations] AS [c]
WHERE
    [c].[name] = N'nested triggers';

SELECT
      [d].[name]
    , [d].[is_recursive_triggers_on]
FROM
    [sys].[databases] AS [d]
WHERE
    [d].[name] = DB_NAME();

USE [sat_trigger_chains];

SELECT

[c].[name]

, [c].[value_in_use]

FROM

[sys].[configurations] AS [c]

WHERE

[c].[name] = N'nested triggers';

SELECT

[d].[name]

, [d].[is_recursive_triggers_on]

FROM

[sys].[databases] AS [d]

WHERE

[d].[name] = DB_NAME();

Both of my instances returned 1 for nested triggers and 0 for is_recursive_triggers_on.

The Test Chain

I used SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer) on one workstation, with a scratch database on each. Every trigger is an AFTER INSERT trigger:

orders fires trg_orders_ins, which inserts into order_audit.
order_audit fires trg_order_audit_ins, which inserts into fulfillment_queue.
fulfillment_queue fires trg_fulfillment_queue_ins, which inserts into notification_log. It also inserts into shadow_log through sp_executesql.
shadow_log fires trg_shadow_log_ins, which inserts into shadow_archive.
counter_chain has a trigger that inserts into counter_chain (direct recursion).
ping and pong have triggers that insert into each other (indirect recursion).
Two more recursive pairs, static_a/static_b and dynamic_a/dynamic_b, came later for the nesting-limit test. They’re described in that section.

The third trigger also writes to shadow_log through dynamic SQL, with the table name held in a variable:

CREATE TRIGGER [dbo].[trg_fulfillment_queue_ins]
ON [dbo].[fulfillment_queue]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @order_id   int;
    DECLARE @sql        nvarchar(400);
    DECLARE @target     sysname = N'shadow_log';

    INSERT INTO [dbo].[notification_log]
    (
          [order_id]
        , [nest_level]
        , [proc_nest]
    )
    SELECT
          [i].[order_id]
        , TRIGGER_NESTLEVEL()
        , @@NESTLEVEL
    FROM
        [inserted] AS [i];

    SELECT
        @order_id = MAX([i].[order_id])
    FROM
        [inserted] AS [i];

    SET @sql = N'INSERT INTO [dbo].' + QUOTENAME(@target)
        + N' ([order_id], [nest_level], [proc_nest]) VALUES (@order_id, TRIGGER_NESTLEVEL(), @@NESTLEVEL);';

    EXEC [sys].[sp_executesql]
          @sql
        , N'@order_id int'
        , @order_id = @order_id;
END;

CREATE TRIGGER [dbo].[trg_fulfillment_queue_ins]

ON [dbo].[fulfillment_queue]

AFTER INSERT

BEGIN

SET NOCOUNT ON;

DECLARE @order_id int;

DECLARE @sql nvarchar(400);

DECLARE @target sysname = N'shadow_log';

INSERT INTO [dbo].[notification_log]

(

[order_id]

, [nest_level]

, [proc_nest]

)

SELECT

[i].[order_id]

, TRIGGER_NESTLEVEL()

, @@NESTLEVEL

FROM

[inserted] AS [i];

SELECT

@order_id = MAX([i].[order_id])

FROM

[inserted] AS [i];

SET @sql = N'INSERT INTO [dbo].' + QUOTENAME(@target)

+ N' ([order_id], [nest_level], [proc_nest]) VALUES (@order_id, TRIGGER_NESTLEVEL(), @@NESTLEVEL);';

EXEC [sys].[sp_executesql]

@sql

, N'@order_id int'

, @order_id = @order_id;

END;

Step 1: Which Tables Have Triggers

sys.triggers gives the trigger and its parent table. sys.trigger_events gives the events it fires on. parent_class = 1 keeps DML triggers on tables and views and drops database-level DDL triggers.^[4]

SELECT
      OBJECT_SCHEMA_NAME([tr].[parent_id]) + N'.' + OBJECT_NAME([tr].[parent_id]) AS [table_name]
    , OBJECT_SCHEMA_NAME([tr].[object_id]) + N'.' + [tr].[name] AS [trigger_name]
    , [te].[type_desc] AS [event]
    , [tr].[is_instead_of_trigger]
    , [tr].[is_disabled]
FROM
    [sys].[triggers] AS [tr]
INNER JOIN
    [sys].[trigger_events] AS [te]
        ON [te].[object_id] = [tr].[object_id]
WHERE
    [tr].[parent_class] = 1
ORDER BY
      [table_name]
    , [trigger_name];

SELECT

OBJECT_SCHEMA_NAME([tr].[parent_id]) + N'.' + OBJECT_NAME([tr].[parent_id]) AS [table_name]

, OBJECT_SCHEMA_NAME([tr].[object_id]) + N'.' + [tr].[name] AS [trigger_name]

, [te].[type_desc] AS [event]

, [tr].[is_instead_of_trigger]

, [tr].[is_disabled]

FROM

[sys].[triggers] AS [tr]

INNER JOIN

[sys].[trigger_events] AS [te]

ON [te].[object_id] = [tr].[object_id]

WHERE

[tr].[parent_class] = 1

ORDER BY

[table_name]

, [trigger_name];

At that point it returned seven rows on both instances, one per trigger, each with the INSERT event.

Step 2: What Each Trigger Writes To

A DML trigger is tracked as a referencing entity, but never as a referenced one.^[5] No dependency row says “this table fires that trigger”. That link comes from sys.triggers.parent_id. The other direction, what the trigger touches, comes from sys.dm_sql_referenced_entities. Its is_updated column is 1 when the object or column is modified.^[5] Here is what it returned for trg_fulfillment_queue_ins on both instances:

referenced_entity_name  referenced_minor_name  is_selected  is_updated
----------------------  ---------------------  -----------  ----------
inserted                NULL                   1            0
inserted                order_id               1            0
notification_log        NULL                   0            1
notification_log        nest_level             0            1
notification_log        order_id               0            1
notification_log        proc_nest              0            1

referenced_entity_name referenced_minor_name is_selected is_updated

---------------------- --------------------- ----------- ----------

inserted NULL 1 0

inserted order_id 1 0

notification_log NULL 0 1

notification_log nest_level 0 1

notification_log order_id 0 1

notification_log proc_nest 0 1

notification_log is there, marked as updated. shadow_log isn’t. sys.sql_expression_dependencies gave the same answer. It records a dependency when a name appears in a persisted SQL expression.^[6] A table name built in a variable and run through sp_executesql isn’t in any expression the parser can see.

Step 3: Walk the Chain

With an edge list of table, trigger and written table, a recursive CTE can follow the chain from any starting table. The visited column stops the walk when a table repeats, so the CTE reports a recursive pair once instead of following it until the CTE’s own MAXRECURSION limit stops the query.

USE [sat_trigger_chains];

DECLARE @start_table nvarchar(256) = N'dbo.orders';

WITH [edges] AS
(
    SELECT DISTINCT
          [tr].[parent_id] AS [from_table_id]
        , [tr].[object_id] AS [trigger_id]
        , [re].[referenced_id] AS [to_table_id]
    FROM
        [sys].[triggers] AS [tr]
    CROSS APPLY
        [sys].[dm_sql_referenced_entities]
        (
              OBJECT_SCHEMA_NAME([tr].[object_id]) + N'.' + [tr].[name]
            , N'OBJECT'
        ) AS [re]
    WHERE
        [tr].[parent_class] = 1
        AND [tr].[is_disabled] = 0
        AND [re].[referenced_minor_id] = 0
        AND [re].[is_updated] = 1
        AND [re].[referenced_id] IS NOT NULL
)
, [chain] AS
(
    SELECT
          [e].[to_table_id]
        , 1 AS [depth]
        , CONVERT(nvarchar(4000), OBJECT_NAME([e].[from_table_id])
            + N' -[' + OBJECT_NAME([e].[trigger_id]) + N']-> '
            + OBJECT_NAME([e].[to_table_id])) AS [path]
        , CONVERT(nvarchar(4000), N'/' + CONVERT(nvarchar(11), [e].[from_table_id])
            + N'/' + CONVERT(nvarchar(11), [e].[to_table_id]) + N'/') AS [visited]
        , CONVERT(bit, CASE WHEN [e].[from_table_id] = [e].[to_table_id] THEN 1 ELSE 0 END) AS [is_cycle]
    FROM
        [edges] AS [e]
    WHERE
        [e].[from_table_id] = OBJECT_ID(@start_table)

    UNION ALL

    SELECT
          [e].[to_table_id]
        , [c].[depth] + 1
        , CONVERT(nvarchar(4000), [c].[path]
            + N' -[' + OBJECT_NAME([e].[trigger_id]) + N']-> '
            + OBJECT_NAME([e].[to_table_id]))
        , CONVERT(nvarchar(4000), [c].[visited] + CONVERT(nvarchar(11), [e].[to_table_id]) + N'/')
        , CONVERT(bit, CASE WHEN [c].[visited] LIKE N'%/' + CONVERT(nvarchar(11), [e].[to_table_id]) + N'/%' THEN 1 ELSE 0 END)
    FROM
        [chain] AS [c]
    INNER JOIN
        [edges] AS [e]
            ON [e].[from_table_id] = [c].[to_table_id]
    WHERE
        [c].[is_cycle] = 0
)
SELECT
      [c].[depth]
    , [c].[is_cycle]
    , [c].[path]
FROM
    [chain] AS [c]
ORDER BY
    [c].[path];

USE [sat_trigger_chains];

DECLARE @start_table nvarchar(256) = N'dbo.orders';

WITH [edges] AS

(

SELECT DISTINCT

[tr].[parent_id] AS [from_table_id]

, [tr].[object_id] AS [trigger_id]

, [re].[referenced_id] AS [to_table_id]

FROM

[sys].[triggers] AS [tr]

CROSS APPLY

[sys].[dm_sql_referenced_entities]

(

OBJECT_SCHEMA_NAME([tr].[object_id]) + N'.' + [tr].[name]

, N'OBJECT'

) AS [re]

WHERE

[tr].[parent_class] = 1

AND [tr].[is_disabled] = 0

AND [re].[referenced_minor_id] = 0

AND [re].[is_updated] = 1

AND [re].[referenced_id] IS NOT NULL

)

, [chain] AS

(

SELECT

[e].[to_table_id]

, 1 AS [depth]

, CONVERT(nvarchar(4000), OBJECT_NAME([e].[from_table_id])

+ N' -[' + OBJECT_NAME([e].[trigger_id]) + N']-> '

+ OBJECT_NAME([e].[to_table_id])) AS [path]

, CONVERT(nvarchar(4000), N'/' + CONVERT(nvarchar(11), [e].[from_table_id])

+ N'/' + CONVERT(nvarchar(11), [e].[to_table_id]) + N'/') AS [visited]

, CONVERT(bit, CASE WHEN [e].[from_table_id] = [e].[to_table_id] THEN 1 ELSE 0 END) AS [is_cycle]

FROM

[edges] AS [e]

WHERE

[e].[from_table_id] = OBJECT_ID(@start_table)

UNION ALL

SELECT

[e].[to_table_id]

, [c].[depth] + 1

, CONVERT(nvarchar(4000), [c].[path]

+ N' -[' + OBJECT_NAME([e].[trigger_id]) + N']-> '

+ OBJECT_NAME([e].[to_table_id]))

, CONVERT(nvarchar(4000), [c].[visited] + CONVERT(nvarchar(11), [e].[to_table_id]) + N'/')

, CONVERT(bit, CASE WHEN [c].[visited] LIKE N'%/' + CONVERT(nvarchar(11), [e].[to_table_id]) + N'/%' THEN 1 ELSE 0 END)

FROM

[chain] AS [c]

INNER JOIN

[edges] AS [e]

ON [e].[from_table_id] = [c].[to_table_id]

WHERE

[c].[is_cycle] = 0

)

SELECT

[c].[depth]

, [c].[is_cycle]

, [c].[path]

FROM

[chain] AS [c]

ORDER BY

[c].[path];

Results from three starting tables, identical on both instances:

start dbo.orders
depth  is_cycle  path
1      0         orders -[trg_orders_ins]-> order_audit
2      0         orders -[trg_orders_ins]-> order_audit -[trg_order_audit_ins]-> fulfillment_queue
3      0         orders -[trg_orders_ins]-> order_audit -[trg_order_audit_ins]-> fulfillment_queue -[trg_fulfillment_queue_ins]-> notification_log

start dbo.ping
1      0         ping -[trg_ping_ins]-> pong
2      1         ping -[trg_ping_ins]-> pong -[trg_pong_ins]-> ping

start dbo.counter_chain
1      1         counter_chain -[trg_counter_chain_ins]-> counter_chain

start dbo.orders

depth is_cycle path

1 0 orders -[trg_orders_ins]-> order_audit

2 0 orders -[trg_orders_ins]-> order_audit -[trg_order_audit_ins]-> fulfillment_queue

3 0 orders -[trg_orders_ins]-> order_audit -[trg_order_audit_ins]-> fulfillment_queue -[trg_fulfillment_queue_ins]-> notification_log

start dbo.ping

1 0 ping -[trg_ping_ins]-> pong

2 1 ping -[trg_ping_ins]-> pong -[trg_pong_ins]-> ping

start dbo.counter_chain

1 1 counter_chain -[trg_counter_chain_ins]-> counter_chain

The recursive pair and the self-referencing table both show up with is_cycle = 1. The orders chain stops at notification_log.

What the Map Missed

After one insert into orders, I read back the nest level each trigger recorded. The rows were the same on both instances:

table_name         TRIGGER_NESTLEVEL()  @@NESTLEVEL
order_audit        1                    (not recorded)
fulfillment_queue  2                    (not recorded)
notification_log   3                    3
shadow_log         3                    5
shadow_archive     4                    6

table_name TRIGGER_NESTLEVEL() @@NESTLEVEL

order_audit 1 (not recorded)

fulfillment_queue 2 (not recorded)

notification_log 3 3

shadow_log 3 5

shadow_archive 4 6

The insert wrote to five tables across four trigger levels, and the map only got me three of each. The fourth trigger, trg_shadow_log_ins, is one the map from orders never reached. I treat the map as the minimum set of tables a change can reach. Any trigger that builds SQL at run time needs to be read by hand. A search of sys.sql_modules.definition for sp_executesql and EXEC ( is a reasonable place to start.

@@NESTLEVEL counts every module on the stack, not only triggers. TRIGGER_NESTLEVEL() returns the number of triggers executed for the statement.^[7] Inside the dynamic SQL it stayed at 3, but @@NESTLEVEL went from 3 to 5. Inside sp_executesql, @@NESTLEVEL returns 2 plus the current level, and a plain EXEC () adds 1.^[8] To see whether those extra levels count against the limit, I built two more recursive pairs. One pair inserts with plain INSERT statements. The other inserts through sp_executesql. A control table capped how many triggers could fire, and I raised the cap one step at a time:

Pair	Highest cap that completed	Lowest cap that raised Msg 217
Static INSERT	32	33
Dynamic SQL	11	12

Both instances gave the same numbers. In this test, each dynamic-SQL trigger cost three nesting levels instead of one: the trigger, sp_executesql and the dynamic batch. With EXEC (), I’d expect one fewer, but I didn’t test that. So TRIGGER_NESTLEVEL() on its own can understate how close a chain is to the 32-level limit when dynamic SQL sits between the triggers.

What Happens at the Limit

With RECURSIVE_TRIGGERS OFF, one insert into counter_chain produced two rows: the original and one from the trigger. The trigger didn’t fire a second time.

The ping and pong pair is indirect recursion, so the OFF setting doesn’t stop it. An insert into ping failed on both instances:

Msg 217, Level 16, State 1, Procedure trg_pong_ins, Line 9
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

1 2	Msg 217, Level 16, State 1, Procedure trg_pong_ins, Line 9 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

I also ran that insert inside an explicit transaction, after inserting an unrelated orders row in the same transaction. In the CATCH block, XACT_STATE() returned -1 and @@TRANCOUNT returned 1. A value of -1 means the transaction is uncommittable and can only be rolled back in full.^[9] The transaction was still open, but doomed, so ROLLBACK was the only option, and it took the unrelated orders row with it. Microsoft’s documentation says a failure at any level of nested triggers cancels the entire transaction.^[3] I wrote about the related nested-transaction behaviour in A Nested COMMIT Does Not Commit Anything.

Then I set RECURSIVE_TRIGGERS ON for the scratch database and inserted into counter_chain again. It raised the same error 217, from trg_counter_chain_ins, and left zero rows in the table. I set the option back to OFF afterward and confirmed it with sys.databases.

A Guard With TRIGGER_NESTLEVEL

If you pass a trigger’s object ID, TRIGGER_NESTLEVEL returns how many times that trigger has run for the statement.^[7] Inside a trigger, @@PROCID is the trigger’s own object ID.^[10] I altered the recursive trigger to stop itself at five:

ALTER TRIGGER [dbo].[trg_counter_chain_ins]
ON [dbo].[counter_chain]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF TRIGGER_NESTLEVEL(@@PROCID) >= 5
    BEGIN
        RETURN;
    END;

    INSERT INTO [dbo].[counter_chain]
    (
        [nest_level]
    )
    VALUES
    (
        TRIGGER_NESTLEVEL()
    );
END;

ALTER TRIGGER [dbo].[trg_counter_chain_ins]

ON [dbo].[counter_chain]

AFTER INSERT

BEGIN

SET NOCOUNT ON;

IF TRIGGER_NESTLEVEL(@@PROCID) >= 5

BEGIN

RETURN;

END;

INSERT INTO [dbo].[counter_chain]

(

[nest_level]

)

VALUES

(

TRIGGER_NESTLEVEL()

);

END;

With RECURSIVE_TRIGGERS still ON, one insert produced five rows, with no error: my row plus four from the trigger (levels 1 to 4). The fifth execution hit the guard and returned. The map query still reports the cycle, because the guard is a runtime condition and the catalog has no record of it. So an is_cycle = 1 row means I still have to open the trigger and read it.

Before You Touch Anything

On an inherited database, the order I’d follow is:

Read nested triggers and is_recursive_triggers_on, and write them down.
Run the inventory query and note disabled triggers and INSTEAD OF triggers separately.
Run the chain query from each table you plan to change.
Search trigger definitions for dynamic SQL and procedure calls, and add those edges by hand.
Test the change on a restored copy, and check @@NESTLEVEL as well as TRIGGER_NESTLEVEL() in the deepest trigger.

I ran this on one workstation, on one build of each version, with AFTER INSERT triggers only. My chain query doesn’t compare the kind of write a trigger does with the event the next trigger fires on, so an AFTER UPDATE trigger on a table that only receives inserts would still appear as an edge. I didn’t test INSTEAD OF triggers, cross-database targets or CLR triggers. Dependency information isn’t kept for CLR triggers at all.^[5]

The chain query has gaps I didn’t test. The documentation says sys.dm_sql_referenced_entities returns error 2020 when a referenced object doesn’t exist, which may stop the whole query on a database with broken references.^[5] A trigger that calls a procedure produces a row with a NULL referenced_id, which my filter drops. Trigger names with dots or brackets need QUOTENAME. Cascading foreign-key actions that fire triggers aren’t in the map either.

Related: Documenting ETL Procedure Data Flow from SQL Server Metadata (available 2026-11-13).

If you’ve mapped trigger chains in a legacy database and found a gap I didn’t cover, I’d like to hear about it. Find me on Bluesky or LinkedIn.

References

Server Configuration: nested triggers - Microsoft Learn. States that the option controls whether AFTER triggers can cascade, that the default of 1 allows up to 32 levels, that 0 prevents cascading, and that INSTEAD OF triggers nest regardless of the setting. ↩
ALTER DATABASE SET options (Transact-SQL) - Microsoft Learn. Describes RECURSIVE_TRIGGERS, notes that OFF prevents only direct recursion and that indirect recursion needs the nested triggers server option set to 0. ↩
Create Nested Triggers - Microsoft Learn. Defines direct and indirect recursion, gives the 32-level limit, and states that a failure at any level of nested triggers cancels the entire transaction. ↩
sys.triggers (Transact-SQL) - Microsoft Learn. Defines parent_class, where 0 is a DDL trigger on the database and 1 is a trigger on an object or column. ↩
sys.dm_sql_referenced_entities (Transact-SQL) - Microsoft Learn. Defines is_updated, and its entity table shows Transact-SQL DML triggers as referencing but not referenced entities, and CLR triggers as neither. ↩
sys.sql_expression_dependencies (Transact-SQL) - Microsoft Learn. States that a dependency is created when the referenced entity appears by name in a persisted SQL expression of the referencing entity. ↩
TRIGGER_NESTLEVEL (Transact-SQL) - Microsoft Learn. Returns the number of triggers executed for the statement, or with an object_id, the number of times that trigger has executed. ↩
@@NESTLEVEL (Transact-SQL) - Microsoft Learn. States that inside sp_executesql the value returned is 2 plus the current nesting level, and inside a Transact-SQL string it is 1 plus the current level. ↩
XACT_STATE (Transact-SQL) - Microsoft Learn. Defines -1 as an active transaction classified as uncommittable, which can only be rolled back in full. ↩
@@PROCID (Transact-SQL) - Microsoft Learn. Returns the object identifier of the current Transact-SQL module, which can be a trigger. ↩

Tags: dependencies, legacy-code, SQL Server, SQL Server 2025, sql-server-2019, t-sql, triggers

Trigger Archaeology: Mapping Nested and Recursive Trigger Chains

Two Settings Control How Far a Chain Goes

The Test Chain

Step 1: Which Tables Have Triggers

Step 2: What Each Trigger Writes To

Step 3: Walk the Chain

What the Map Missed

What Happens at the Limit

A Guard With TRIGGER_NESTLEVEL

Before You Touch Anything

References

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta

Trigger Archaeology: Mapping Nested and Recursive Trigger Chains

Two Settings Control How Far a Chain Goes

The Test Chain

Step 1: Which Tables Have Triggers

Step 2: What Each Trigger Writes To

Step 3: Walk the Chain

What the Map Missed

What Happens at the Limit

A Guard With TRIGGER_NESTLEVEL

Before You Touch Anything

References

Share this:

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta