List DDL Triggers By Using The sys.triggers Catalog View

How do you list DDL triggers in SQL Server 2005? It's very easy you just query the sys.triggers catalog view. Let's try an example

USE adventureworks
GO

--Create our trigger
CREATE TRIGGER trDropTable
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'You can not drop tables'
ROLLBACK
GO


--Create the table
CREATE TABLE abc(id INT)
GO

--Let's drop the table
DROP TABLE abc
GO

Our trigger works because the following message is displayed after we tried to drop the table

You can not drop tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

In order to list DDL triggers you need to use parent_id = 0
SELECT * FROM sys.triggers
WHERE parent_id = 0

In order to list DML triggers you need to use parent_id <> 0
SELECT * FROM sys.triggers
WHERE parent_id <> 0

And if you want to list all triggers you can just ommit the WHERE clause