DDL Triggers | Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET

DDL triggers are new in SQL Server 2005. They can be fired after the engine invokes Data Definition Language (DDL) statements such as:

Create Table
Drop Table
Alter Procedure
Drop Schema
Create Login

DDL triggers are typically used to audit or control changes in database and server objects. In the following example, the trigger will prevent changes to any table in the current database:

 CREATE TRIGGER trdPreventTableChanges ON DATABASE FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE AS RAISERROR('This database contains sensitive data. Changes to the tables are typically not allowed and are rolled back by a trigger. If you do have a permission to change tables, temporarily disablethe trigger by using: DISABLE TRIGGER trdPreventTableChanges ON DATABASE <your batch with table changes> ENABLE TRIGGER trdPreventTableChanges ON DATABASE; ', 16, 1} ROLLBACK; GO

DDL triggers are After triggers, which means that:

You can replace the For keyword with After (as in the third line of the preceding example).
You have to explicitly invoke Rollback statements to cancel the changes made by DDL statements.

Since it is not an Instead-of trigger, you do not have to actually execute changes in the code yourself. A DDL trigger will not fire if there is another reason to prevent completion of a DDL modification statement. For example, if a table that you are trying to drop does not exist, SQL Server will not invoke a DDL trigger to log the identity of a user who issues a Drop Table statement.

Scope and Events of DDL Triggers

DDL triggers can be set to react on database or server objects. In the following example, the trigger will prevent the creation of new logins (server objects stored in the database):

 CREATE TRIGGER trdPreventLoginCreation ON ALL SERVER FOR CREATE_LOGIN AS RAISERROR('It is not allowed to add logins to this server without permission. Call DBA Security Group.', 16, 1) INSERT INTO [dbo].[ActivityLog]([Activity],[LogDate],[UserName]} VALUES ('LOGIN CHANGE', GetDate(},SYSTEM_USER) ROLLBACK GO

If you want to prevent any change on any login, instead of referencing all login events (CREATE_LOGIN, DROP_LOGIN, ALTER_LOGIN), you can reference the event group—DDL_LOGIN_EVENTS:

 CREATE TRIGGER trdPreventLoginChanges ON SERVER FOR DDL_LOGIN_EVENTS AS RAISERROR('It is not allowed to add logins to this server without permission. Call DBA Security Group.', 16, 1) ROLLBACK GO

Similarly, to reference all table modification statements you can use the DDL_ TAB LE_EVENTS group, and if you decide to fire a trigger after all table, view, index, and statistics modification events, you can use the DDL_TABLE_VIEW_EVENTS group. To see a complete list of events and their groups, search "Event Groups for Use with DDL Triggers" in Books OnLine.

Tip

To audit all DDL changes at the database level, use the DDL_DATABASE_LEVEL_EVENTS group. To track all security changes on the server, use the DDL_SERVER_SECURITY_EVENTS group. With these groups, you will find the answers to questions such as, "Who keeps changing a stored procedure after you deployed it?" and "Who is allowing developer access to the production database?"

EventData()

Since DDL triggers are not tied to table modifications, as in the case of DML triggers, they do not have Inserted and Deleted virtual tables. Instead, you can get information about the DDL modification statement that has fired the trigger using a special EventData() function. In the following example, changes to the table are allowed, but they are recorded for audit purposes in the special table:

 CREATE TRIGGER trdAuditTableChanges ON DATABASE FOR DDL_TABLE_EVENTS AS    declare @event varchar(max)    set @event = Convert(nvarchar(max), EVENTDATA())      INSERT INTO [dbo] . [ActivityLog] ( [Activity] , [LogDate] , [UserName] , [Note] )      VALUES ('TABLE CHANGE', GetDate(),SYSTEM_USER, @event) GO

When the statement that modifies a table is issued, EventData() will return data in the form of an XML document:

 <EVENT_INSTANCE>   <EventType>CREATE_TABLE</EventType>   <PostTime>2004-09-08T21:56:37.727</PostTime>   <SPID>52</SPID>   <ServerName>V505\C4</ServerName>   <LoginName>V505\dsunderic</LoginName>   <UserName>V505\dsunderic</UserName>   <DatabaseName>Asset5</DatabaseName>   <SchemaName >dbo </S chemaName >   <0bjectName>test</Obj ectName>   <0bj ectType>TABLE</Obj ectType>   <TSQLCommand>         <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"         ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />       <CommandText>create table test(id int)</CommandText>      </TSQLCommand> </EVENT_INSTANCE>

You can use XQuery methods to parse the content of an event document. In the following example, the Activity, UserName, and Note columns of the ActivityLog table were extracted from the EventType, LoginName, and TSQLCommand nodes of the event document:

 CREATE TRIGGER trdAuditTableChanges ON DATABASE AFTER DDL_TABLE_EVENTS AS declare @event xml set @event = EVENTDATA() INSERT INTO [dbo].[ActivityLog]([Activity],[LogDate],[UserName],[Note]} VALUES (@event.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),  GETDATE(}, @event.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'), @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ); GO

The preceding XQuery value() methods simply access the values of the specified nodes. The value() method is better suited for use in Transact-SQL than the alternative query() method, since it returns data with carriage return and linefeed (CR/LF) characters invisible. The query() method returns CR and LF in an ampersand-escaped sequence of characters. For more information on how to use XQuery, search "XQuery Against the XML Data Type" in Books OnLine.

Note

EventDATA() can be used only inside the DDL trigger. It is not guaranteed to return a meaningful result after a change is rolled hack or committed.

The function uses different schemas to return data when it is invoked by database and server modification statements. The schema also depends on the actual modification statement, since not all nodes are applicable for all events.

Syntax of DDL Triggers

DDL triggers are designed using the following syntax:

 CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ ENCRYPTION ] | [ EXECUTE AS CALLER | SELF | 'user_name' ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement  [ ; ]  [ ...n ] }

The On clause controls the scope of the trigger—it is tied to database or server events. Like stored procedure triggers, DDL triggers can be encrypted and executed alternatively by caller, schema owner, or specific database user.