Grupos de eventos DDL
En la tabla siguiente se muestran los grupos de eventos DDL que se pueden usar para ejecutar un desencadenador DDL o una notificación de eventos, así como las instrucciones Transact-SQL que comprenden. Observe la naturaleza inclusiva de los grupos de eventos. Por ejemplo, un desencadenador DDL o una notificación de eventos que especifica FOR DDL_TABLE_EVENTS (tipo 10018) cubre las instrucciones CREATE TABLE, ALTER TABLE y DROP TABLE. Un desencadenador DLL o una notificación de eventos que especifica FOR DDL_TABLE_VIEW_EVENTS (tipo 10017) cubre todas las instrucciones Transact-SQL bajo los tipos DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_INDEX_EVENTS y DDL_STATISTICS_EVENTS.
Nota
Determinados procedimientos almacenados del sistema que realizan operaciones similares a DDL también pueden activar desencadenadores DDL o notificaciones de eventos. Pruebe los desencadenadores DDL y las notificaciones de eventos para determinar sus respuestas a los procedimientos almacenados del sistema que se ejecutan. Por ejemplo, tanto la instrucción CREATE TYPE como el procedimiento almacenado sp_addtype activarán un desencadenador DDL o una notificación de eventos creada en un evento CREATE_TYPE.
Eventos
Los eventos que figuran bajo DDL_DATABASE_LEVEL_EVENTS se ejecutan en el nivel de servidor (instancia) o de base de datos. Los eventos que figuran bajo DDL_SERVER_LEVEL_EVENTS solo pueden ejecutarse en el nivel de servidor.
tipo primario |
tipo |
nombre |
NULL |
296 |
ALTER_SERVER_CONFIGURATION |
NULL |
10001 |
DDL_EVENTS |
10001 |
10016 |
| DDL_DATABASE_LEVEL_EVENTS |
10016 |
10027 |
| | DDL_ASSEMBLY_EVENTS |
10027 |
102 |
| | | ALTER_ASSEMBLY |
10027 |
101 |
| | | CREATE_ASSEMBLY |
10027 |
103 |
| | | DROP_ASSEMBLY |
10016 |
10029 |
| | DDL_DATABASE_SECURITY_EVENTS |
10029 |
10033 |
| | | DDL_APPLICATION_ROLE_EVENTS |
10033 |
138 |
| | | | ALTER_APPLICATION_ROLE |
10033 |
137 |
| | | | CREATE_APPLICATION_ROLE |
10033 |
139 |
| | | | DROP_APPLICATION_ROLE |
10029 |
10038 |
| | | DDL_ASYMMETRIC_KEY_EVENTS |
10038 |
248 |
| | | | ALTER_ASYMMETRIC_KEY |
10038 |
247 |
| | | | CREATE_ASYMMETRIC_KEY |
10038 |
249 |
| | | | DROP_ASYMMETRIC_KEY |
10029 |
10036 |
| | | DDL_AUTHORIZATION_DATABASE_EVENTS |
10036 |
205 |
| | | | ALTER_AUTHORIZATION_DATABASE |
10029 |
10030 |
| | | DDL_CERTIFICATE_EVENTS |
10030 |
198 |
| | | | ALTER_CERTIFICATE |
10030 |
197 |
| | | | CREATE_CERTIFICATE |
10030 |
199 |
| | | | DROP_CERTIFICATE |
10029 |
10039 |
| | | DDL_CRYPTO_SIGNATURE_EVENTS |
10039 |
257 |
| | | | ADD_SIGNATURE |
10039 |
255 |
| | | | ADD_SIGNATURE_SCHEMA_OBJECT |
10039 |
258 |
| | | | DROP_SIGNATURE |
10039 |
256 |
| | | | DROP_SIGNATURE_SCHEMA_OBJECT |
10029 |
10066 |
| | | DDL_DATABASE_AUDIT_SPECIFICATION_EVENTS |
10066 |
291 |
| | | | ALTER_DATABASE_AUDIT_SPECIFICATION |
10066 |
290 |
| | | | CREATE_DATABASE_AUDIT_SPECIFICATION |
10066 |
292 |
| | | | DROP_DATABASE_AUDIT_SPECIFICATION |
10029 |
10062 |
| | | DDL_DATABASE_ENCRYPTION_KEY_EVENTS |
10062 |
279 |
| | | | ALTER_DATABASE_ENCRYPTION_KEY |
10062 |
278 |
| | | | CREATE_DATABASE_ENCRYPTION_KEY |
10062 |
280 |
| | | | DROP_DATABASE_ENCRYPTION_KEY |
10029 |
10035 |
| | | DDL_GDR_DATABASE_EVENTS |
10035 |
171 |
| | | | DENY_DATABASE |
10035 |
170 |
| | | | GRANT_DATABASE |
10035 |
172 |
| | | | REVOKE_DATABASE |
10029 |
10040 |
| | | DDL_MASTER_KEY_EVENTS |
10040 |
253 |
| | | | ALTER_MASTER_KEY |
10040 |
252 |
| | | | CREATE_MASTER_KEY |
10040 |
254 |
| | | | DROP_MASTER_KEY |
10029 |
10032 |
| | | DDL_ROLE_EVENTS |
10032 |
207 |
| | | | ADD_ROLE_MEMBER |
10032 |
135 |
| | | | ALTER_ROLE |
10032 |
134 |
| | | | CREATE_ROLE |
10032 |
136 |
| | | | DROP_ROLE |
10032 |
208 |
| | | | DROP_ROLE_MEMBER |
10029 |
10034 |
| | | DDL_SCHEMA_EVENTS |
10034 |
142 |
| | | | ALTER_SCHEMA |
10034 |
141 |
| | | | CREATE_SCHEMA |
10034 |
143 |
| | | | DROP_SCHEMA |
10029 |
10037 |
| | | DDL_SYMMETRIC_KEY_EVENTS |
10037 |
245 |
| | | | ALTER_SYMMETRIC_KEY |
10037 |
244 |
| | | | CREATE_SYMMETRIC_KEY |
10037 |
246 |
| | | | DROP_SYMMETRIC_KEY |
10029 |
10031 |
| | | DDL_USER_EVENTS |
10031 |
132 |
| | | | ALTER_USER |
10031 |
131 |
| | | | CREATE_USER |
10031 |
133 |
| | | | DROP_USER |
10016 |
10052 |
| | DDL_DEFAULT_EVENTS |
10052 |
218 |
| | | BIND_DEFAULT |
10052 |
220 |
| | | CREATE_DEFAULT |
10052 |
231 |
| | | DROP_DEFAULT |
10052 |
242 |
| | | UNBIND_DEFAULT |
10016 |
10026 |
| | DDL_EVENT_NOTIFICATION_EVENTS |
10026 |
74 |
| | | CREATE_EVENT_NOTIFICATION |
10026 |
76 |
| | | DROP_EVENT_NOTIFICATION |
10016 |
10053 |
| | DDL_EXTENDED_PROPERTY_EVENTS |
10053 |
211 |
| | | ALTER_EXTENDED_PROPERTY |
10053 |
222 |
| | | CREATE_EXTENDED_PROPERTY |
10053 |
233 |
| | | DROP_EXTENDED_PROPERTY |
10016 |
10054 |
| | DDL_FULLTEXT_CATALOG_EVENTS |
10054 |
212 |
| | | ALTER_FULLTEXT_CATALOG |
10054 |
223 |
| | | CREATE_FULLTEXT_CATALOG |
10054 |
234 |
| | | DROP_FULLTEXT_CATALOG |
10016 |
10067 |
| | DDL_FULLTEXT_STOPLIST_EVENTS |
10067 |
294 |
| | | ALTER_FULLTEXT_STOPLIST |
10067 |
293 |
| | | CREATE_FULLTEXT_STOPLIST |
10067 |
295 |
| | | DROP_FULLTEXT_STOPLIST |
10016 |
10023 |
| | DDL_FUNCTION_EVENTS |
10023 |
62 |
| | | ALTER_FUNCTION |
10023 |
61 |
| | | CREATE_FUNCTION |
10023 |
63 |
| | | DROP_FUNCTION |
10016 |
10049 |
| | DDL_PARTITION_EVENTS |
10049 |
10050 |
| | | DDL_PARTITION_FUNCTION_EVENTS |
10050 |
192 |
| | | | ALTER_PARTITION_FUNCTION |
10050 |
191 |
| | | | CREATE_PARTITION_FUNCTION |
10050 |
193 |
| | | | DROP_PARTITION_FUNCTION |
10049 |
10051 |
| | | DDL_PARTITION_SCHEME_EVENTS |
10051 |
195 |
| | | | ALTER_PARTITION_SCHEME |
10051 |
194 |
| | | | CREATE_PARTITION_SCHEME |
10051 |
196 |
| | | | DROP_PARTITION_SCHEME |
10016 |
10055 |
| | DDL_PLAN_GUIDE_EVENTS |
10055 |
216 |
| | | ALTER_PLAN_GUIDE |
10055 |
228 |
| | | CREATE_PLAN_GUIDE |
10055 |
238 |
| | | DROP_PLAN_GUIDE |
10016 |
10024 |
| | DDL_PROCEDURE_EVENTS |
10024 |
52 |
| | | ALTER_PROCEDURE |
10024 |
51 |
| | | CREATE_PROCEDURE |
10024 |
53 |
| | | DROP_PROCEDURE |
10016 |
10056 |
| | DDL_RULE_EVENTS |
10056 |
219 |
| | | BIND_RULE |
10056 |
229 |
| | | CREATE_RULE |
10056 |
239 |
| | | DROP_RULE |
10056 |
243 |
| | | UNBIND_RULE |
10016 |
10041 |
| | DDL_SSB_EVENTS |
10041 |
10063 |
| | | DDL_BROKER_PRIORITY_EVENTS |
10063 |
282 |
| | | | ALTER_BROKER_PRIORITY |
10063 |
281 |
| | | | CREATE_BROKER_PRIORITY |
10063 |
283 |
| | | | DROP_BROKER_PRIORITY |
10041 |
10043 |
| | | DDL_CONTRACT_EVENTS |
10043 |
154 |
| | | | CREATE_CONTRACT |
10043 |
156 |
| | | | DROP_CONTRACT |
10041 |
10042 |
| | | DDL_MESSAGE_TYPE_EVENTS |
10042 |
152 |
| | | | ALTER_MESSAGE_TYPE |
10042 |
151 |
| | | | CREATE_MESSAGE_TYPE |
10042 |
153 |
| | | | DROP_MESSAGE_TYPE |
10041 |
10044 |
| | | DDL_QUEUE_EVENTS |
10044 |
158 |
| | | | ALTER_QUEUE |
10044 |
157 |
| | | | CREATE_QUEUE |
10044 |
159 |
| | | | DROP_QUEUE |
10041 |
10047 |
| | | DDL_REMOTE_SERVICE_BINDING_EVENTS |
10047 |
175 |
| | | | ALTER_REMOTE_SERVICE_BINDING |
10047 |
174 |
| | | | CREATE_REMOTE_SERVICE_BINDING |
10047 |
176 |
| | | | DROP_REMOTE_SERVICE_BINDING |
10041 |
10046 |
| | | DDL_ROUTE_EVENTS |
10046 |
165 |
| | | | ALTER_ROUTE |
10046 |
164 |
| | | | CREATE_ROUTE |
10046 |
166 |
| | | | DROP_ROUTE |
10041 |
10045 |
| | | DDL_SERVICE_EVENTS |
10045 |
162 |
| | | | ALTER_SERVICE |
10045 |
161 |
| | | | CREATE_SERVICE |
10045 |
163 |
| | | | DROP_SERVICE |
10016 |
10022 |
| | DDL_SYNONYM_EVENTS |
10022 |
34 |
| | | CREATE_SYNONYM |
10022 |
36 |
| | | DROP_SYNONYM |
10016 |
10017 |
| | DDL_TABLE_VIEW_EVENTS |
10017 |
10020 |
| | | DDL_INDEX_EVENTS |
10020 |
213 |
| | | | ALTER_FULLTEXT_INDEX |
10020 |
25 |
| | | | ALTER_INDEX |
10020 |
224 |
| | | | CREATE_FULLTEXT_INDEX |
10020 |
24 |
| | | | CREATE_INDEX |
10020 |
274 |
| | | | CREATE_SPATIAL_INDEX |
10020 |
206 |
| | | | CREATE_XML_INDEX |
10020 |
235 |
| | | | DROP_FULLTEXT_INDEX |
10020 |
26 |
| | | | DROP_INDEX |
10017 |
10021 |
| | | DDL_STATISTICS_EVENTS |
10021 |
27 |
| | | | CREATE_STATISTICS |
10021 |
29 |
| | | | DROP_STATISTICS |
10021 |
28 |
| | | | UPDATE_STATISTICS |
10017 |
10018 |
| | | DDL_TABLE_EVENTS |
10018 |
22 |
| | | | ALTER_TABLE |
10018 |
21 |
| | | | CREATE_TABLE |
10018 |
23 |
| | | | DROP_TABLE |
10017 |
10019 |
| | | DDL_VIEW_EVENTS |
10019 |
42 |
| | | | ALTER_VIEW |
10019 |
41 |
| | | | CREATE_VIEW |
10019 |
43 |
| | | | DROP_VIEW |
10016 |
10025 |
| | DDL_TRIGGER_EVENTS |
10025 |
72 |
| | | ALTER_TRIGGER |
10025 |
71 |
| | | CREATE_TRIGGER |
10025 |
73 |
| | | DROP_TRIGGER |
10016 |
10028 |
| | DDL_TYPE_EVENTS |
10028 |
91 |
| | | CREATE_TYPE |
10028 |
93 |
| | | DROP_TYPE |
10016 |
10048 |
| | DDL_XML_SCHEMA_COLLECTION_EVENTS |
10048 |
178 |
| | | ALTER_XML_SCHEMA_COLLECTION |
10048 |
177 |
| | | CREATE_XML_SCHEMA_COLLECTION |
10048 |
179 |
| | | DROP_XML_SCHEMA_COLLECTION |
10016 |
241 |
| | RENAME |
10001 |
10002 |
| DDL_SERVER_LEVEL_EVENTS |
10002 |
214 |
| | ALTER_INSTANCE |
10002 |
10004 |
| | DDL_DATABASE_EVENTS |
10004 |
202 |
| | | ALTER_DATABASE |
10004 |
201 |
| | | CREATE_DATABASE |
10004 |
203 |
| | | DROP_DATABASE |
10002 |
10003 |
| | DDL_ENDPOINT_EVENTS |
10003 |
182 |
| | | ALTER_ENDPOINT |
10003 |
181 |
| | | CREATE_ENDPOINT |
10003 |
183 |
| | | DROP_ENDPOINT |
10002 |
10057 |
| | DDL_EVENT_SESSION_EVENTS |
10057 |
265 |
| | | ALTER_EVENT_SESSION |
10057 |
264 |
| | | CREATE_EVENT_SESSION |
10057 |
266 |
| | | DROP_EVENT_SESSION |
10002 |
10011 |
| | DDL_EXTENDED_PROCEDURE_EVENTS |
10011 |
221 |
| | | CREATE_EXTENDED_PROCEDURE |
10011 |
232 |
| | | DROP_EXTENDED_PROCEDURE |
10002 |
10012 |
| | DDL_LINKED_SERVER_EVENTS |
10012 |
263 |
| | | ALTER_LINKED_SERVER |
10012 |
225 |
| | | CREATE_LINKED_SERVER |
10012 |
10013 |
| | | DDL_LINKED_SERVER_LOGIN_EVENTS |
10013 |
226 |
| | | | CREATE_LINKED_SERVER_LOGIN |
10013 |
236 |
| | | | DROP_LINKED_SERVER_LOGIN |
10012 |
262 |
| | | DROP_LINKED_SERVER |
10002 |
10014 |
| | DDL_MESSAGE_EVENTS |
10014 |
215 |
| | | ALTER_MESSAGE |
10014 |
227 |
| | | CREATE_MESSAGE |
10014 |
237 |
| | | DROP_MESSAGE |
10002 |
10015 |
| | DDL_REMOTE_SERVER_EVENTS |
10015 |
217 |
| | | ALTER_REMOTE_SERVER |
10015 |
230 |
| | | CREATE_REMOTE_SERVER |
10015 |
240 |
| | | DROP_REMOTE_SERVER |
10002 |
10058 |
| | DDL_RESOURCE_GOVERNOR_EVENTS |
10058 |
273 |
| | | ALTER_RESOURCE_GOVERNOR_CONFIG |
10058 |
10059 |
| | | DDL_RESOURCE_POOL |
10059 |
268 |
| | | | ALTER_RESOURCE_POOL |
10059 |
267 |
| | | | CREATE_RESOURCE_POOL |
10059 |
269 |
| | | | DROP_RESOURCE_POOL |
10058 |
10060 |
| | | DDL_WORKLOAD_GROUP |
10060 |
271 |
| | | | ALTER_WORKLOAD_GROUP |
10060 |
270 |
| | | | CREATE_WORKLOAD_GROUP |
10060 |
272 |
| | | | DROP_WORKLOAD_GROUP |
10002 |
10005 |
| | DDL_SERVER_SECURITY_EVENTS |
10005 |
209 |
| | | ADD_SERVER_ROLE_MEMBER |
10005 |
10008 |
| | | DDL_AUTHORIZATION_SERVER_EVENTS |
10008 |
204 |
| | | | ALTER_AUTHORIZATION_SERVER |
10005 |
10009 |
| | | DDL_CREDENTIAL_EVENTS |
10009 |
260 |
| | | | ALTER_CREDENTIAL |
10009 |
259 |
| | | | CREATE_CREDENTIAL |
10009 |
261 |
| | | | DROP_CREDENTIAL |
10005 |
10061 |
| | | DDL_CRYPTOGRAPHIC_PROVIDER_EVENTS |
10061 |
276 |
| | | | ALTER_CRYPTOGRAPHIC_PROVIDER |
10061 |
275 |
| | | | CREATE_CRYPTOGRAPHIC_PROVIDER |
10061 |
277 |
| | | | DROP_CRYPTOGRAPHIC_PROVIDER |
10005 |
10007 |
| | | DDL_GDR_SERVER_EVENTS |
10007 |
168 |
| | | | DENY_SERVER |
10007 |
167 |
| | | | GRANT_SERVER |
10007 |
169 |
| | | | REVOKE_SERVER |
10005 |
10006 |
| | | DDL_LOGIN_EVENTS |
10006 |
145 |
| | | | ALTER_LOGIN |
10006 |
144 |
| | | | CREATE_LOGIN |
10006 |
146 |
| | | | DROP_LOGIN |
10005 |
10064 |
| | | DDL_SERVER_AUDIT_EVENTS |
10064 |
285 |
| | | | ALTER_SERVER_AUDIT |
10064 |
284 |
| | | | CREATE_SERVER_AUDIT |
10064 |
286 |
| | | | DROP_SERVER_AUDIT |
10005 |
10065 |
| | | DDL_SERVER_AUDIT_SPECIFICATION_EVENTS |
10065 |
288 |
| | | | ALTER_SERVER_AUDIT_SPECIFICATION |
10065 |
287 |
| | | | CREATE_SERVER_AUDIT_SPECIFICATION |
10065 |
289 |
| | | | DROP_SERVER_AUDIT_SPECIFICATION |
10005 |
10010 |
| | | DDL_SERVICE_MASTER_KEY_EVENTS |
10010 |
251 |
| | | | ALTER_SERVICE_MASTER_KEY |
10005 |
210 |
| | | DROP_SERVER_ROLE_MEMBER |
Esta lista se puede crear ejecutando el ejemplo de código siguiente.
WITH DirectReports(name, parent_type, type, level, sort) AS
(
SELECT CONVERT(varchar(255),type_name), parent_type, type, 1, CONVERT(varchar(255),type_name)
FROM sys.trigger_event_types
WHERE parent_type IS NULL
UNION ALL
SELECT CONVERT(varchar(255), REPLICATE ('| ' , level) + e.type_name),
e.parent_type, e.type, level + 1,
CONVERT (varchar(255), RTRIM(sort) + '| ' + e.type_name)
FROM sys.trigger_event_types AS e
INNER JOIN DirectReports AS d
ON e.parent_type = d.type
)
SELECT parent_type, type, name
FROM DirectReports
ORDER BY sort;