Learn SQL Server 2005 T-SQL Enhancements : Page 3

DDL Triggers
DML (data manipulation language) triggers have always been an effective way to take action when changes are made to your data. DML permits you to take a number of actions such as updating an audit log of data updates. Triggers, though, are useless when it comes to tracking and auditing changes made to your database schema. For example, suppose one member of your development team adds a new field while another team member changes the size of one field from VARCHAR(20) to VARCHAR(30). In SQL Server 2000 there is no way to track these types of schema updates without the use of a third-party tool. SQL Server 2005 allows you to track such updates with the addition of DDL (data definition language) triggers. As an added bonus, the syntax for DDL triggers is very similar to the syntax for DML triggers. In Listing 16, a CREATE TRIGGER command creates a DDL trigger to prevent a table from being dropped. The event type (FOR DROP_TABLE) determines which database event this trigger is going to be associated with. To ensure the trigger runs on any DDL change, use DDL_DATABASE_LEVEL_EVENTS.

The EVENTDATA Function

In SQL Server 2000, there is no way to track these types of schema updates without the use of a third-party tool. This changes in SQL Server 2005 with the addition of DDL (data definition language) triggers.

DML triggers use the Inserted and/or Deleted virtual tables to determine the before and after values of an updated record. A DDL trigger uses the EVENTDATA() function to return information about the database event that caused the trigger to fire. EVENTDATA() returns an XML value and based on the type of event, the XML contains different data. There are four pieces of information that are contained in the XML regardless of the DDL command that fired the trigger.

• The event type
• The time of the event
• The SPID of the connection
• Login and user name information about the individual executing the DDL command

SQL Server Books Online contains more information regarding the data returned by EVENTDATA(). In Listing 17, a CREATE TABLE command creates the AuditLog table and then a CREATE TRIGGER command creates a trigger that adds a record to the AuditLog table whenever any database level event occurs.

Common Table Expressions (CTE)
A Common Table Expression (CTE) is a temporary result set created from a simple query. They are implemented by an expression that creates a table that can be referred to by name. If a CTE sounds like a view or possibly a derived table then you are on the right page. The syntax of a CTE is similar to a view. A SELECT statement based on data contained in the PurchaseOrderDetail table creates the CTE named PurchaseOrderCTE (see Listing 18). You can see that after the CTE is created another SELECT command draws its rows from the CTE.

Why would you want to use a CTE over a view or derived table? One of the features of a CTE is the ability to create recursive query expressions. Listing 19 uses a CTE to list a manager and all the employees that report to them. It loops through each employee reporting to the manager and then loops through each employee reporting to the current employee and then loops through each employee reporting to the current employee and then, OK, you get the idea. Using recursion, each employee is listed who has employees reporting to them. Recursive queries make working with hierarchal data, such as manager/employee relationships or a Bill of Materials much easier than prior versions of SQL Server.

Recursive queries make working with hierarchal data, such as manager/employee relationships or a Bill of Materials much easier than prior versions of SQL Server.

I hope this gives you a taste of some of the new and exciting T-SQL features being added to SQL Server 2005. I hope this article has piqued your curiosity sufficiently that you will go out and investigate what else SQL Server 2005 has to offer.

If you are interested in getting a jump on learning SQL Server 2005, then you owe it to yourself to pick up a copy of A First Look at SQL Server 2005 for Developers," by Bob Beauchemin, Niels Berglund, and Dan Sullivan.

Previous Page