Find triggers and their associated tables

Observed sign or problem

An INSERT, UPDATE, or DELETE causes additional changes, rejects an operation, or takes longer than expected. Some of that logic may be in a trigger and run without appearing in the original statement.

What we need to distinguish

A trigger is code SQL Server runs automatically for specified events. DML covers data operations such as INSERT, UPDATE, and DELETE; DDL covers definition changes such as CREATE or ALTER.

We want to identify the trigger, its associated object, the events that activate it and whether it is executed as AFTER or INSTEAD OF. It is also convenient to separate DML triggers associated with tables or views from DDL triggers associated with the database.

Query

DECLARE @Objeto NVARCHAR(517) = NULL;
DECLARE @IdObjeto INT =
    CASE WHEN @Objeto IS NULL THEN NULL ELSE OBJECT_ID(@Objeto) END;

IF @Objeto IS NOT NULL AND @IdObjeto IS NULL
    THROW 50000, 'El objeto no existe o no es visible en esta base.', 1;

SELECT
    tr.name AS Trigger,
    tr.parent_class_desc AS Alcance,
    s.name AS Esquema,
    o.name AS ObjetoAsociado,
    o.type_desc AS TipoObjeto,
    ev.type_desc AS Evento,
    CASE
        WHEN tr.is_instead_of_trigger = 1 THEN N'INSTEAD OF'
        ELSE N'AFTER'
    END AS Momento,
    tr.is_disabled AS EstaDeshabilitado,
    tr.is_not_for_replication AS NoSeEjecutaEnReplicacion,
    tr.modify_date AS FechaModificacion
FROM sys.triggers AS tr
LEFT JOIN sys.objects AS o
    ON tr.parent_class = 1
   AND o.object_id = tr.parent_id
LEFT JOIN sys.schemas AS s
    ON s.schema_id = o.schema_id
LEFT JOIN sys.trigger_events AS ev
    ON ev.object_id = tr.object_id
WHERE tr.is_ms_shipped = 0
  AND (@IdObjeto IS NULL OR tr.parent_id = @IdObjeto)
ORDER BY
    s.name,
    o.name,
    tr.name,
    ev.type_desc;

Parameters or permissions

Leave @Objeto at NULL to list all visible triggers in the database, or type dbo.Pedido to narrow the search. Each event produces a row, so the same trigger can appear multiple times.

Metadata is subject to permissions. The code definition can be revised with OBJECT_DEFINITION(tr.object_id) when the account has VIEW DEFINITION.

How to read the result

AFTER runs the trigger after the operation passes its checks. INSTEAD OF runs the trigger in place of the original operation.

In a DML trigger, ObjetoAsociado identifies the table or view on which it is defined. In a base DDL trigger, the object and schema appear as NULL and Alcance indicates DATABASE.

Evento can display INSERT, UPDATE, DELETE, or a DDL event. INSTEAD OF replaces the original operation; AFTER is executed after the statement and its corresponding checks have succeeded.

EstaDeshabilitado = 1 preserves the definition, but prevents the trigger from being executed. The modification date is a clue to investigate changes, not history.

What this does establish

It allows you to confirm which visible triggers are defined, what event activates them, what their scope is and whether they are enabled.

What this still does not establish

It does not show that the trigger was executed in a specific operation or how much time it took. It also does not explain its logic, its order compared to other triggers or whether it correctly handles multi-row operations.

A trigger disabled today could have been active when a historical data occurred.

Next reasonable check

inserted and deleted are logical images: temporary representations of new and preceding rows, not permanent tables.

Read the full definition and look for uses for logical tables inserted and deleted. Then reproduce the operation within a controlled environment and check all the affected rows, not just one.

Search SQL Server