Display DDL Triggers in your SQL Server Database

To display DDL Triggers in your database (SQL Server 2005/2008), use the sys.triggers catalog view as shown below:

-- Database-scoped DDL triggers in the current database
SELECT name as TriggerName, create_date as CreationDate,
type_desc as [TriggerType(SQLorCLR)], is_disabled
FROM sys.triggers
WHERE parent_class_desc = 'DATABASE'
ORDER BY name

The query shown above displays all the database-scoped triggers.

In order to view the server-scoped triggers, use the sys.servertriggers and sys.server_trigger_events

-- Server-scoped triggers in the current database
SELECT name as TriggerName, create_date as CreationDate,
st.type_desc [TriggerType(SQLorCLR)], is_disabled
FROM sys.server_triggers st
INNER JOIN sys.server_trigger_events ste ON
st.object_id = ste.object_id

You can also check a related post Display DML Triggers in your SQL Server Database