grupos de eventos DDL
As tabelas a seguir listam os grupos de eventos DDL que podem ser usados para executar um gatilho DDL ou uma notificação de evento e também as instruções Transact-SQL que eles cobrem. Observe a natureza inclusiva dos grupos de eventos. Por exemplo, um gatilho DDL ou uma notificação de evento que especifica FOR DDL_TABLE_EVENTS (10018) abrange as instruções Transact-SQL CREATE TABLE, ALTER TABLE e DROP TABLE. Uma notificação de evento ou gatilho DDL que especifica FOR DDL_TABLE_VIEW_EVENTS (10017) abrange todas as instruções Transact-SQL nos tipos DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_INDEX_EVENTS e DDL_STATISTICS_EVENTS.
Observação
Certos procedimentos armazenados do sistema que executam operações similares a DDL também podem acionar gatilhos ou notificações de eventos DDL. Teste seus gatilhos e notificações de eventos DDL para determinar suas respostas aos procedimentos armazenados do sistema que são executados. Por exemplo, a instrução CREATE TYPE e o procedimento armazenado sp_addtype acionarão um gatilho DDL ou uma notificação de eventos que tenham sido criados em um evento CREATE_TYPE.
Eventos
Os eventos listados em DDL_DATABASE_LEVEL_EVENTS são executados no nível do servidor (instância) ou do banco de dados. Os eventos listados em DDL_SERVER_LEVEL_EVENTS são executados apenas no nível do servidor.
| parent_type | type | name |
| NULO | 296 | ALTER_SERVER_CONFIGURATION |
| NULO | 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 | | | RENOMEAR |
| 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 |
Os dados acima podem ser criados através da execução do seguinte exemplo de código.
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;