Useful Queries for Triggers in SQL Server

By: Daniel Farina

Overview

As a final chapter for this tutorial, I will give you a handful of queries that will help when working with triggers.

Queries

By using SQL Server Dynamic Management Views as well as system views we can create queries to aid us in the assessment of triggers.

List DML triggers in a database with their parent object

SELECT QUOTENAME(OBJECT_SCHEMA_NAME(TR.object_id)) + '.' + QUOTENAME(TR.name) [Trigger_name],
       QUOTENAME(OBJECT_SCHEMA_NAME(T.object_id)) + '.' + QUOTENAME(T.name) [Parent_table_name],                 
       QUOTENAME(OBJECT_SCHEMA_NAME(V.object_id)) + '.' + QUOTENAME(V.name) [Parent_view_name]
FROM sys.triggers TR
LEFT JOIN sys.tables T
    ON TR.parent_id = T.object_id
LEFT JOIN sys.views V
    ON TR.parent_id = V.object_id
WHERE TR.parent_class = 1

List disabled triggers in a database (i.e. DML and database triggers)

SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.triggers
WHERE is_disabled = 1;

List enabled triggers in a database (i.e. DML and database triggers)

SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.triggers
WHERE is_disabled = 0;

List all disabled server triggers

SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.server_triggers
WHERE is_disabled = 1;

List all enabled server triggers

SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.server_triggers
WHERE is_disabled = 0;

Disable all DML triggers in a database

DECLARE @SqlStmt VARCHAR(255);
 
DECLARE CUR_TR CURSOR LOCAL FAST_FORWARD FOR
SELECT 'DISABLE TRIGGER ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(name) AS SqlStmt
FROM sys.triggers
WHERE parent_class = 1 -- DML Triggers
      AND is_disabled = 0;
 
OPEN CUR_TR;
FETCH NEXT FROM CUR_TR
INTO @SqlStmt;
WHILE @@FETCH_STATUS <> 0
BEGIN
    EXEC (@SqlStmt);
    FETCH NEXT FROM CUR_TR
    INTO @SqlStmt;
END;
CLOSE CUR_TR;
DEALLOCATE CUR_TR;

Execution plans of triggers

SELECT QUOTENAME(DB_NAME(TS.database_id)) + ''+
       QUOTENAME(ISNULL(OBJECT_SCHEMA_NAME(TS.object_id, TS.database_id), '**Server Trigger**')) + ''+
       QUOTENAME(ISNULL(OBJECT_NAME(TS.object_id, TS.database_id),STRIG.name)),
       qp.query_plan
FROM sys.dm_exec_trigger_stats TS
LEFT JOIN sys.server_triggers STRIG
   ON STRIG.object_id = TS.object_id AND
      STRIG.type = TS.type
CROSS APPLY sys.dm_exec_query_plan(TS.plan_handle) qp;

Getting execution statistics of cached triggers

SELECT QUOTENAME(DB_NAME(TS.database_id)) + '.'+
       QUOTENAME(ISNULL(OBJECT_SCHEMA_NAME(TS.object_id, TS.database_id), '**Server Trigger**')) + '.'+
       QUOTENAME(ISNULL(OBJECT_NAME(TS.object_id, TS.database_id),STRIG.name)),
       TS.cached_time,
       TS.last_execution_time,
       TS.execution_count,
       TS.total_worker_time,
       TS.last_worker_time,
       TS.min_worker_time,
       TS.max_worker_time,
       TS.total_elapsed_time,
       TS.last_elapsed_time,
       TS.min_elapsed_time,
       TS.max_elapsed_time
FROM sys.dm_exec_trigger_stats TS
LEFT JOIN sys.server_triggers STRIG
   ON STRIG.object_id = TS.object_id AND
      STRIG.type = TS.type

Physical reads by trigger statistics

SELECT QUOTENAME(DB_NAME(TS.database_id)) + '.'+
       QUOTENAME(ISNULL(OBJECT_SCHEMA_NAME(TS.object_id, TS.database_id), '**Server Trigger**')) + '.'+
       QUOTENAME(ISNULL(OBJECT_NAME(TS.object_id, TS.database_id),STRIG.name)),
       TS.total_physical_reads,
       TS.last_physical_reads,
       TS.min_physical_reads,
       TS.max_physical_reads,
      TS.total_num_physical_reads,
       TS.last_num_physical_reads,
       TS.min_num_physical_reads,
       TS.max_num_physical_reads
FROM sys.dm_exec_trigger_stats TS
LEFT JOIN sys.server_triggers STRIG
   ON STRIG.object_id = TS.object_id AND
      STRIG.type = TS.type

Logical I/O by trigger statistics

SELECT QUOTENAME(DB_NAME(TS.database_id)) + '.'+
       QUOTENAME(ISNULL(OBJECT_SCHEMA_NAME(TS.object_id, TS.database_id), '**Server Trigger**')) + '.'+
       QUOTENAME(ISNULL(OBJECT_NAME(TS.object_id, TS.database_id),STRIG.name)),
       TS.total_logical_writes,
       TS.last_logical_writes,
       TS.min_logical_writes,
       TS.max_logical_writes,
       TS.total_logical_reads,
       TS.last_logical_reads,
       TS.min_logical_reads,
       TS.max_logical_reads
FROM sys.dm_exec_trigger_stats TS
LEFT JOIN sys.server_triggers STRIG
   ON STRIG.object_id = TS.object_id AND
      STRIG.type = TS.type

Page server reads by trigger statistics

SELECT QUOTENAME(DB_NAME(TS.database_id)) + '.'+
       QUOTENAME(ISNULL(OBJECT_SCHEMA_NAME(TS.object_id, TS.database_id), '**Server Trigger**')) + '.'+
       QUOTENAME(ISNULL(OBJECT_NAME(TS.object_id, TS.database_id),STRIG.name)),
       TS.total_page_server_reads,
       TS.last_page_server_reads,
       TS.min_page_server_reads,
       TS.max_page_server_reads,
       TS.total_num_page_server_reads,
       TS.last_num_page_server_reads,
       TS.min_num_page_server_reads,
       TS.max_num_page_server_reads
FROM sys.dm_exec_trigger_stats TS
LEFT JOIN sys.server_triggers STRIG
   ON STRIG.object_id = TS.object_id AND
      STRIG.type = TS.type

Pages spilled by trigger statistics

SELECT QUOTENAME(DB_NAME(TS.database_id)) + '.'+
       QUOTENAME(ISNULL(OBJECT_SCHEMA_NAME(TS.object_id, TS.database_id), '**Server Trigger**')) + '.'+
       QUOTENAME(ISNULL(OBJECT_NAME(TS.object_id, TS.database_id),STRIG.name)),
       OBJECT_SCHEMA_NAME(TS.object_id, TS.database_id),
       OBJECT_NAME(TS.object_id, TS.database_id),
       TS.total_spills,
       TS.last_spills,
       TS.min_spills,
       TS.max_spills
FROM sys.dm_exec_trigger_stats TS
LEFT JOIN sys.server_triggers STRIG
   ON STRIG.object_id = TS.object_id AND
      STRIG.type = TS.type