Groupes d'événements DDL
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 DDL et notifications d'événements 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 DDL ou une notification d'événement 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;