Lire en anglais

Partager via

groupes d'événements DDL

  • Article

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.

Notes

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;

Voir aussi

Notifications d'événements
Déclencheurs DDL
Événements DDL