DDL Triggers

All SQL Server versions up to and including SQL Server 2000 are limited in their trigger support to statements that modify data (INSERT, UPDATE and DELETE). DBAs have always needed Data Definition Language (DDL) triggers that would fire as a result of a schema change to help accomplish such tasks as auditing schema changes, performing security-related activities, and verifying that schema changes adhere to company policies. So DBAs will be glad to learn that SQL Server 2005 finally supports DDL AFTER triggers (which fire after a DDL operation takes place). You can create database-level or server-level DDL triggers. In this article, I explain both types and give you a few useful examples that show how to use DDL AFTER triggers.

Database-Level DDL Triggers

Database-level DDL triggers let you respond to database-level DDL events such as creating, altering, and dropping objects. You can create triggers to respond to specific events such as CREATE_TABLE and ALTER_TABLE statements. To respond to any event that's part of an event group, you can create a trigger such as DDL_DATABASE_LEVEL_EVENTS, which covers all database-related DDL events (e.g., creating, dropping and altering objects in the database), or DDL_TABLE_EVENTS, which covers all table related DDL events (e.g., adding, dropping or altering columns in the table). Please refer to SQL Server Books Online (BOL) for the full list of event groups.The best way to demonstrate how DDL triggers work is to jump right to our first example. Suppose you have a production database in which the tables contain sensitive data. Run the following code to create a database called testdb and a table called SensitiveTable:

CREATE DATABASE testdb;GOUSE testdb;CREATE TABLE SensitiveTable(col1 INT);

In the past, you might have had situations in which DBAs accidentally dropped or altered tables in the production database because they thought they were connected to the development database. You want to prevent all attempts at dropping and altering production tables unless the DBA is certain that the operation is intentional. To achieve this safety measure, you can create a safety trigger that rolls back all DROP TABLE and ALTER TABLE attempts, then sends a message saying that the database contains sensitive data and the safety trigger must be disabled before the schema change.

Run the code in Listing 1 to create the safety trigger. The trigger's code is straightforward. You create it on the database for DROP_TABLE and ALTER_TABLE events, and the trigger simply generates a message that says that before issuing any DROP TABLE or ALTER TABLE statement, you must disable the trigger, then re-enable it after the change. The trigger then rolls back the schema-change attempt. To test the trigger, try to drop the SensitiveData table by running the following command:

DROP TABLE SensitiveTable;

The safety trigger rolls back the table-creation attempt, and you get the following message:

This database contains sensitive tables.A trigger protects the tables from being dropped or altered.If you are sure the DROP/ALTER operation is safe, disable the trigger first:DISABLE TRIGGER trg_protect_sensitivetables ON DATABASE;;ENABLE TRIGGER trg_protect_sensitivetables ON DATABASE;.Msg 3609, Level 16, State 2, Line 1Transaction ended in trigger. Batch has been aborted.

To successfully drop the table for this example, disable the trigger, drop the table, then re-enable the trigger by using the following commands:

DISABLE TRIGGER trg_protect_sensitivetables ON DATABASE;DROP TABLE SensitiveTable;ENABLE TRIGGER trg_protect_sensitivetables ON DATABASE;

To drop the trigger, run the following code:

DROP TRIGGER trg_protect_sensitivetables ON DATABASE;

SQL Server 2005 also gives you a lot of information about the event that fired the trigger. To get this information, you can invoke EventData() within the trigger and query the XML data that the EventData() function returns. The XML data contains a lot of useful information, including the event post time; the server process id (SPID) of the session that issued the DDL statement; the event type; schema and object names; the object type; the T-SQL statement that fired the trigger; and the state of a session's SET options.

You can use the XML data describing the event to determine a course of action depending on your findings. SQL Server 2005 lets you query XML data by using the W3C standard XML query language, XQuery. The parts of the XQuery language that you need to learn to extract event information are so simple that you really don't need to become an XML or XQuery expert. My next example shows how simple it is.

Suppose your company has a policy that restricts table and column names to 30 characters. You want to enforce this policy with a trigger. Run the code that Listing 2 shows to create this trigger. The trigger's code first declares some local variables, then stores in the XML local variable @eventdata the XML data that the EventData() function returns.

