My naive recursive trigger in SQL Server died at nest level 32 with Msg 217 and took the INSERT with it. A recursive trigger fires itself again, directly (it updates its own table) or indirectly (A updates B, B updates A).
Direct recursion needs ALTER DATABASE ... SET RECURSIVE_TRIGGERS ON. Indirect recursion needs only the default nested triggers = 1. Here’s what we’re building, a team-size rollup up an org chart, checked against a recursive CTE:
Before You Start: Test Setup and the Two Recursion Settings
Test setup: SQL Server 2025 (17.0.1000.7) Standard Developer, sqlcmd in VS Code. I only read the server option; it’s instance-wide. If you’re not sure what you run, check your SQL Server version first:
SET NOCOUNT ON;
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsRecursiveTriggersEnabled') AS RecursiveTriggers,
(SELECT CAST(value_in_use AS int) FROM sys.configurations
WHERE name = 'nested triggers') AS NestedTriggers;sqlcmd printed:
RecursiveTriggers NestedTriggers
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------
0 1What this tells me: a new database starts with recursion off, and nesting is on. TeamSize counts everyone below a person, at every level:
SET NOCOUNT ON;
CREATE TABLE dbo.Employees (
EmpID int PRIMARY KEY,
FullName nvarchar(16) NOT NULL,
ManagerID int NULL REFERENCES dbo.Employees (EmpID),
TeamSize int NOT NULL DEFAULT 0
);
INSERT INTO dbo.Employees VALUES
(1, N'Hannah Brooks', NULL, 6), (2, N'Marcus Bennett', 1, 2),
(3, N'Grace Miller', 1, 2), (4, N'Tyler Reed', 2, 1),
(5, N'Olivia Carter', 3, 1), (6, N'Ethan Walsh', 4, 0),
(7, N'Chloe Ramirez', 5, 0);Step 1: Create the AFTER INSERT and AFTER UPDATE Triggers
The insert trigger credits the manager. The AFTER UPDATE trigger pushes each change one level up its own table, so it wants to fire itself:
CREATE TRIGGER dbo.trg_Employees_NewHire ON dbo.Employees
AFTER INSERT
AS
UPDATE m SET TeamSize = m.TeamSize + n.Hires
FROM dbo.Employees AS m
JOIN (SELECT ManagerID, COUNT(*) AS Hires
FROM inserted GROUP BY ManagerID) AS n ON n.ManagerID = m.EmpID;
GO
CREATE TRIGGER dbo.trg_Employees_Rollup ON dbo.Employees
AFTER UPDATE
AS
UPDATE m SET TeamSize = m.TeamSize + x.Delta
FROM dbo.Employees AS m
JOIN (SELECT i.ManagerID, SUM(i.TeamSize - d.TeamSize) AS Delta
FROM inserted AS i JOIN deleted AS d ON d.EmpID = i.EmpID
GROUP BY i.ManagerID) AS x ON x.ManagerID = m.EmpID
WHERE x.Delta <> 0;Step 2: Hire Someone With RECURSIVE_TRIGGERS OFF
SET NOCOUNT ON;
ALTER DATABASE CURRENT SET RECURSIVE_TRIGGERS OFF;
GO
INSERT INTO dbo.Employees VALUES (8, N'Noah Fischer', 4, 0);
SELECT EmpID, FullName, TeamSize FROM dbo.Employees
WHERE EmpID IN (1, 2, 4) ORDER BY EmpID;Result:
EmpID FullName TeamSize
----------- ---------------- -----------
1 Hannah Brooks 6
2 Marcus Bennett 3
4 Tyler Reed 2
Checkpoint: you should see Tyler at 2 and Marcus at 3, but Hannah stuck at 6. The AFTER INSERT trigger firing the update trigger is nesting, so it ran. The update trigger firing itself is direct recursion, so OFF blocked it.
Step 3: Turn RECURSIVE_TRIGGERS ON and Hit Msg 217
Same hire, wrapped like a TRY CATCH in a stored procedure:
SET NOCOUNT ON;
ALTER DATABASE CURRENT SET RECURSIVE_TRIGGERS ON;
GO
BEGIN TRY
INSERT INTO dbo.Employees VALUES (8, N'Noah Fischer', 4, 0);
END TRY
BEGIN CATCH
PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE());
END CATCH;
SELECT COUNT(*) AS Employees FROM dbo.Employees;What came back:
Msg 217: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Employees
-----------
7
Checkpoint: 7 rows, so the INSERT rolled back. A scratch copy that logged each firing showed levels 2-4 working, then 5-32 each updating zero rows. WHERE x.Delta <> 0 didn’t help: AFTER UPDATE fires even when no rows change.
Step 4: Guard the Trigger With ROWCOUNT_BIG and TRIGGER_NESTLEVEL
Leave when the statement that fired the trigger changed nothing. TRIGGER_NESTLEVEL() in a PRINT lets you watch the depth:
CREATE OR ALTER TRIGGER dbo.trg_Employees_Rollup ON dbo.Employees
AFTER UPDATE
AS
IF ROWCOUNT_BIG() = 0 RETURN;
SET NOCOUNT ON;
PRINT CONCAT('Rollup at nest level ', TRIGGER_NESTLEVEL());
UPDATE m SET TeamSize = m.TeamSize + x.Delta
FROM dbo.Employees AS m
JOIN (SELECT i.ManagerID, SUM(i.TeamSize - d.TeamSize) AS Delta
FROM inserted AS i JOIN deleted AS d ON d.EmpID = i.EmpID
GROUP BY i.ManagerID) AS x ON x.ManagerID = m.EmpID
WHERE x.Delta <> 0;Note: the guard must come before SET NOCOUNT ON. Unlike the inserted table, the row count resets on every SET.
Test the Recursive Trigger Against a Recursive CTE
Three hires in one INSERT, checked by a recursive CTE:
SET NOCOUNT ON;
INSERT INTO dbo.Employees VALUES
(8, N'Noah Fischer', 4, 0), (9, N'Ava Coleman', 5, 0),
(10, N'Liam Porter', 6, 0);
WITH r AS (SELECT EmpID AS Boss, EmpID FROM dbo.Employees
UNION ALL
SELECT r.Boss, e.EmpID FROM r
JOIN dbo.Employees AS e ON e.ManagerID = r.EmpID)
SELECT e.FullName, e.TeamSize, COUNT(*) - 1 AS Counted
FROM r JOIN dbo.Employees AS e ON e.EmpID = r.Boss
GROUP BY e.EmpID, e.FullName, e.TeamSize
HAVING COUNT(*) > 1 ORDER BY e.EmpID;Output:
Rollup at nest level 2
Rollup at nest level 3
Rollup at nest level 4
Rollup at nest level 5
FullName TeamSize Counted
---------------- ----------- -----------
Hannah Brooks 9 9
Marcus Bennett 4 4
Grace Miller 3 3
Tyler Reed 3 3
Olivia Carter 2 2
Ethan Walsh 1 1Every TeamSize equals Counted. Level 6 returned before its PRINT.
Is Indirect Recursion Blocked When RECURSIVE_TRIGGERS Is OFF?
No. Here two tables sync a balance, each with a trigger that writes to another table:
SET NOCOUNT ON;
CREATE TABLE dbo.Billing (Id int PRIMARY KEY, Balance decimal(10,2));
CREATE TABLE dbo.Crm (Id int PRIMARY KEY, Balance decimal(10,2));
INSERT INTO dbo.Billing VALUES (101, 250.00);
INSERT INTO dbo.Crm VALUES (101, 250.00);
GO
CREATE TRIGGER dbo.trg_Billing_Sync ON dbo.Billing
AFTER UPDATE
AS
UPDATE t SET Balance = i.Balance
FROM dbo.Crm AS t JOIN inserted AS i ON i.Id = t.Id
WHERE t.Balance <> i.Balance;
GO
CREATE TRIGGER dbo.trg_Crm_Sync ON dbo.Crm
AFTER UPDATE
AS
UPDATE t SET Balance = i.Balance
FROM dbo.Billing AS t JOIN inserted AS i ON i.Id = t.Id
WHERE t.Balance <> i.Balance;SET NOCOUNT ON;
ALTER DATABASE CURRENT SET RECURSIVE_TRIGGERS OFF;
GO
BEGIN TRY
UPDATE dbo.Billing SET Balance = 310.00 WHERE Id = 101;
END TRY
BEGIN CATCH
PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE());
END CATCH;
SELECT b.Balance AS Billing, c.Balance AS Crm
FROM dbo.Billing AS b JOIN dbo.Crm AS c ON c.Id = b.Id;Result:
Msg 217: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Billing Crm
------------ ------------
250.00 250.00
Microsoft’s docs say OFF prevents only direct recursion; nested triggers = 0 stops the rest, server-wide. I fixed one trigger instead:
CREATE OR ALTER TRIGGER dbo.trg_Crm_Sync ON dbo.Crm
AFTER UPDATE
AS
IF TRIGGER_NESTLEVEL() > 1 RETURN;
UPDATE t SET Balance = i.Balance
FROM dbo.Billing AS t JOIN inserted AS i ON i.Id = t.Id
WHERE t.Balance <> i.Balance;
GO
SET NOCOUNT ON;
UPDATE dbo.Billing SET Balance = 310.00 WHERE Id = 101;
SELECT Id, Balance AS CrmBalance FROM dbo.Crm;Output:
Id CrmBalance
----------- ------------
101 310.00Troubleshooting: Recursive Trigger Problems I Hit
- Rollup stopped at Tyler: my first guard sat after
SET NOCOUNT ON. - Pass deltas so multi-row inserts add up (see checking if a value changed).
- An INSTEAD OF trigger never recurses on its own table, per Microsoft.
My Takeaways on Recursive Triggers in SQL Server
- Keep RECURSIVE_TRIGGERS off unless you need it on purpose.
- Recursive and ping-pong triggers need a zero-row exit.
TRIGGER_NESTLEVEL()stops indirect loops without touching server settings.
With nested triggers = 0, it all changes: no rollup in Step 2, no indirect loop.
Where to Take It Next With SQL Server Triggers
Next step: debug a SQL Server trigger when one misbehaves. On MariaDB, see MariaDB CREATE TRIGGER.
Related:
- SQL Server trigger basics
- Fire only when a column changes
- A ROLLBACK error: Msg 3609
- Disable a trigger
Frequently Asked Questions
Is RECURSIVE_TRIGGERS on by default in SQL Server?
No. New databases copy model, where it’s off. The ALTER DATABASE SET options page also lists sys.databases.is_recursive_triggers_on.
Why didn’t SET RECURSIVE_TRIGGERS ON take effect?
The ALTER sat in the INSERT’s batch, and on my run the trigger still acted as OFF. A GO after the ALTER fixed it.
How do I turn off nested triggers in SQL Server?
sp_configure 'nested triggers', 0; then RECONFIGURE;. It’s server-wide and, per CREATE TRIGGER, disables recursive triggers too.
Shivam Dogra is a cloud database developer and Team Lead at TSInfo Technologies, where he has worked since June 2021. He writes the SQL Server and Oracle tutorials on DatabaseFAQs.com, covering queries, joins, stored procedures, triggers and permissions. Shivam holds a Master of Computer Applications (MCA) from Chandigarh University.