Ever wanted to know whether your triggers are enabled or not? The ObjectProperty command allows you to select information about the status of objects in your database, returning 1 for True and 0 for False. The example below determines whether a single trigger is disabled.
SELECT ObjectProperty(object_id('Trigger_Name'), 'ExecIsTriggerDisabled')
There are a number of other useful properties that can be returned, see help for the ObjectProperty command for more details.
The code below lists all tables that have a trigger and the status of that trigger.