DDL Triggers

The concept of triggers is not new in SQL Server 2005. Almost every RDBMS supports a procedural T-SQL code, called a trigger, that is invoked when a Data Modification Language (DML) statement, such as INSERT, UPDATE, or DELETE, is executed. SQL Server 2005 expands this support, however, and now you can have a procedural T-SQL code executed when a DDL statement, such as CREATE TABLE, ALTER TABLE, DROP TABLE, and so on, is executed.

A DDL trigger can be defined at the database level or at the server level. A database-level DDL trigger allows you to capture events such as CREATE/ALTER/DROP TABLE, VIEW, USER, ROLE, and other DDL statements. A server-level DDL trigger allows you to respond to events such as CREATE/ALTER/DROP LOGIN, CERTIFICATE, and other server-level DDL statements. The syntax to create a DDL trigger is similar to that for creating a DML trigger, except that you specify whether it is a database-scoped (the ON DATABASE clause) or a server-scoped (the ON ALL SERVER clause) DDL trigger.

Here is an example of a script that creates a database-level DDL trigger to audit and prevent dropping of a table:

USE AdventureWorks; GO IF OBJECT_ID('dbo.tblDDLActions') IS NOT NULL    DROP TABLE dbo.tblDDLActions; GO CREATE TABLE dbo.tblDDLActions  (RecordID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,   Action XML,   ActiveUser NVARCHAR(100) DEFAULT SYSTEM_USER,   ActionDate DATETIME DEFAULT GETDATE()); GO IF EXISTS (SELECT name FROM sys.triggers            WHERE parent_class = 0 AND name = 'trgTest1')    DROP TRIGGER trgTest1    ON DATABASE; GO IF OBJECT_ID('dbo.tblTest') IS NOT NULL    DROP TABLE dbo.tblTest; GO CREATE TRIGGER trgTest1 ON DATABASE FOR DROP_TABLE AS BEGIN    DECLARE @eventData XML    SET @eventData = EVENTDATA();    ROLLBACK;    SET NOCOUNT ON;    INSERT INTO dbo.tblDDLActions (Action) VALUES (@eventData);    PRINT 'Dropping a table is not allowed';END; GO --Test the trigger CREATE TABLE dbo.tblTest(col1 int); GO BEGIN TRY    DROP TABLE tblTest; END TRY BEGIN CATCH    PRINT ERROR_MESSAGE(); END CATCH GO SELECT * FROM dbo.tblDDLActions; GO --Clean up DROP TRIGGER trgTest1 ON DATABASE; DROP TABLE dbo.tblTest; DROP TABLE dbo.tblDDLActions;

This script creates an audit table that is used to store details about who performed the DDL action and when, and what action (dropping a table in this case).

The EVENtdATA() function can be used inside a DDL trigger to find complete details about an action that led to the firing of the trigger. This function returns a value of xml data type. Here is an example of the XML document returned by the EVENtdATA() function for the DROP_TABLE database-level event:

 <EVENT_INSTANCE>     <EventType>DROP_TABLE</EventType>     <PostTime>2005-02-25T16:36:12.313</PostTime>     <SPID>51</SPID>     <ServerName>DDGXP\DECCTP</ServerName>     <LoginName>DDGXP\Darshan Singh</LoginName>     <UserName>dbo</UserName>     <DatabaseName>AdventureWorks</DatabaseName>     <SchemaName>dbo</SchemaName>     <ObjectName>tblTest</ObjectName>     <ObjectType>TABLE</ObjectType>     <TSQLCommand>         <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"                     QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />         <CommandText>DROP TABLE tblTest;</CommandText>     </TSQLCommand> </EVENT_INSTANCE>

You can save the XML returned by EVENTDATA() into a table, as is done in the preceding example, or directly execute XQuery queries on the value returned. XML data types and XQuery are discussed in detail in Chapter 10.

Here is an example of a server-level DDL trigger that prevents the creation of a SQL Server login. It allows the creation of Windows NT authenticated logins, but if it finds out that the login being created uses SQL authentication, it executes the ROLLBACK statement to abort the statement that fired the trigger:

