DDL 事件群組

發行項
06/25/2013

下表列出可用來執行 DDL 觸發程序或事件通知的 DDL 事件群組，以及它們所涵蓋的 Transact-SQL 陳述式。請注意這些事件群組的內含本質。例如，指定 FOR DDL_TABLE_EVENTS (10018) 的 DDL 觸發程序或事件通知會涵蓋 CREATE TABLE、ALTER TABLE 和 DROP TABLE Transact-SQL 陳述式。指定 FOR DDL_TABLE_VIEW_EVENTS (10017) 的 DDL 觸發程序或事件通知會涵蓋 DDL_TABLE_EVENTS、DDL_VIEW_EVENTS、DDL_INDEX_EVENTS 和 DDL_STATISTICS_EVENTS 類型底下的所有 Transact-SQL 陳述式。

[!附註]

執行類似 DDL 作業的某些系統預存程序也可以引發 DDL 觸發程序或事件通知。請測試 DDL 觸發程序和事件通知，以判斷它們對執行之系統預存程序的回應。例如，CREATE TYPE 陳述式與 sp_addtype 預存程序都會引發在 CREATE_TYPE 事件上建立的 DDL 觸發程序或事件通知。

事件

列在 DDL_DATABASE_LEVEL_EVENTS 底下的事件會在伺服器 (執行個體) 或資料庫層級執行。列在 DDL_SERVER_LEVEL_EVENTS 底下的事件只能在伺服器層級執行。

parent_type	型別	名稱
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

您可以透過執行下列程式碼範例，建立上述資料。

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;

請參閱

概念

事件通知

DDL 觸發程序

DDL 事件

DDL 事件群組

事件

請參閱

概念

其他資源