Groupes d'événements DDL
Le tableau suivant récapitule 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 (type 10018) couvre les instructions 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 (type 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 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 ou une notification d'événement DDL créé sur un événement CREATE_TYPE.
Événements
Les événements répertoriés sous DDL_DATABASE_LEVEL_EVENTS s'exécutent au niveau de la base de données ou du serveur (instance). Les événements répertoriés sous DDL_SERVER_LEVEL_EVENTS peuvent être exécutés uniquement au niveau du serveur.
parent_type |
type |
nom |
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 |
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 |
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 |
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 |
210 |
| | | DROP_SERVER_ROLE_MEMBER |
Cette liste peut être créée 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