IF EXISTS (SELECT name FROM sys.server_triggers            WHERE parent_class = 100 AND name = 'trgTest2')    DROP TRIGGER trgTest2    ON ALL SERVER; GO CREATE TRIGGER trgTest2 ON ALL SERVER FOR CREATE_LOGIN AS BEGIN    SET NOCOUNT ON;    IF 'SQL Login' =      EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]',                         'varchar(100)')    BEGIN       ROLLBACK;       PRINT 'Creating a SQL login is not allowed.';    END    ELSE    BEGIN       PRINT 'Creating Windows NT Authenticated logins is allowed.'; END END; GO --Test the trigger, should print error message BEGIN TRY    CREATE LOGIN DarshanSingh WITH PASSWORD = 'WjKkWjKf1234' END TRY BEGIN CATCH    PRINT ERROR_MESSAGE(); END CATCH GO --Test the trigger, should work fine BEGIN TRY    --Change the NT Login in the following statement    CREATE LOGIN [DDGXP\Guest] FROM WINDOWS; END TRY BEGIN CATCH    PRINT ERROR_MESSAGE(); END CATCH GO --Clean up DROP TRIGGER trgTest2 ON ALL SERVER; GO --Change the NT Login in the following statement DROP LOGIN [DDGXP\Guest]; GO

This script creates a server-scoped DDL trigger to handle the CREATE_LOGIN event. The trigger code uses the EVENtdATA() function to get an xml type value that contains the event information. It then uses the value() XML method and an XQuery expression to see if the login being created uses SQL authentication. If it does, it rolls back the transaction and prints an error message. The rest of the statements in this batch test the trigger by first trying to create a SQL authenticated login, which should fail, and a Windows NT authenticated login, which should succeed. Remember to change the NT login name in the preceding script to match your machine or domain in place of DDGXP.

Tip

You can use the sys.triggers catalog view to see database-level DDL triggers. The parent_class column in this catalog view has 0 as the value for DDL triggers. You can use the sys.server_triggers catalog view to get a list of server-level DDL triggers. The sys.trigger_events and sys.server_trigger_events catalog views contain a row for each time a database-level and server-level trigger was fired, respectively. Databaselevel DDL triggers are listed in the Programmability\Database Triggers folder in Object Explorer. The server-level triggers are listed under the Server Objects\Triggers folder in Object Explorer.

The ON DATABASE or ON ALL SERVER clause can be used with DROP TRIGGER to delete a DDL trigger, ALTER TRIGGER to modify a DDL trigger definition, DISABLE TRIGGER to disable a DDL trigger, and ENABLE TRIGGER to enable a DDL trigger. The CONTROL SERVER permission is required to create a DDL trigger at the server scope, and ALTER ANY TRIGGER permission is required to create a DDL trigger at the database scope.

For simplicity, SQL Server 2005 groups the related events into event groups on which you can define the triggers. For instance, instead of specifying FOR CREATE TABLE, ALTER TABLE, and DROP TABLE in the CREATE TRIGGER statement, you can just specify FOR DDL_TABLE_EVENTS, which includes the three table DDL statements.

In addition to having different purpose, DDL triggers differ from DML triggers in some ways. You cannot define an INSTEAD OF DDL trigger. The virtual inserted and deleted tables are not available and cannot be accessed inside the DDL trigger code; rather, the EVENTDATA() function is used to get the triggering event information, as illustrated previously.

Both DML triggers and DDL triggers are executed synchronously. This means that the action that leads to execution of a trigger waits until the trigger finishes executing. This is the reason DDL events such as CREATE DATABASE cannot be captured using DDL triggers: Such events are meant for asynchronous, non-transacted statements only.

SQL Server 2005 provides an alternative to DDL triggersa mechanism called event notifications, which can be used to handle DDL events asynchronously. In addition to DDL events, event notification can also be used to handle profiler trace events, as discussed in the following section.