Professional Microsoft® SQL Server® 2008 Administration

10.3. Trigger Views

The Management Studio interface is still slightly lacking in what you can accomplish with DDL triggers, so a DBA must often go T-SQL as a management interface. One of the nice views that is available to show you all the database-level DDL triggers is sys.triggers; for server-level triggers, you can use sys.server_triggers. Between these two views, you can quickly see what views your server has installed on it with a query like this one:

SELECT type, name, parent_class_desc FROM sys.triggers
WHERE parent_class_desc = 'DATABASE'
UNION
SELECT type, name, parent_class_desc FROM sys.server_triggers
WHERE parent_class_desc = 'SERVER'