if you don't know what nested trigers are read here.

Triggers are nested when a trigger performs an action that initiates another trigger.

So if you have 2 table where each has an after update trigger like:

CREATE TABLE trTest1 
(
id INT, 
NAME VARCHAR(50)
)
GO
INSERT INTO trTest1
SELECT 1, 'NAME 1' UNION ALL
SELECT 2, 'NAME 2' UNION ALL
SELECT 3, 'NAME 3' UNION ALL
SELECT 4, 'NAME 4'
GO
CREATE TABLE trTest2
(
id INT,
NAME VARCHAR(50)
)
GO
INSERT INTO trTest2
SELECT 1, 'NAME 1' UNION ALL
SELECT 2, 'NAME 2' UNION ALL
SELECT 3, 'NAME 3' UNION ALL
SELECT 4, 'NAME 4'
GO
CREATE TRIGGER trigTest1 ON trTest1 FOR UPDATE AS
UPDATE t2
SET    id = i.id + 1
FROM trTest2 t2 JOIN inserted i ON t2.id = i.id
GO
CREATE TRIGGER trigTest2 ON trTest2 FOR UPDATE AS
UPDATE t1
SET    id = i.id + 1
FROM trTest1 t1 JOIN inserted i ON t1.id = i.id
GO
SELECT * FROM trTest1
SELECT * FROM trTest2
GO
UPDATE trTest1
SET id = 10
WHERE id = 1
SELECT * FROM trTest1
SELECT * FROM trTest2
GO
DROP TABLE trTest1
DROP TABLE trTest2

If you run this it will fail with the eror message

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

You have to use

EXEC sp_configure 'nested triggers', '0';
RECONFIGURE

to disable nested triggers execution.

I recently had to check one of our db's if there are any nested triggers in it.

so i wrote this script:

CREATE PROCEDURE usp_GetRefferencingTriggers 
AS
-- get all triggers and tables they're on.
SELECT     NAME AS TriggerName, OBJECT_NAME(parent_obj) AS ParentTable, c.TEXT
INTO    #AllTriggers
FROM    sysobjects o
        JOIN syscomments c ON o.id = c.id
WHERE    xtype = 'TR'
SELECT     t1.TriggerName AS MainTriggerName, t1.ParentTable AS MainTriggerParentTable,
–t1.TEXT AS MainTriggerText,
t2.TriggerName AS TriggerReferencingParentTable, t2.ParentTable AS ReferencingTriggerParentTable
–t2.TEXT AS RefferencingTriggerText
FROM    #AllTriggers t1
JOIN
#AllTriggers t2 ON
t2.ParentTable <> t1.ParentTable
AND t2.TEXT like '% ' + t1.ParentTable + '[(| ' + char(10) + '|' + char(13) + ']%'
order by t1.TriggerName, t2.ParentTable
DROP TABLE #AllTriggers
GO

It's not perfect but it reduces the number of triggers to look into a lot.

It simply looks for any triggers that have a current trigger's parent table name in their body.

So this means that it will also return rows for triggers in which you join a parent table to another table etc...

It's not perfect but it helped me in reducing number of triggers i had to check from 90 to 6.

And that's OK with me :))