IDENTIFY DISABLED TRIGGERS WITHIN A DATABASE

To identify whether certain triggers have been disabled, use the system
function OBJECTPROPERTY(). The function takes two parameters, id and
property. The id is the identification number of the object. You can 
get the
trigger identification number from the sysobjects system table via the 
id
column. You will need to narrow the query from the sysobjects table 
with
the type column. The type column will have "TR" as a value designated
for triggers. The second parameter, property, which is used in
OBJECTPROPERTY() to find disabled triggers, would have the value
"ExeclsTriggerDisabled".

The following is sample code that you can use to identify disabled
triggers within a database:

SELECT
CASE
  OBJECTPROPERTY(object_id(name),
     'ExecIsTriggerDisabled')
  WHEN 1 THEN 'DISABLED'
  WHEN 0 THEN 'ENABLED'
  ELSE 'UNKNOWN'
END TriggerStatus
, name
FROM sysobjects WHERE type = 'tr'
ORDER BY
CASE
  OBJECTPROPERTY(object_id(name),
     'ExecIsTriggerDisabled')
  WHEN 1 THEN 'DISABLED'
  WHEN 0 THEN 'ENABLED'
  ELSE 'UNKNOWN'
END

----------------------------------------