DDL 事件群組
下表列出可用來執行 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;