Use sys.trigger_event_types to List Trigger Event Types in SQL Server

In SQL Server, you can use sys.trigger_event_types to return all events or event groups on which a trigger can fire.

Example 1 – A Small Sample

Here, I use the TOP() clause to return the first ten rows from the view.

SELECT TOP(10) * 
FROM sys.trigger_event_types;

Result:

+--------+-------------------+---------------+
| type   | type_name         | parent_type   |
|--------+-------------------+---------------|
| 21     | CREATE_TABLE      | 10018         |
| 22     | ALTER_TABLE       | 10018         |
| 23     | DROP_TABLE        | 10018         |
| 24     | CREATE_INDEX      | 10020         |
| 25     | ALTER_INDEX       | 10020         |
| 26     | DROP_INDEX        | 10020         |
| 27     | CREATE_STATISTICS | 10021         |
| 28     | UPDATE_STATISTICS | 10021         |
| 29     | DROP_STATISTICS   | 10021         |
| 34     | CREATE_SYNONYM    | 10022         |
+--------+-------------------+---------------+

If I select all rows, I get 284 when using SQL Server 2017 and 291 in SQL Server 2019.

Example 2 – Getting the Parent

If you look closely at the results of sys.trigger_event_types, you’ll see that DDL trigger events are hierarchical.

Here’s an example that shows the hierarchy of the CREATE_TABLE trigger event.

WITH event_types(Type, Type_Name, Parent_Type, Level) AS (
    SELECT type,
           type_name,
           parent_type, 
           1 AS level
    FROM sys.trigger_event_types
    WHERE type_name = 'CREATE_TABLE'

    UNION ALL

    SELECT tet.type,
           tet.type_name,
           tet.parent_type, 
           et.level + 1 AS level
    FROM event_types AS et
    JOIN sys.trigger_event_types AS tet
        ON et.parent_type = tet.type
)

SELECT 
  Type,
  Type_name,
  Parent_Type
FROM event_types
ORDER BY level DESC;

Result:

+--------+---------------------------+---------------+
| Type   | Type_name                 | Parent_Type   |
|--------+---------------------------+---------------|
| 10001  | DDL_EVENTS                | NULL          |
| 10016  | DDL_DATABASE_LEVEL_EVENTS | 10001         |
| 10017  | DDL_TABLE_VIEW_EVENTS     | 10016         |
| 10018  | DDL_TABLE_EVENTS          | 10017         |
| 21     | CREATE_TABLE              | 10018         |
+--------+---------------------------+---------------+

We can see that DDL_EVENTS is at the top of the hierarchy, followed by DDL_DATABASE_LEVEL_EVENTS, etc, until we reach CREATE_TABLE.

Another way to visualise it is like this:

DDL_EVENTS > DDL_DATABASE_LEVEL_EVENTS > DDL_TABLE_VIEW_EVENTS > DDL_TABLE_EVENTS > CREATE_TABLE

You can replace CREATE_TABLE in the query with any other event type to see its hierarchy.

Also see Query that Returns a Hierarchical List of Trigger Event Types in SQL Server to return a hierarchical list of trigger event types.