TechNet
Export (0) Print
Expand All

DDL Event Groups

 

Applies To: SQL Server 2016

The following tables list the DDL event groups that can be used to run a DDL trigger or an event notification, and also the Transact-SQL statements they cover. Note the inclusive nature of the event groups. For example, a DDL trigger or event notification that specifies FOR DDL_TABLE_EVENTS (10018) covers the CREATE TABLE, ALTER TABLE and DROP TABLE Transact-SQL statements. A DDL trigger or event notification that specifies FOR DDL_TABLE_VIEW_EVENTS (10017) covers all Transact-SQL statements under the types DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_INDEX_EVENTS, and DDL_STATISTICS_EVENTS.

System_CAPS_ICON_note.jpg Note


Certain system stored procedures that perform DDL-like operations can also fire DDL triggers or event notifications. Test your DDL triggers and event notifications to determine their responses to system stored procedures that are run. For example, the CREATE TYPE statement and sp_addtype stored procedure will both fire a DDL trigger or event notification that is created on a CREATE_TYPE event.

The events listed under DDL_DATABASE_LEVEL_EVENTS execute at the server (instance) or database level. Events listed under DDL_SERVER_LEVEL_EVENTS execute only at the server level.

parent_typetypename
NULL296ALTER_SERVER_CONFIGURATION
NULL10001DDL_EVENTS
1000110016| DDL_DATABASE_LEVEL_EVENTS
1001610027| | DDL_ASSEMBLY_EVENTS
10027102| | | ALTER_ASSEMBLY
10027101| | | CREATE_ASSEMBLY
10027103| | | DROP_ASSEMBLY
1001610029| | DDL_DATABASE_SECURITY_EVENTS
1002910033| | | DDL_APPLICATION_ROLE_EVENTS
10033138| | | | ALTER_APPLICATION_ROLE
10033137| | | | CREATE_APPLICATION_ROLE
10033139| | | | DROP_APPLICATION_ROLE
1002910038| | | DDL_ASYMMETRIC_KEY_EVENTS
10038248| | | | ALTER_ASYMMETRIC_KEY
10038247| | | | CREATE_ASYMMETRIC_KEY
10038249| | | | DROP_ASYMMETRIC_KEY
1002910036| | | DDL_AUTHORIZATION_DATABASE_EVENTS
10036205| | | | ALTER_AUTHORIZATION_DATABASE
1002910030| | | DDL_CERTIFICATE_EVENTS
10030198| | | | ALTER_CERTIFICATE
10030197| | | | CREATE_CERTIFICATE
10030199| | | | DROP_CERTIFICATE
1002910039| | | DDL_CRYPTO_SIGNATURE_EVENTS
10039257| | | | ADD_SIGNATURE
10039255| | | | ADD_SIGNATURE_SCHEMA_OBJECT
10039258| | | | DROP_SIGNATURE
10039256| | | | DROP_SIGNATURE_SCHEMA_OBJECT
1002910066| | | DDL_DATABASE_AUDIT_SPECIFICATION_EVENTS
10066291| | | | ALTER_DATABASE_AUDIT_SPECIFICATION
10066290| | | | CREATE_DATABASE_AUDIT_SPECIFICATION
10066292| | | | DROP_DATABASE_AUDIT_SPECIFICATION
1002910062| | | DDL_DATABASE_ENCRYPTION_KEY_EVENTS
10062279| | | | ALTER_DATABASE_ENCRYPTION_KEY
10062278| | | | CREATE_DATABASE_ENCRYPTION_KEY
10062280| | | | DROP_DATABASE_ENCRYPTION_KEY
1002910035| | | DDL_GDR_DATABASE_EVENTS
10035171| | | | DENY_DATABASE
10035170| | | | GRANT_DATABASE
10035172| | | | REVOKE_DATABASE
1002910040| | | DDL_MASTER_KEY_EVENTS
10040253| | | | ALTER_MASTER_KEY
10040252| | | | CREATE_MASTER_KEY
10040254| | | | DROP_MASTER_KEY
1002910032| | | DDL_ROLE_EVENTS
10032207| | | | ADD_ROLE_MEMBER
10032135| | | | ALTER_ROLE
10032134| | | | CREATE_ROLE
10032136| | | | DROP_ROLE
10032208| | | | DROP_ROLE_MEMBER
1002910034| | | DDL_SCHEMA_EVENTS
10034142| | | | ALTER_SCHEMA
10034141| | | | CREATE_SCHEMA
10034143| | | | DROP_SCHEMA
1002910037| | | DDL_SYMMETRIC_KEY_EVENTS
10037245| | | | ALTER_SYMMETRIC_KEY
10037244| | | | CREATE_SYMMETRIC_KEY
10037246| | | | DROP_SYMMETRIC_KEY
1002910031| | | DDL_USER_EVENTS
10031132| | | | ALTER_USER
10031131| | | | CREATE_USER
10031133| | | | DROP_USER
1001610052| | DDL_DEFAULT_EVENTS
10052218| | | BIND_DEFAULT
10052220| | | CREATE_DEFAULT
10052231| | | DROP_DEFAULT
10052242| | | UNBIND_DEFAULT
1001610026| | DDL_EVENT_NOTIFICATION_EVENTS
1002674| | | CREATE_EVENT_NOTIFICATION
1002676| | | DROP_EVENT_NOTIFICATION
1001610053| | DDL_EXTENDED_PROPERTY_EVENTS
10053211| | | ALTER_EXTENDED_PROPERTY
10053222| | | CREATE_EXTENDED_PROPERTY
10053233| | | DROP_EXTENDED_PROPERTY
1001610054| | DDL_FULLTEXT_CATALOG_EVENTS
10054212| | | ALTER_FULLTEXT_CATALOG
10054223| | | CREATE_FULLTEXT_CATALOG
10054234| | | DROP_FULLTEXT_CATALOG
1001610067| | DDL_FULLTEXT_STOPLIST_EVENTS
10067294| | | ALTER_FULLTEXT_STOPLIST
10067293| | | CREATE_FULLTEXT_STOPLIST
10067295| | | DROP_FULLTEXT_STOPLIST
1001610023| | DDL_FUNCTION_EVENTS
1002362| | | ALTER_FUNCTION
1002361| | | CREATE_FUNCTION
1002363| | | DROP_FUNCTION
1001610049| | DDL_PARTITION_EVENTS
1004910050| | | DDL_PARTITION_FUNCTION_EVENTS
10050192| | | | ALTER_PARTITION_FUNCTION
10050191| | | | CREATE_PARTITION_FUNCTION
10050193| | | | DROP_PARTITION_FUNCTION
1004910051| | | DDL_PARTITION_SCHEME_EVENTS
10051195| | | | ALTER_PARTITION_SCHEME
10051194| | | | CREATE_PARTITION_SCHEME
10051196| | | | DROP_PARTITION_SCHEME
1001610055| | DDL_PLAN_GUIDE_EVENTS
10055216| | | ALTER_PLAN_GUIDE
10055228| | | CREATE_PLAN_GUIDE
10055238| | | DROP_PLAN_GUIDE
1001610024| | DDL_PROCEDURE_EVENTS
1002452| | | ALTER_PROCEDURE
1002451| | | CREATE_PROCEDURE
1002453| | | DROP_PROCEDURE
1001610056| | DDL_RULE_EVENTS
10056219| | | BIND_RULE
10056229| | | CREATE_RULE
10056239| | | DROP_RULE
10056243| | | UNBIND_RULE
1001610069| | DDL_SEARCH_PROPERTY_LIST_EVENTS
10069298| | | ALTER_SEARCH_PROPERTY_LIST
10069297| | | CREATE_SEARCH_PROPERTY_LIST
10069299| | | DROP_SEARCH_PROPERTY_LIST
1001610070| | DDL_SEQUENCE_EVENTS
10070304| | | ALTER_SEQUENCE
10070303| | | CREATE_SEQUENCE
10070305| | | DROP_SEQUENCE
1001610041| | DDL_SSB_EVENTS
1004110063| | | DDL_BROKER_PRIORITY_EVENTS
10063282| | | | ALTER_BROKER_PRIORITY
10063281| | | | CREATE_BROKER_PRIORITY
10063283| | | | DROP_BROKER_PRIORITY
1004110043| | | DDL_CONTRACT_EVENTS
10043154| | | | CREATE_CONTRACT
10043156| | | | DROP_CONTRACT
1004110042| | | DDL_MESSAGE_TYPE_EVENTS
10042152| | | | ALTER_MESSAGE_TYPE
10042151| | | | CREATE_MESSAGE_TYPE
10042153| | | | DROP_MESSAGE_TYPE
1004110044| | | DDL_QUEUE_EVENTS
10044158| | | | ALTER_QUEUE
10044157| | | | CREATE_QUEUE
10044159| | | | DROP_QUEUE
1004110047| | | DDL_REMOTE_SERVICE_BINDING_EVENTS
10047175| | | | ALTER_REMOTE_SERVICE_BINDING
10047174| | | | CREATE_REMOTE_SERVICE_BINDING
10047176| | | | DROP_REMOTE_SERVICE_BINDING
1004110046| | | DDL_ROUTE_EVENTS
10046165| | | | ALTER_ROUTE
10046164| | | | CREATE_ROUTE
10046166| | | | DROP_ROUTE
1004110045| | | DDL_SERVICE_EVENTS
10045162| | | | ALTER_SERVICE
10045161| | | | CREATE_SERVICE
10045163| | | | DROP_SERVICE
1001610022| | DDL_SYNONYM_EVENTS
1002234| | | CREATE_SYNONYM
1002236| | | DROP_SYNONYM
1001610017| | DDL_TABLE_VIEW_EVENTS
1001710020| | | DDL_INDEX_EVENTS
10020213| | | | ALTER_FULLTEXT_INDEX
1002025| | | | ALTER_INDEX
10020224| | | | CREATE_FULLTEXT_INDEX
1002024| | | | CREATE_INDEX
10020274| | | | CREATE_SPATIAL_INDEX
10020206| | | | CREATE_XML_INDEX
10020235| | | | DROP_FULLTEXT_INDEX
1002026| | | | DROP_INDEX
1001710021| | | DDL_STATISTICS_EVENTS
1002127| | | | CREATE_STATISTICS
1002129| | | | DROP_STATISTICS
1002128| | | | UPDATE_STATISTICS
1001710018| | | DDL_TABLE_EVENTS
1001822| | | | ALTER_TABLE
1001821| | | | CREATE_TABLE
1001823| | | | DROP_TABLE
1001710019| | | DDL_VIEW_EVENTS
1001942| | | | ALTER_VIEW
1001941| | | | CREATE_VIEW
1001943| | | | DROP_VIEW
1001610025| | DDL_TRIGGER_EVENTS
1002572| | | ALTER_TRIGGER
1002571| | | CREATE_TRIGGER
1002573| | | DROP_TRIGGER
1001610028| | DDL_TYPE_EVENTS
1002891| | | CREATE_TYPE
1002893| | | DROP_TYPE
1001610048| | DDL_XML_SCHEMA_COLLECTION_EVENTS
10048178| | | ALTER_XML_SCHEMA_COLLECTION
10048177| | | CREATE_XML_SCHEMA_COLLECTION
10048179| | | DROP_XML_SCHEMA_COLLECTION
10016241| | RENAME
1000110002| DDL_SERVER_LEVEL_EVENTS
10002214| | ALTER_INSTANCE
1000210071| | DDL_AVAILABILITY_GROUP_EVENTS
10071307| | | ALTER_AVAILABILITY_GROUP
10071306| | | CREATE_AVAILABILITY_GROUP
10071308| | | DROP_AVAILABILITY_GROUP
1000210004| | DDL_DATABASE_EVENTS
10004202| | | ALTER_DATABASE
10004201| | | CREATE_DATABASE
10004203| | | DROP_DATABASE
1000210003| | DDL_ENDPOINT_EVENTS
10003182| | | ALTER_ENDPOINT
10003181| | | CREATE_ENDPOINT
10003183| | | DROP_ENDPOINT
1000210057| | DDL_EVENT_SESSION_EVENTS
10057265| | | ALTER_EVENT_SESSION
10057264| | | CREATE_EVENT_SESSION
10057266| | | DROP_EVENT_SESSION
1000210011| | DDL_EXTENDED_PROCEDURE_EVENTS
10011221| | | CREATE_EXTENDED_PROCEDURE
10011232| | | DROP_EXTENDED_PROCEDURE
1000210012| | DDL_LINKED_SERVER_EVENTS
10012263| | | ALTER_LINKED_SERVER
10012225| | | CREATE_LINKED_SERVER
1001210013| | | DDL_LINKED_SERVER_LOGIN_EVENTS
10013226| | | | CREATE_LINKED_SERVER_LOGIN
10013236| | | | DROP_LINKED_SERVER_LOGIN
10012262| | | DROP_LINKED_SERVER
1000210014| | DDL_MESSAGE_EVENTS
10014215| | | ALTER_MESSAGE
10014227| | | CREATE_MESSAGE
10014237| | | DROP_MESSAGE
1000210015| | DDL_REMOTE_SERVER_EVENTS
10015217| | | ALTER_REMOTE_SERVER
10015230| | | CREATE_REMOTE_SERVER
10015240| | | DROP_REMOTE_SERVER
1000210058| | DDL_RESOURCE_GOVERNOR_EVENTS
10058273| | | ALTER_RESOURCE_GOVERNOR_CONFIG
1005810059| | | DDL_RESOURCE_POOL
10059268| | | | ALTER_RESOURCE_POOL
10059267| | | | CREATE_RESOURCE_POOL
10059269| | | | DROP_RESOURCE_POOL
1005810060| | | DDL_WORKLOAD_GROUP
10060271| | | | ALTER_WORKLOAD_GROUP
10060270| | | | CREATE_WORKLOAD_GROUP
10060272| | | | DROP_WORKLOAD_GROUP
1000210005| | DDL_SERVER_SECURITY_EVENTS
10005209| | | ADD_SERVER_ROLE_MEMBER
10005301| | | ALTER_SERVER_ROLE
10005300| | | CREATE_SERVER_ROLE
1000510008| | | DDL_AUTHORIZATION_SERVER_EVENTS
10008204| | | | ALTER_AUTHORIZATION_SERVER
1000510009| | | DDL_CREDENTIAL_EVENTS
10009260| | | | ALTER_CREDENTIAL
10009259| | | | CREATE_CREDENTIAL
10009261| | | | DROP_CREDENTIAL
1000510061| | | DDL_CRYPTOGRAPHIC_PROVIDER_EVENTS
10061276| | | | ALTER_CRYPTOGRAPHIC_PROVIDER
10061275| | | | CREATE_CRYPTOGRAPHIC_PROVIDER
10061277| | | | DROP_CRYPTOGRAPHIC_PROVIDER
1000510007| | | DDL_GDR_SERVER_EVENTS
10007168| | | | DENY_SERVER
10007167| | | | GRANT_SERVER
10007169| | | | REVOKE_SERVER
1000510006| | | DDL_LOGIN_EVENTS
10006145| | | | ALTER_LOGIN
10006144| | | | CREATE_LOGIN
10006146| | | | DROP_LOGIN
1000510064| | | DDL_SERVER_AUDIT_EVENTS
10064285| | | | ALTER_SERVER_AUDIT
10064284| | | | CREATE_SERVER_AUDIT
10064286| | | | DROP_SERVER_AUDIT
1000510065| | | DDL_SERVER_AUDIT_SPECIFICATION_EVENTS
10065288| | | | ALTER_SERVER_AUDIT_SPECIFICATION
10065287| | | | CREATE_SERVER_AUDIT_SPECIFICATION
10065289| | | | DROP_SERVER_AUDIT_SPECIFICATION
1000510010| | | DDL_SERVICE_MASTER_KEY_EVENTS
10010251| | | | ALTER_SERVICE_MASTER_KEY
10005302| | | DROP_SERVER_ROLE
10005210| | | DROP_SERVER_ROLE_MEMBER

The data above can be created by running the following code example.

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;  

Event Notifications
DDL Triggers
DDL Events

Community Additions

ADD
Show:
© 2016 Microsoft