Designing DDL Triggers

Before you can design a DDL trigger, the following is required:

• You have to understand DDL trigger scope.
• You have to determine which Transact-SQL statement, or group of statements, fires the trigger.

Security Note:
Malicious code inside triggers can run under escalated privileges. For more information on how to mitigate this threat, see Managing Trigger Security.

Understanding Trigger Scope

DDL triggers can fire in response to a Transact-SQL event processed in the current database, or on the current server. The scope of the trigger depends on the event. For example, a DDL trigger created to fire in response to a CREATE TABLE event will do so whenever a CREATE TABLE event occurs in the database. A DDL trigger created to fire in response to a CREATE LOGIN event will do so whenever a CREATE LOGIN event occurs in the server.

In the following example, DDL trigger safety will fire whenever a DROP TABLE or ALTER TABLE event occurs in the database:

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
   ROLLBACK
;

In the following example, a DDL trigger prints a message if any CREATE DATABASE event occurs on the current server instance. It uses the EVENTDATA function to retrieve the text of the corresponding Transact-SQL statement. For more information about how to use EVENTDATA with DDL Triggers, see Using the EVENTDATA Function.

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

The lists that map the Transact-SQL statements to the scopes that can be specified for them are available through the links provided in the section "Selecting a Particular DDL Statement to Fire a DDL Trigger," later in this topic.

Database-scoped DDL triggers are stored as objects in the database in which they are created. DDL triggers can be created in the master database and behave just like those created in user-designed databases. Information about DDL triggers can be obtained in the sys.triggers catalog view from within the database context in which they are created, or by specifying the database name as an identifier, such as master.sys.triggers.

Server-scoped DDL triggers are stored as objects in the master database. However, information about server-scoped DDL triggers can be obtained from the sys.server_triggers catalog view in any database context.

For more information about how to retrieve metadata for DDL triggers, see Getting Information About DDL Triggers.

DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.

Specifying a Transact-SQL Statement or Group of Statements

DDL triggers can be created to fire in response to the following events:

• One or more particular DDL statements
• A predefined group of DDL statements

Selecting a Particular DDL Statement to Fire a DDL Trigger

DDL triggers can be designed to fire after one or more particular Transact-SQL statements are run. In the previous example, trigger safety fires after any DROP TABLE or ALTER TABLE event.

Not all DDL events can be used to fire DDL triggers. Some events are intended for asynchronous, nontransacted statements only. For example, an ADD_ROLE_MEMBER event cannot be used to fire a DDL trigger. You should use event notifications for these events. For more information about event notifications, see Event Notifications (Database Engine).

The topic DDL Events for Use with DDL Triggers lists the Transact-SQL statements that can be specified to fire a DDL trigger, and the scope at which they can fire.

Selecting a Predefined Group of DDL Statements to Fire a DDL Trigger

A DDL Trigger can fire after execution of any Transact-SQL event that belongs to a predefined grouping of similar events. For example, if you want a DDL trigger to fire after any CREATE TABLE, ALTER TABLE, or DROP TABLE statement is run, you can specify FOR DDL_TABLE_EVENTS in the CREATE TRIGGER statement. After CREATE TRIGGER is run, the events that are covered by an event group are added to the sys.trigger_events catalog view.

The topic Event Groups for Use with DDL Triggers lists the predefined groups of DDL statements that are available for DDL Triggers, the particular statements they cover, and the scopes at which these event groups can be programmed.

See Also

Concepts

Using the EVENTDATA Function
Understanding DDL Triggers
Implementing DDL Triggers

Help and Information

Getting SQL Server 2005 Assistance

Change History