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.
Prev. page
[1]
2
next page 
