Logging DDL Events

My team is creating a solution that stores data in a Microsoft SQL Server 2005 database. Because of the nature of the project, numerous developers are creating and deleting database objects and updating the database. Is there an easy way to log these events to track which developers are doing what?

The simplest way to log Data Definition Language (DDL) events is to take advantage of the new DDL trigger feature in SQL Server 2005. A trigger is a type of stored procedure that fires automatically when a specified event occurs. For earlier versions of SQL Server, these events are based on Data Manipulation Language (DML) statements that insert, update, and delete data. However, with SQL Server 2005, you can create triggers that fire when specified DDL events occur, such as modifying column definitions or dropping login accounts.

You can define a DDL trigger with a server scope or a database scope. A server scope responds to server-level events. For example, you can define a trigger that fires whenever a user creates,alters, or deletes a certificate. A database scope responds to database-level events specific to the database in which you define the trigger. Such events can include nearly any DDL event that occurs within that database, such as dropping tables or altering view definitions.

To log the events that your developers generate, you should create a DDL trigger with a database scope. However, before you do that, you should create a table to store event information. At a minimum, the table should include a primary key column and an XML column, as callout A in Listing 1 shows.

The EventInfo column is defined as type XML because SQL Server 2005 returns event data as XML. You can choose not to define the column as XML, but then you must convert the data as you insert it into the table. As you refine your auditing strategy, you might find it preferable to define the column as a different type, but for the purpose of this example, I use XML.

As you can see in callout B in Listing 1, the code defines the TestEvents table. The purpose of this table is merely to provide a way to test the trigger. You can delete this code after you've fully tested your trigger and viewed the logged events.

After you create the necessary tables, you can create the DDL trigger. The trigger must be created within the database in which the DDL events occur, which, in this case, is the Test-Data database. Callout C in Listing 1 shows the basic components of a DDL trigger. You begin with the CREATE TRIGGER clause, which identifies the name of the trigger (LogDDLEvents). Next, you specify a scope, which is either ON ALL SERVER or ON DATABASE. As you would guess, the ON ALL SERVER clause defines a server-level scope, and the ON DATABASE clause defines a database-level scope.

Next you must identify when the trigger will fire. You can specify a FOR clause or an AFTER clause. If you specify FOR, the trigger fires as soon as the triggering event occurs. If you specify AFTER, the trigger fires after the entire triggering SQL statement runs. Within the FOR or AFTER clause, you must also define the events that will cause the trigger to fire. You can define one or more events (separated by commas) or define an event group, which contains a set of individual events. In this case, you should use the DDL_DATABASE_LEVEL_EVENTS event group to catch all the DDL events in the database. (For a list of events and event groups, see SQL Server 2005 Books Online—BOL.)

Finally, you must specify the SQL statement that should run when the trigger fires. Callout C uses an INSERT statement to add data to the DDLEvents table. The statement uses the EVENTDATA system function to retrieve data (in XML format) about the last event to occur. You can see an example of the event data in Listing 2, which shows the XML data returned by the ALTER TABLE statement at callout D in Listing 1.

After you create the trigger, it inserts event data into the DDLEvents table whenever a DDL event occurs. You can view the event data by running a T-SQL SELECT statement that includes XQuery elements. For example, suppose that you want to view information about the event that occurs as a result of running the ALTER TABLE statement in callout D. Specifically, you want to know which object was affected and what SQL statement ran against the object. Listing 3 shows a sample XQuery statement that retrieves this data.

The code in Listing 3 begins by declaring the @LogID (as type INT) and @EventInfo (as type XML) variables. The code then uses the MAX system function to assign the highest LogID value in the DDLEvents table to the @LogID variable. Next, the statement assigns the full XML value from a single row in the table to the @EventInfo variable. The row returned is based on the value assigned to the @LogID variable. In other words, the code assigns the EventInfo XML value from the last inserted row to the @EventInfo variable.

After you set the variables, you define a SELECT statement that retrieves the necessary data. Each column specified in the selected list corresponds to an XML data element. The SELECT statement in Listing 3 returns two columns of data: Object-Name and SqlStatement. The Object-Name column corresponds to the ObjectName XML element. The Sql-Statement column corresponds to the TSQLCommand/CommandText element. You reference the elements in your statements based on their position in the XML data, relative to the EVENT_INSTANCE base element. (Listing 2 shows these elements and their positions in the XML hierarchy.)

For each column specified in the SELECT statement, you should use XQuery's value method to extract specific data from the XML element. The method takes two parameters. The first parameter uses the data function to specify that you should extract data from the specified element. For the first column, the data function extracts the value from the ObjectName element. Notice that [1] follows the data function. Because XQuery is a statically typed language, you must use the [1] to specify that the path expression returns a singleton value. Without the [1], the SQL Server compiler doesn't know how many ObjectName nodes will occur at runtime.

The value method's second parameter specifies the data type that should be used for the returned value. For the two specified columns, the method converts the values to NVARCHAR data types.

After you've defined the necessary columns in your SELECT statement, you can run your query. Table 1 shows the results that you should expect. However, if you've run other DDL statements since creating the trigger, you'll see different results. You can easily create a query that returns other XML elements from the DDLEvents table, such as the LoginName and UserName elements. By using DDL triggers, along with the EVENTDATA function and XQuery statements, you can implement a simple, yet useful, solution to track all your DDL events.