TRIGGER_NESTLEVEL TSQL Function

This function is used to determine the current nest level or number of triggers that fired the current one. This could be used to prevent triggers from firing when fired by others. Here’s an example that does that; we have two tables with triggers, that fire AFTER INSERT, and insert into the other table. The use of TRIGGER_NESTLEVEL allows us to control the flow gracefully.

First create the tables and triggers;

CREATE TABLE dbo.Table1
(
	Id INTEGER NOT NULL PRIMARY KEY CLUSTERED,
	Value VARCHAR(20) NOT NULL
);
GO

CREATE TABLE dbo.Table2
(
	Id INTEGER NOT NULL PRIMARY KEY CLUSTERED,
	Value VARCHAR(20) NOT NULL
);
GO

-- Trigger on table 1
CREATE TRIGGER dbo.trg_Table1 ON dbo.Table1
AFTER INSERT
AS
BEGIN
	DECLARE @nest INTEGER;
	SET @nest = TRIGGER_NESTLEVEL();

	IF (@nest = 1)
	BEGIN
		INSERT INTO dbo.Table2
		(
			Id,
			Value
		)
		SELECT i.Id,
			   i.Value
		FROM inserted i;
	END
	ELSE
	BEGIN
		PRINT 'Trigger 1: Insert aborted trigger_nestlevel is ' + CAST(@nest AS VARCHAR(10));
	END
END
GO

-- Trigger on table 2
CREATE TRIGGER dbo.trg_Table2 ON dbo.Table2
AFTER INSERT
AS
BEGIN
	DECLARE @nest INTEGER;
	SET @nest = TRIGGER_NESTLEVEL();

	IF (@nest = 1)
	BEGIN
		INSERT INTO dbo.Table1
		(
			Id,
			Value
		)
		SELECT i.Id,
			   i.Value
		FROM inserted i;
	END
	ELSE
	BEGIN
		PRINT 'Trigger 2: Insert aborted trigger_nestlevel is ' + CAST(@nest AS VARCHAR(10));
	END
END
GO

Now insert data into dbo.Table1.

INSERT INTO dbo.Table1
(
	Id,
	Value
)
VALUES
(
	1,
	'Blah'
);

Trigger 2: Insert aborted trigger_nestlevel is 2

(1 row(s) affected)

(1 row(s) affected)

SELECT *
FROM dbo.Table1
UNION ALL
SELECT *
FROM dbo.Table2;

Now insert data into dbo.Table2.

INSERT INTO dbo.Table2
(
	Id,
	Value
)
VALUES
(
	2,
	'Blah'
);

Trigger 1: Insert aborted trigger_nestlevel is 2

(1 row(s) affected)

(1 row(s) affected)

SELECT *
FROM dbo.Table1
UNION ALL
SELECT *
FROM dbo.Table2;

Of course this situation is best avoided in the first place but it’s nice to have an awareness of some of these lesser known functions.