MSSQL Trigger 확인 Query 모음

MSSQL Trigger 관련 현업에서 꼭 필요한 Query 모음입니다.

 

Trigger 공부하고 싶을 때는 밑에 링크로 참조하세요.

• MSSQL Trigger 특징 및 유형.
• MSSQL LOGIN Trigger 사용법 및 특징.
• MSSQL DDL Trigger 사용법 및 특징.
• MSSQL DML Trigger 특징 및 유형.
• MSSQL AFTER Trigger 생성, 수정, 삭제 사용법.
• MSSQL INSTEAD OF Trigger 생성, 수정, 삭제 사용법.
• MSSQL insert, update, delete 동시 Trigger 사용법.
• MSSQL Nested Triggers 사용법.
• MSSQL Trigger Function 사용법 및 특징.
• MSSQL Trigger enable, disable 사용법 및 설명.
• MSSQL Trigger 조회 및 내용검색.
• MSSQL Trigger 장점 및 단점. 

서버, DML, DATABASE의 TRIGGER 상태 알 수 있습니다.

• Trigger를 문서화가 되어 있지 않았을 때 query 하나로 모든 trigger를 확인할 때 사용합니다.
• 소스코드입니다.

-- 서버, DML, DABASE 의 TRIGGER 상태 알수 있습니다.
SELECT 'DML,Database' name_type,
       name,
       parent_class_desc,     
       case when is_disabled  = 0 then 'disable' else 'enable' end tr_status
FROM sys.triggers
UNION ALL
SELECT 'server tr' name_type,
       name,
       parent_class_desc,    
       case when is_disabled  = 0 then 'disable' else 'enable' end tr_status
FROM sys.server_triggers
-- WHERE is_disabled = 1
;

 

TABLE, VIEW에 생성된 TRIGGER 알아보기.

• Trigger 생성되어 있는데 어느 table에서 생성되어 있는지 확인할 때 사용합니다.
• 소스코드입니다.

-- TABLE, VIEW 에 생성된 TRIGGER 알아보기.
SELECT triggers.name [Trigger_name],
       tables.name [Parent_table_name],                 
       views.name [Parent_view_name]
FROM sys.triggers triggers
LEFT JOIN sys.tables tables
    ON triggers.parent_id = tables.object_id
LEFT JOIN sys.views views
    ON triggers.parent_id = views.object_id
WHERE triggers.parent_class = 1
;

 

dm_exec_trigger_stats을 통해서 trigger 정보 확인.

• dm_exec_trigger_stats는 Trigger에 대한 상태를 알 수는 system view입니다.
• DB를 운영할 때 trigger의 통계 및 상태를 알려고 할 때 사용하세요.
• 소스코드입니다.

--  dm_exec_trigger_stats 을 통해서 trigger 정보 확인. 
SELECT QUOTENAME(DB_NAME(dm_exec_trigger_stats.database_id)) + '.'+
       QUOTENAME(ISNULL(OBJECT_SCHEMA_NAME(dm_exec_trigger_stats.object_id, dm_exec_trigger_stats.database_id), 'Server Trigger')) + '.'+
       QUOTENAME(ISNULL(OBJECT_NAME(dm_exec_trigger_stats.object_id, dm_exec_trigger_stats.database_id),server_triggers.name)) trigger_info,
       OBJECT_SCHEMA_NAME(dm_exec_trigger_stats.object_id, dm_exec_trigger_stats.database_id) schemaName,
       OBJECT_NAME(dm_exec_trigger_stats.object_id, dm_exec_trigger_stats.database_id) objectName,

       --  Pages spilled by trigger.
       dm_exec_trigger_stats.total_spills as total_spills,
       dm_exec_trigger_stats.last_spills  as last_spills,
       dm_exec_trigger_stats.min_spills   as min_spills,
       dm_exec_trigger_stats.max_spills   as max_spills, 

	   -- Getting execution statistics of cached triggers
       dm_exec_trigger_stats.cached_time  as cached_time,
       dm_exec_trigger_stats.last_execution_time  as last_execution_time,
       dm_exec_trigger_stats.execution_count      as execution_count,
       dm_exec_trigger_stats.total_worker_time    as total_worker_time,
       dm_exec_trigger_stats.last_worker_time     as last_worker_time,
       dm_exec_trigger_stats.min_worker_time      as min_worker_time,
       dm_exec_trigger_stats.max_worker_time      as max_worker_time,
       dm_exec_trigger_stats.total_elapsed_time   as total_elapsed_time,
       dm_exec_trigger_stats.last_elapsed_time    as last_elapsed_time,
       dm_exec_trigger_stats.min_elapsed_time     as min_elapsed_time,
       dm_exec_trigger_stats.max_elapsed_time     as max_elapsed_time,

       -- Page server reads  
       dm_exec_trigger_stats.total_page_server_reads	as total_page_server_reads,
       dm_exec_trigger_stats.last_page_server_reads		as last_page_server_reads,
       dm_exec_trigger_stats.min_page_server_reads		as min_page_server_reads,
       dm_exec_trigger_stats.max_page_server_reads		as max_page_server_reads,
       dm_exec_trigger_stats.total_num_page_server_reads	as total_num_page_server_reads,
       dm_exec_trigger_stats.last_num_page_server_reads		as last_num_page_server_reads,
       dm_exec_trigger_stats.min_num_page_server_reads		as min_num_page_server_reads,
       dm_exec_trigger_stats.max_num_page_server_reads		as max_num_page_server_reads,

       -- Logical I/O  
       dm_exec_trigger_stats.total_logical_writes	as total_logical_writes,
       dm_exec_trigger_stats.last_logical_writes	as last_logical_writes,
       dm_exec_trigger_stats.min_logical_writes		as min_logical_writes,
       dm_exec_trigger_stats.max_logical_writes		as max_logical_writes,
       dm_exec_trigger_stats.total_logical_reads	as total_logical_reads,
       dm_exec_trigger_stats.last_logical_reads		as last_logical_reads,
       dm_exec_trigger_stats.min_logical_reads		as min_logical_reads,
       dm_exec_trigger_stats.max_logical_reads		as max_logical_reads

FROM sys.dm_exec_trigger_stats dm_exec_trigger_stats
LEFT JOIN sys.server_triggers server_triggers
   ON server_triggers.object_id = dm_exec_trigger_stats.object_id AND
      server_triggers.type = dm_exec_trigger_stats.type;