SQL Server: check if trigger is on or off?

Question

How can we see which trigger is enabled or disabled in SQL Server 2008?

Answer 1

Using sys.triggers

 SELECT name, is_disabled FROM sys.triggers 

Answer 2

In large databases, you usually don't know the table for the trigger.

 SELECT OBJECT_NAME(parent_id) [table_name],[name] [trigger_name],is_disabled FROM sys.triggers 

Answer 3

The descriptive state of the trigger will help you know the status clearly. Also excluding triggers not related to user tables.

Check out the code below:

 SELECT OBJECT_NAME(parent_id) [Table_Name],[name] [Trigger_Name], Case When is_disabled=0 then 'Enabled' Else 'Disabled' End [Trigger_Status], is_disabled FROM sys.triggers where OBJECT_NAME(parent_id) is not null