--==To find out the all triggers in the DB==--

SELECT

OBJECT_NAME(parent_obj)TbName ,

[name] TrName,

(CASE WHEN (STATUS & 2048) = 2048 THEN 'Disabled' ELSE 'Enabled' END) Status

FROM sysobjects

WHERE xtype = 'TR'

order by 1, 2

--==To find out a selected trigger status in the DB==--

use DB

GO

IF (OBJECTPROPERTY (object_id(<TrName>),'ExecIsTriggerDisabled') = 1)

PRINT 'Trigger disabled'

ELSE

PRINT 'Trigger enabled'