Designing DDL Triggers
Before you can design a DDL trigger, the following is required:
You must understand the scope of the DDL trigger.
You must determine which Transact-SQL statement, or group of statements, fires the trigger.
.gif "Security note") Security Note |
|---|
Malicious code inside triggers can run under escalated privileges. For more information about how to help reduce this threat, see Managing Trigger Security. |
Note
DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.
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 can do so whenever a CREATE_TABLE event occurs in the database, or on the server instance. A DDL trigger created to fire in response to a CREATE_LOGIN event can do so only when 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. The example 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. You can obtain information about DDL triggers by querying the sys.triggers catalog view. You can query sys.triggers within the database context in which the triggers 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, you can obtain information about server-scoped DDL triggers by querying 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.
Specifying a Transact-SQL Statement or Group of Statements
DDL triggers can be created to fire in response to one or more particular DDL statements, or to 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. For lists of the Transact-SQL statements that can be specified to fire a DDL trigger, and the scope at which the trigger can fire, see DDL Events.
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.
Note
In SQL Server 2005, if a trigger is created on an event group, sys.trigger_events does not include information about the event group, sys.trigger_events includes information only about the individual events covered by that group. In SQL Server 2008, sys.trigger_events persists metadata about the event group on which the triggers is created, and also about the individual events that the event group covers. Therefore, changes to the events that are covered by event groups in SQL Server 2008 do not apply to DDL triggers that are created on those event groups in SQL Server 2005.
For a list of the predefined groups of DDL statements that are available for DDL triggers, the particular statements the event groups cover, and the scopes at which these event groups can be programmed, see DDL Event Groups.