DDL Events
Collapse the table of content
Expand the table of content

DDL Events

 

Applies To: SQL Server 2016

The following tables list the DDL events that can be used to fire a DDL trigger or event notification. Note that each event corresponds to a Transact-SQL statement or stored procedure, with the statement syntax modified to include an underscore character (_) between keywords.

System_CAPS_ICON_important.jpg Important


System stored procedures that perform DDL-like operations also fire DDL triggers and 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.

DDL triggers or event notifications can be created to fire in response to the following events when they occur in the database in which the trigger or event notification is created, or anywhere in the server instance.

CREATE_APPLICATION_ROLE (Applies to the CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.)ALTER_APPLICATION_ROLE (Applies to the ALTER APPLICATION ROLE statement and sp_approlepassword.)DROP_APPLICATION_ROLE (Applies to the DROP APPLICATION ROLE statement and sp_dropapprole.)
CREATE_ASSEMBLYALTER_ASSEMBLYDROP_ASSEMBLY
CREATE_ASYMMETRIC_KEYALTER_ASYMMETRIC_KEYDROP_ASYMMETRIC_KEY
ALTER_AUTHORIZATIONALTER_AUTHORIZATION_DATABASE (Applies to the ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)
CREATE_BROKER_PRIORITYCREATE_BROKER_PRIORITYCREATE_BROKER_PRIORITY
CREATE_CERTIFICATEALTER_CERTIFICATEDROP_CERTIFICATE
CREATE_CONTRACTDROP_CONTRACT
CREATE_CREDENTIALALTER_CREDENTIALDROP_CREDENTIAL
GRANT_DATABASEDENY_DATABASEREVOKE_DATABASE
CREATE_DATABASE_AUDIT_SPEFICIATIONALTER_DATABASE_AUDIT_SPEFICIATIONDENY_DATABASE_AUDIT_SPEFICIATION
CREATE_DATABASE_ENCRYPTION_KEYALTER_DATABASE_ENCRYPTION_KEYDROP_DATABASE_ENCRYPTION_KEY
CREATE_DEFAULTDROP_DEFAULT
BIND_DEFAULT (Applies to sp_bindefault.)UNBIND_DEFAULT (Applies to sp_unbindefault.)
CREATE_EVENT_NOTIFICATIONDROP_EVENT_NOTIFICATION
CREATE_EXTENDED_PROPERTY (Applies to sp_addextendedproperty.)ALTER_EXTENDED_PROPERTY (Applies to sp_updateextendedproperty.)DROP_EXTENDED_PROPERTY (Applies to sp_dropextendedproperty.)
CREATE_FULLTEXT_CATALOG (Applies to the CREATE FULLTEXT CATALOG statement and sp_fulltextcatalog when create is specified.)ALTER_FULLTEXT_CATALOG (Applies to the ALTER FULLTEXT CATALOG statement, sp_fulltextcatalog when start_incremental, start_full, Stop, or Rebuild is specified, and sp_fulltext_database when enable is specified.)DROP_FULLTEXT_CATALOG (Applies to the DROP FULLTEXT CATALOG statement and sp_fulltextcatalog when drop is specified.)
CREATE_FULLTEXT_INDEX (Applies to the CREATE FULLTEXT INDEX statement and sp_fulltexttable when create is specified.)ALTER_FULLTEXT_INDEX (Applies to the ALTER FULLTEXT INDEX statement, sp_fulltextcatalog when start_full, start_incremental, or stop is specified, sp_fulltext_column, and sp_fulltext_table when any action other than create or drop is specified.)DROP_FULLTEXT_INDEX (Applies to the DROP FULLTEXT INDEX statement and sp_fulltexttable when drop is specified.)
CREATE_FULLTEXT_STOPLISTALTER_FULLTEXT_STOPLISTDROP_FULLTEXT_STOPLIST
CREATE_FUNCTIONALTER_FUNCTIONDROP_FUNCTION
CREATE_INDEXALTER_INDEX (Applies to the ALTER INDEX statement and sp_indexoption.)DROP_INDEX
CREATE_MASTER_KEYALTER_MASTER_KEYDROP_MASTER_KEY
CREATE_MESSAGE_TYPEALTER_MESSAGE_TYPEDROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTIONALTER_PARTITION_FUNCTIONDROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEMEALTER_PARTITION_SCHEMEDROP_PARTITION_SCHEME
CREATE_PLAN_GUIDE (Applies to sp_create_plan_guide.)ALTER_PLAN_GUIDE (Applies to sp_control_plan_guide when ENABLE, ENABLE ALL, DISABLE, or DISABLE ALL is specified.)DROP_PLAN_GUIDE (Applies to sp_control_plan_guide when DROP or DROP ALL is specified.)
CREATE_PROCEDUREALTER_PROCEDURE (Applies to the ALTER PROCEDURE statement and sp_procoption.)DROP_PROCEDURE
CREATE_QUEUEALTER_QUEUEDROP_QUEUE
CREATE_REMOTE_SERVICE_BINDINGALTER_REMOTE_SERVICE_BINDINGDROP_REMOTE_SERVICE_BINDING
CREATE_SPATIAL_INDEX
RENAME (Applies to sp_rename)
CREATE_ROLE (Applies to the CREATE ROLE statement, sp_addrole, and sp_addgroup.)ALTER_ROLEDROP_ROLE (Applies to the DROP ROLE statement, sp_droprole, and sp_dropgroup.)
ADD_ROLE_MEMBERDROP_ROLE_MEMBER
CREATE_ROUTEALTER_ROUTEDROP_ROUTE
CREATE_RULEDROP_RULE
BIND_RULE (Applies to sp_bindrule.)UNBIND_RULE (Applies to sp_unbindrule.)
CREATE_SCHEMA (Applies to the CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)ALTER_SCHEMA (Applies to the ALTER SCHEMA statement and sp_changeobjectowner.)DROP_SCHEMA
CREATE_SEARCH_PROPERTY_LISTALTER_SEARCH_PROPERTY_LISTDROP_SEARCH_PROPERTY_LIST
CREATE_SEQUENCE_EVENTSCREATE_SEQUENCE_EVENTSCREATE_SEQUENCE_EVENTS
CREATE_SERVER_ROLEALTER_SERVER_ROLEDROP_SERVER_ROLE
CREATE_SERVICEALTER_SERVICEDROP_SERVICE
ALTER_SERVICE_MASTER_KEYBACKUP_SERVICE_MASTER_KEYRESTORE_SERVICE_MASTER_KEY
ADD_SIGNATURE (for signature operations on non-schema scoped objects; database, assembly, trigger)DROP_SIGNATURE
ADD_SIGNATURE_SCHEMA_OBJECT (for schema scoped objects; stored procedures, functions)DROP_SIGNATURE_SCHEMA_OBJECT
CREATE_SPATIAL_INDEXALTER_INDEX can be used for spatial indexes.DROP_INDEX can be used for spatial indexes.
CREATE_STATISTICSDROP_STATISTICSUPDATE_STATISTICS
CREATE_SYMMETRIC_KEYALTER_SYMMETRIC_KEYDROP_SYMMETRIC_KEY
CREATE_SYNONYMDROP_SYNONYM
CREATE_TABLEALTER_TABLE (Applies to the ALTER TABLE statement and sp_tableoption.)DROP_TABLE
CREATE_TRIGGERALTER_TRIGGER (Applies to the ALTER TRIGGER statement and sp_settriggerorder.)DROP_TRIGGER
CREATE_TYPE (Applies to the CREATE TYPE statement and sp_addtype.)DROP_TYPE (Applies to the DROP TYPE statement and sp_droptype.)
CREATE_USER (Applies to the CREATE USER statement, sp_adduser, and sp_grantdbaccess.)ALTER_USER (Applies to ALTER USER statement and sp_change_users_login.)DROP_USER (Applies to the DROP USER statement, sp_dropuser, and sp_revokedbaccess.)
CREATE_VIEWALTER_VIEWDROP_VIEW
CREATE_XML_INDEXALTER_INDEX can be used for XML indexes.DROP_INDEX can be used for XML indexes.
CREATE_XML_SCHEMA_COLLECTIONALTER_XML_SCHEMA_COLLECTIONDROP_XML_SCHEMA_COLLECTION

