DDL Events for Use with DDL Triggers

  • Article

The following tables list the DDL events that can be used to fire a DDL trigger. Note that each event corresponds to a Transact-SQL statement, with the statement syntax modified to include underscores ('_') between keywords.

Important

Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. Test your DDL triggers 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 that is created on a CREATE_TYPE event. However, the sp_rename stored procedure does not fire any DDL triggers.

DDL Statements with Database Scope

CREATE_APPLICATION_ROLE (Applies to 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 ALTER APPLICATION ROLE statement and sp_approlepassword.)

DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)

CREATE_ASSEMBLY

ALTER_ASSEMBLY

DROP_ASSEMBLY

ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)

CREATE_CERTIFICATE

ALTER_CERTIFICATE

 DROP_CERTIFICATE

CREATE_CONTRACT

DROP_CONTRACT

GRANT_DATABASE

DENY_DATABASE

REVOKE_DATABASE

CREATE_EVENT_NOTIFICATION

DROP_EVENT_NOTIFICATION

CREATE_FUNCTION

ALTER_FUNCTION

DROP_FUNCTION

CREATE_INDEX

ALTER_INDEX

DROP_INDEX

CREATE_MESSAGE_TYPE

ALTER_MESSAGE_TYPE

DROP_MESSAGE_TYPE

CREATE_PARTITION_FUNCTION

ALTER_PARTITION_FUNCTION

DROP_PARTITION_FUNCTION

CREATE_PARTITION_SCHEME

ALTER_PARTITION_SCHEME

DROP_PARTITION_SCHEME

CREATE_PROCEDURE

ALTER_PROCEDURE

DROP_PROCEDURE

CREATE_QUEUE

ALTER_QUEUE

DROP_QUEUE

CREATE_REMOTE_SERVICE_BINDING

ALTER_REMOTE_SERVICE_BINDING

DROP_REMOTE_SERVICE_BINDING

CREATE_ROLE (Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.)

ALTER_ROLE

DROP_ROLE (Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)

CREATE_ROUTE

ALTER_ROUTE

DROP_ROUTE

CREATE_SCHEMA (Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)

ALTER_SCHEMA (Applies to ALTER SCHEMA statement and sp_changeobjectowner.)

DROP_SCHEMA

CREATE_SERVICE

ALTER_SERVICE

DROP_SERVICE

CREATE_STATISTICS

DROP_STATISTICS

UPDATE_STATISTICS

CREATE_SYNONYM

DROP_SYNONYM

CREATE_TABLE

ALTER_TABLE

DROP_TABLE

CREATE_TRIGGER

ALTER_TRIGGER

DROP_TRIGGER

CREATE_TYPE (Applies to CREATE TYPE statement and sp_addtype.)

DROP_TYPE (Applies to DROP TYPE statement and sp_droptype.)

CREATE_USER (Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.)

ALTER_USER

DROP_USER (Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)

CREATE_VIEW

ALTER_VIEW

DROP_VIEW

 CREATE_XML_SCHEMA_COLLECTION

 ALTER_XML_SCHEMA_COLLECTION

DROP_XML_SCHEMA_COLLECTION

DDL Statements with Server Scope

ALTER_AUTHORIZATION_SERVER

CREATE_DATABASE

ALTER_DATABASE

DROP_DATABASE

CREATE_ENDPOINT

ALTER_ENDPOINT

DROP_ENDPOINT

CREATE_LOGIN (Applies to 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 ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)

DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)

GRANT_SERVER

DENY_SERVER

REVOKE_SERVER

See Also

Concepts

Designing DDL Triggers

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added an important note that recommends testing DDL triggers to determine their responses to system stored procedures that are executed. Also added the names of those stored procedures to their corresponding events.
  • Added ALTER_ENDPOINT event.