DDL Triggers

I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?

I have some code here (http://sqlservercode.blogspot.com/2006/08/ddl-trigger-events-revisited.html) that basically shows that you can combine events

But where is this info in BOL?

For example if I do this:

create a trigger and I use 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
FROMsys.triggers t
JOINsys.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

So here is the complete list for who wants it

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

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

Denis The SQL Menace

http://sqlservercode.blogspot.com

Hi Denis,

This information is documented in the Books Online topic "Event Groups for Use with DDL Triggers.

http://msdn2.microsoft.com/en-us/library/ms191441.aspx

Regards,

Gail

|||

Thank you, however I would prefer text over an image (So that I can work my copy and paste magic!!)

Denis the SQL Menace

http://sqlservercode.blogspot.com/