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.
|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)','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.
Help and Information
12 December 2006
17 July 2006