grupos de eventos DDL

Se aplica a:SQL ServerAzure SQL DatabaseInstancia administrada de Azure SQLBase de datos SQL en Microsoft Fabric

En las tablas siguientes 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 (10018) cubre las instrucciones Transact-SQL CREATE TABLE, ALTER TABLE y DROP TABLE. Un desencadenador DLL o una notificación de eventos que especifica FOR DDL_TABLE_VIEW_EVENTS (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 se ejecutan en el nivel de servidor.

parent_type	type	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	10069	DDL_SEARCH_PROPERTY_LIST_EVENTS
10069	298	ALTER_SEARCH_PROPERTY_LIST
10069	297	CREATE_SEARCH_PROPERTY_LIST
10069	299	DROP_SEARCH_PROPERTY_LIST
10016	10070	DDL_SEQUENCE_EVENTS
10070	304	ALTER_SEQUENCE
10070	303	CREATE_SEQUENCE
10070	305	DROP_SEQUENCE
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	10071	DDL_AVAILABILITY_GROUP_EVENTS
10071	307	ALTER_AVAILABILITY_GROUP
10071	306	CREATE_AVAILABILITY_GROUP
10071	308	DROP_AVAILABILITY_GROUP
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	301	ALTER_SERVER_ROLE
10005	300	CREATE_SERVER_ROLE
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	302	DROP_SERVER_ROLE
10005	210	DROP_SERVER_ROLE_MEMBER

Los datos anteriores se pueden crear mediante la ejecución del siguiente ejemplo de código.

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;  

Consulte también

Notificaciones de eventos
Desencadenadores DDL
Eventos DDL