Grup Peristiwa DDL
Berlaku untuk:
SQL ServerAzure SQL Database Azure SQL Managed Instance
Tabel berikut mencantumkan grup peristiwa DDL yang dapat digunakan untuk menjalankan pemicu DDL atau pemberitahuan peristiwa, dan juga pernyataan Transact-SQL yang dibahasnya. Perhatikan sifat inklusif dari grup peristiwa. Misalnya, pemicu DDL atau pemberitahuan peristiwa yang menentukan FOR DDL_TABLE_EVENTS (10018) mencakup pernyataan CREATE TABLE, ALTER TABLE, dan DROP TABLE Transact-SQL. Pemicu DDL atau pemberitahuan peristiwa yang menentukan FOR DDL_TABLE_VIEW_EVENTS (10017) mencakup semua pernyataan Transact-SQL di bawah jenis DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_INDEX_EVENTS, dan DDL_STATISTICS_EVENTS.
Catatan
Prosedur tersimpan sistem tertentu yang melakukan operasi seperti DDL juga dapat mengaktifkan pemicu DDL atau pemberitahuan peristiwa. Uji pemicu DDL dan pemberitahuan peristiwa Anda untuk menentukan respons mereka terhadap prosedur tersimpan sistem yang dijalankan. Misalnya, pernyataan CREATE TYPE dan prosedur tersimpan sp_addtype akan mengaktifkan pemicu DDL atau pemberitahuan peristiwa yang dibuat pada peristiwa CREATE_TYPE.
Acara
Peristiwa yang tercantum di bawah DDL_DATABASE_LEVEL_EVENTS dijalankan di tingkat server (instans) atau database. Peristiwa yang tercantum di bawah DDL_SERVER_LEVEL_EVENTS hanya dijalankan di tingkat server.
| parent_type | jenis | nama |
|---|---|---|
| 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 |
Data di atas dapat dibuat dengan menjalankan contoh kode berikut.
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;