DDL triggers or event notifications can be created to fire in response to the following events when they occur anywhere in the server instance.

ALTER_AUTHORIZATION_SERVERALTER_SERVER_CONFIGURATIONALTER_INSTANCE (Applies to sp_configure and sp_addserver when a local server instance is specified.)
CREATE_AVAILABILITY_GROUPALTER_AVAILABILITY_GROUPDROP_AVAILABILITY_GROUP
CREATE_CREDENTIALALTER_CREDENTIALDROP_CREDENTIAL
CREATE_CRYPTOGRAPHIC_PROVIDERALTER_CRYPTOGRAPHIC_PROVIDERDROP_CRYPTOGRAPHIC_PROVIDER
CREATE_DATABASEALTER_DATABASE (Applies to the ALTER DATABASE statement and sp_fulltext_database.)DROP_DATABASE
CREATE_ENDPOINTALTER_ENDPOINTDROP_ENDPOINT
CREATE_EVENT_SESSIONALTER_EVENT_SESSIONDROP_EVENT_SESSION
CREATE_EXTENDED_PROCEDURE (Applies to sp_addextendedproc.)DROP_EXTENDED_PROCEDURE (Applies to sp_dropextendedproc.)
CREATE_LINKED_SERVER (Applies to sp_addlinkedserver.)ALTER_LINKED_SERVER (Applies to sp_serveroption.)DROP_LINKED_SERVER (Applies to sp_dropserver when a linked server is specified.)
CREATE_LINKED_SERVER_LOGIN (Applies to sp_addlinkedsrvlogin.)DROP_LINKED_SERVER_LOGIN (Applies to sp_droplinkedsrvlogin.)
CREATE_LOGIN (Applies to the CREATE LOGIN statement, sp_addlogin, sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.)ALTER_LOGIN (Applies to the ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)DROP_LOGIN (Applies to the DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)
CREATE_MESSAGE (Applies to sp_addmessage.)ALTER_MESSAGE (Applies to sp_altermessage.)DROP_MESSAGE (Applies to sp_dropmessage.)
CREATE_REMOTE_SERVER (Applies to sp_addserver.)ALTER_REMOTE_SERVER (Applies to sp_setnetname.)DROP_REMOTE_SERVER (Applies to sp_dropserver when a remote server is specified.)
CREATE_RESOURCE_POOLALTER_RESOURCE_POOLDROP_RESOURCE_POOL
GRANT_SERVERDENY_SERVERREVOKE_SERVER
ADD_SERVER_ROLE_MEMBERDROP_SERVER_ROLE_MEMBER
CREATE_SERVER_AUDITALTER_SERVER_AUDITDROP_SERVER_AUDIT
CREATE_SERVER_AUDIT_SPECIFICATIONALTER_SERVER_AUDIT_SPECIFICATIONDROP_SERVER_AUDIT_SPECIFICATION
CREATE_WORKLOAD_GROUPCREATE_WORKLOAD_GROUPCREATE_WORKLOAD_GROUP

DDL Triggers
Event Notifications
DDL Event Groups

Community Additions

ADD
Show:
© 2016 Microsoft