DDL Trigger Events Revisited

Many many moons ago I wrote about DDL Trigger Events
I promised that I would find out all the events that you can combine for example DDL_VIEW_EVENTS instead of CREATE_VIEW
,ALTER_VIEW and DROP_VIEW
So I finally did it, I created a bunch of triggers and then checked with a query that joined sys.triggers and sys.trigger_events

I started by creating a trigger and I used DDL_VIEW_EVENTS

CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_VIEW_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Views!'
ROLLBACK;
GO

After that I would check the sys.triggers and sys.trigger_events views to see what was inserted

SELECT name,te.type,te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc

In this case 3 rows were inserted

DDL_VIEW_EVENTS
----------------------------------
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

Then I altered the trigger as follows

CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_USER_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Users!'
ROLLBACK;
GO

I checked again with the same query

SELECT name,te.type,te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc
in this case 3 rows were also inserted


DDL_USER_EVENTS
----------------------------------
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

I kept repeating this until I had all the events and below is the result
The number that you see before the event is the type from the sys.trigger_events view

DDL_XML_SCHEMA_COLLECTION_EVENTS
----------------------------------
177 CREATE_XML_SCHEMA_COLLECTION
178 ALTER_XML_SCHEMA_COLLECTION
179 DROP_XML_SCHEMA_COLLECTION


DDL_VIEW_EVENTS
----------------------------------
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW


DDL_TRIGGER_EVENTS
----------------------------------
71 CREATE_TRIGGER
72 ALTER_TRIGGER
73 DROP_TRIGGER


DDL_USER_EVENTS
----------------------------------
131 CREATE_USER
132 ALTER_USER
133 DROP_USER


DDL_TYPE_EVENTS
----------------------------------
91 CREATE_TYPE
93 DROP_TYPE


DDL_TABLE_EVENTS
----------------------------------
21 CREATE_TABLE
22 ALTER_TABLE
23 DROP_TABLE


DDL_SYNONYM_EVENTS
----------------------------------
34 CREATE_SYNONYM
36 DROP_SYNONYM


DDL_STATISTICS_EVENTS
-----------------------------------
27 CREATE_STATISTICS
28 UPDATE_STATISTICS
29 DROP_STATISTICS


DDL_SERVICE_EVENTS
------------------------------------
161 CREATE_SERVICE
162 ALTER_SERVICE
163 DROP_SERVICE


DDL_SCHEMA_EVENTS
------------------------------------
141 CREATE_SCHEMA
142 ALTER_SCHEMA
143 DROP_SCHEMA


DDL_ROUTE_EVENTS
------------------------------------
164 CREATE_ROUTE
165 ALTER_ROUTE
166 DROP_ROUTE


DDL_ROLE_EVENTS
----------------------------------
134 CREATE_ROLE
135 ALTER_ROLE
136 DROP_ROLE


DDL_REMOTE_SERVICE_BINDING_EVENTS
-----------------------------------
174 CREATE_REMOTE_SERVICE_BINDING
175 ALTER_REMOTE_SERVICE_BINDING
176 DROP_REMOTE_SERVICE_BINDING


DDL_QUEUE_EVENTS
---------------------------------
157 CREATE_QUEUE
158 ALTER_QUEUE
159 DROP_QUEUE


DDL_PROCEDURE_EVENTS
----------------------------------
51 CREATE_PROCEDURE
52 ALTER_PROCEDURE
53 DROP_PROCEDURE


DDL_PARTITION_SCHEME_EVENTS
------------------------------------
194 CREATE_PARTITION_SCHEME
195 ALTER_PARTITION_SCHEME
196 DROP_PARTITION_SCHEME


DDL_PARTITION_FUNCTION_EVENTS
------------------------------
191 CREATE_PARTITION_FUNCTION
192 ALTER_PARTITION_FUNCTION
193 DROP_PARTITION_FUNCTION


DDL_EVENT_NOTIFICATION_EVENTS
----------------------------------
74 CREATE_EVENT_NOTIFICATION
76 DROP_EVENT_NOTIFICATION


DDL_ASSEMBLY_EVENTS
-----------------------------------
101 CREATE_ASSEMBLY
102 ALTER_ASSEMBLY
103 DROP_ASSEMBLY


DDL_CONTRACT_EVENTS
-----------------------------------
154 CREATE_CONTRACT
156 DROP_CONTRACT


DDL_FUNCTION_EVENTS
---------------------------------
61 CREATE_FUNCTION
62 ALTER_FUNCTION
63 DROP_FUNCTION


DDL_INDEX_EVENTS
---------------------------------
24 CREATE_INDEX
25 ALTER_INDEX
26 DROP_INDEX
206 CREATE_XML_INDEX


DDL_MESSAGE_TYPE_EVENTS
------------------------------------
151 CREATE_MESSAGE_TYPE
152 ALTER_MESSAGE_TYPE
153 DROP_MESSAGE_TYPE