groupes d'événements DDL
S’applique à : 
SQL Server Azure SQL Database Azure SQL Managed Instance
Les tableaux suivants répertorient les groupes d’événements DDL qui peuvent être utilisés pour exécuter un déclencheur DDL ou une notification d’événement, ainsi que les instructions Transact-SQL qu’ils couvrent. Notez la nature inclusive des groupes d'événements. Par exemple, un déclencheur DDL ou une notification d'événement qui spécifie FOR DDL_TABLE_EVENTS (10018) couvre les instructions Transact-SQL CREATE TABLE, ALTER TABLE et DROP TABLE. Un déclencheur DDL ou une notification d'événement qui spécifie FOR DDL_TABLE_VIEW_EVENTS (10017) couvre toutes les instructions Transact-SQL sous les types DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_INDEX_EVENTS et DDL_STATISTICS_EVENTS.
Remarque
Certaines procédures stockées système qui exécutent des opérations de type DDL peuvent également exécuter des déclencheurs DDL ou déclencher des notifications d'événements. Testez vos déclencheurs et notifications d'événements DDL afin de déterminer leur réponse aux procédures stockées système qui sont exécutées. Par exemple, l’instruction CREATE TYPE et la procédure stockée sp_addtype activent toutes les deux un déclencheur ou une notification d’événements DDL créés sur un événement CREATE_TYPE.
Événements
Les événements répertoriés sous DDL_DATABASE_LEVEL_EVENTS sont exécutés au niveau du serveur (instance) ou de la base de données. Les événements répertoriés sous DDL_SERVER_LEVEL_EVENTS sont exécutés uniquement au niveau du serveur.
| 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 | 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 |
Les données ci-dessus peuvent être créées en exécutant l'exemple de code suivant.
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;