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 觸發程式或事件通知涵蓋類型底下的所有 Transact-SQL 語句DDL_TABLE_EVENTS、DDL_VIEW_EVENTS、DDL_INDEX_EVENTS和DDL_STATISTICS_EVENTS。
注意
執行類似 DDL 作業的某些系統預存程序也可以引發 DDL 觸發程序或事件通知。 請測試 DDL 觸發程序和事件通知，以判斷它們對執行之系統預存程序的回應。 例如，CREATE TYPE 陳述式與 sp_addtype 預存程序都會引發在 CREATE_TYPE 事件上建立的 DDL 觸發程序或事件通知。
事件
列在 DDL_DATABASE_LEVEL_EVENTS 底下的事件會在伺服器 (執行個體) 或資料庫層級執行。 列在 DDL_SERVER_LEVEL_EVENTS 底下的事件只能在伺服器層級執行。
| parent_type | type | NAME |
| 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 | | |重 命名 |
| 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;