The code then issues a series of assignments that extract into local variables the table name, schema name, and event type from the XML data that's using XQuery. To extract a particular element from XML data by using XQuery, you use the following query method:

xmldata.query('data(//element)')

For example, to extract the ObjectName element from @eventdata, you use the following expression:

@eventdata.query('data(//ObjectName)'

Then, you just convert the result to the desired data type and length and assign it to a local variable for later use.Next the trigger's code determines whether the table name is longer than 30 characters and if it is, constructs an error message in the @msg variable and turns on a flag that says that the trigger needs to be aborted.

The trigger's code goes on to determine whether at least one column in the target table has a name longer than 30 characters. If such a column exists, the code appends an error message to the @msg variable (along with the long column names) and turns on the abort flag.

Finally, the trigger checks to see whether the abort flag is turned on. If the flag is on, the trigger raises the error message in the @msg variable and rolls back the activity that fired the trigger. To test Listing 2's trigger, issue the following code, which attempts to create a table with a long table name and long column names:

CREATE TABLE this_is_a_long_table_name_with_more_than_30_characters(  this_is_a_long_column_name_with_more_than_30_characters INT,  this_is_another_long_column_name_with_more_than_30_characters INT);

You'll get the following error message, which states that the table name exceeds the maximum length, and a list of column names that are longer than the maximum.

Msg 50000, Level 16, State 1, Procedure trg_enforce_table_policy, Line 48Maximum allowed table name length is 30.Table name: this_is_a_long_table_name_with_more_than_30_characters is longer than the allowed maximum.Maximum allowed column name length is 30.The following column names are longer than the allowed maximum:this_is_a_long_column_name_with_more_than_30_charactersthis_is_another_long_column_name_with_more_than_30_charactersCREATE_TABLE statement rolled back.Please revise table and column names to meet the requirements and rerun the statement.Msg 3609, Level 16, State 2, Line 1Transaction ended in trigger. Batch has been aborted.

Next, run the following code, which creates a table with short table and column names:

CREATE TABLE short_table_name(  short_col_name1 INT,  short_col_name2 INT);

The code should successfully create the table. Now, try to add a column with a name longer than 30 characters. When you run the following code

ALTER TABLE short_table_name ADD this_is_an_attempt_to_add_a_column_with_a_long_name INT;

you should get the following error message:

Msg 50000, Level 16, State 1, Procedure trg_enforce_table_policy, Line 48Maximum allowed column name length is 30.The following column names are longer than the allowed maximum:this_is_an_attempt_to_add_a_column_with_a_long_nameALTER_TABLE statement rolled back.Please revise table and column names to meet the requirements and rerun the statement.Msg 3609, Level 16, State 2, Line 1Transaction ended in trigger. Batch has been aborted.

As you can see, Listing 2's trigger prevents all CREATE and ALTER TABLE attempts that don't meet the company's policy for table- and column-name lengths. To drop the trigger, issue the following DROP TRIGGER statement:

DROP TRIGGER trg_enforce_table_policy ON DATABASE;

Server-Level DDL Triggers

Just as you can with database-level DDL triggers, you can create server-level triggers that respond to individual server level DDL events or event groups. Individual server events include CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN, and others. DDL_LOGIN_EVENTS and DDL_SERVER_SECURITY_EVENTS are examples of event groups.

Let's walk through an example of a server-level DDL trigger. Suppose that for security reasons, you want to prevent any DDL login events between midnight and 8:00 AM. You set up this restriction by running the code in Listing 3 to create a trigger that checks the hour in the current date/time that the GETDATE function returns. If the hour is between 0 and 7, the trigger raises an error message and rolls back the DDL login activity. To test the trigger, change your computer's clock to a time between midnight and 8:00 AM, and run the following code:

CREATE LOGIN login1 WITH PASSWORD = '123';

You should get an error message indicating that the operation isn't allowed, and the code will roll back the operation. Now, change the computer's clock to a time between 8:00 AM and midnight, and run the preceding code again. You should be able to successfully create the login.

Enhanced Automation Capabilities

SQL Server 2005 significantly enhances your server's automatic response capabilities by letting you create triggers that respond to DDL events. You can now audit every schema change both at the server and database levels, enforce schema-related company policies, or make sure that your server will automatically respond with a reactive action of your choice following DDL events in your database.