The way around this, of course, was to drop the foreign key constraints on your tables and manage all the referential integrity in triggers. Simple triggers like the one above quickly grew into multi-page maintenance nightmares. What a drag!

This is all fixed in SQL Server 2000. Now, with just two clicks of the mouse from the table properties dialog, as shown in Figure 1, you can have SQL Server take care of synchronizing your master and child tables.

Figure 1: Adding cascading updates and deletes to the Customers table.

Once you have enabled cascading updates and deletes, when you delete a customer, SQL Server will automatically remove all the orders associated with that customer. No fuss, no muss.

Beside the obvious benefit of reducing the tedious code we developers need to write and enabling us to focus our time and energy on more interesting tasks, cascading updates and deletes has the added benefit of increasing the performance of our applications. Remember, all that T/SQL we built into the triggers had to be parsed, optimized, and executed at run time. Now, these cascading actions are built into the table definitions and occur inside the SQL Server engine.

For a very interesting discussion of the interaction among constraints, cascading actions, and the SQL Server 2000 system tables, see Chapter 6 of Inside Microsoft SQL Server 2000 (0-7356-0998-5).

When Microsoft first announced that SQL Server 2000 would finally include cascading referential integrity, I commented that this would be the end of triggers; I called it the trigger-be-gone feature. It turns out I was wrong. While it will be the end of triggers as we currently use them, we will now start to use triggers in a completely different manner, because the SQL Server 2000 development team added two new types of triggers to our arsenal of development tools.

Trigger Enhancements
In SQL Server 7.0 Microsoft added the ability to define multiple triggers per table. This was a boon to those developers working on team projects, because now multiple developers could work on their own triggers without having to worry about overwriting other people's work. Microsoft has extended this model with SQL Server 2000 by adding INSTEAD OF and AFTER triggers.

Unlike triggers in SQL Server 7.0 and the AFTER triggers in SQL Server 2000, an object can only have a single INSTEAD OF trigger. Another important difference is that an INSTEAD OF trigger fires instead of the triggering event. In other words, only the trigger executes; the original statement does not. INSTEAD OF triggers have two main uses:

• Inserting data into views that would otherwise not be updatable
  
• Pre-processing the results of statements and taking specific actions depending on the results

Let's look at examples of these two cases, starting off with adding new rows to a view.

Using INSTEAD OF Triggers to Update a Non-Updateable View
For this example, I created a very simple database for tracking temperatures. It has two tables: a header table listing locations and a detail table tracking the temperature readings at those locations. The text in Listing 2 contains a simplified version of SQL statements; if you want, you can download the complete version from my Web site. The two tables are defined as follows:

Listing 2: Creating the Sample Tables

CREATE 
TABLE tblTemperature (
     TemperatureID  INT 
IDENTITY(1, 1)  NOT NULL,
     
LocationID     
INT                 
NOT NULL,
     DateTaken      
DATETIME            NOT 
NULL,
     
Celsius        
INT                 
NOT NULL
 
CREATE TABLE tblLocation (
     
LocationID     INT IDENTITY(1, 1)  NOT 
NULL,
     LocationName   
VARCHAR(20)         NOT NULL)

Finally, I created a view in Listing 3 to display the temperature readings in both Celsius and Fahrenheit.

Listing 3: View to Display Temperature Readings

CREATE VIEW qryFahrenheit
AS 

     SELECT TemperatureID, LocationName, Celsius, 

            
Fahrenheit  = (32 + (Celsius * 
9/5)),
            
DateTaken
          FROM 
tblLocation    
L,
               
tblTemperature T
          WHERE 
L.LocationID = T.LocationID

Now, while you can issue INSERT and UPDATE statements against views, doing so is subject to a complex set of rules and one of them is that you the view cannot contain any derived columns in the select list. Therefore, if I want to add rows to my view, I cannot do so directly because the view contains the calculated Fahrenheit column. In earlier versions of SQL Server, this view would have been nonupdateable. To add rows to it, I would have to write directly into the base tables rather than through the view. This complicates my application because I would have to know the layout of the base tables. Sort of defeats the purpose of a view, doesn't it?

With SQL Server 2000, you can hide this complexity from your developers by creating and INSTEAD OF trigger like Listing 4:

Listing 4: A Sample INSTEAD OF Trigger

CREATE TRIGGER tiiFahrenheit ON qryFahrenheit 

     INSTEAD OF INSERT
AS
BEGIN 
INSERT INTO tblTemperature 

          (LocationID, Celsius, 
DateTaken)
     SELECT LocationID, Celsius, 
DateTaken
          FROM 
tblLocation 
L,
               
INSERTED    
I
          WHERE L.LocationName 
= I.LocationName
END

The first thing you will notice is that we can now create triggers on views. Other than that, it is a normal trigger. We are simply taking the values out of the special INSERTED table and adding them to the base table, tblTemperature. I am able to hide the physical layout of my base tables from the developer. No matter how many tables are included in the view and no matter how complex the relationships, all the developer needs to do is insert a row into the single view. From there, I can distribute the data to any table I like.

To add a row to the view, you would use an INSERT statement like this:

INSERT INTO qryFahrenheit 
     
(TemperatureID, LocationName, Celsius, DateTaken)
     
VALUES 
     (0,  'Pacifica', 23, CAST ('9/26/2000' 
AS DATETIME))
Note: For the time being, assume that "Pacifica" already 
exists in Location table. In the next section, we drop this 
assumption.

The potentially confusing thing about this statement is that you must provide values for every column in your view that does not allow null values. In other words, if the column in the base table has NOT NULL as an attribute, you must provide a value in your INSERT statement. This even applies to IDENTITY columns. Luckily, the query processor makes this easy for you so you don't have to deal with SET IDENTITY_INSERT statements. Rather, you can just pass a placeholder value�0 in my example�and SQL Server will take care of the rest.

Using INSTEAD OF Triggers to Correct Constraint Violations
Now that we have seen how you can use INSTEAD OF to update otherwise non-updateable views, let's see how you could use them to pre-process the results of your statements.

As I mentioned in the earlier section on cascading updates and deletes, one of the main drawbacks of traditional triggers is that you cannot use them to recover from constraint violations, because SQL Server's query processor enforces constraints before firing triggers. Therefore, if your statement violates a constraint, your trigger will never fire. INSTEAD OF triggers, on the other hand, fire before SQL Server checks the constraints. Thus, you can write your INSTEAD OF triggers to anticipate constraint violations and attempt to fix them.

Continuing with our simple temperature database, let's add primary keys as shown in Listing 5 to both tables and then establish a foreign key relationship between tblLocation and tblTemperature.

Listing 5: Adding Primary Keys and a Foreign Key Relationship

ALTER TABLE tblLocation 
     
ADD CONSTRAINT pkLocation
     PRIMARY KEY 
(LocationID)
ALTER TABLE tblTemperature
     ADD 
CONSTRAINT pkTemperature
     PRIMARY KEY 
(TemperatureID) 
ALTER TABLE tblTemperature 
     ADD 
CONSTRAINT fkTemperature_Location 

          FOREIGN KEY 
(LocationID) 
          
REFERENCES tblLocation (LocationID)

Let's use the same INSERT statement again, but this time let's clear out both tables first:

DELETE FROM tblTemperature
DELETE FROM tblLocation
INSERT INTO qryFahrenheit 
     
(TemperatureID, LocationName, Celsius, DateTaken)
     
VALUES 
     (0,  'Pacifica', 23, CAST ('9/26/2000' 
AS DATETIME))

If you execute this batch as is, it will fail to insert any rows because your INSTEAD OF trigger is trying join the LocationName of "Pacifica" in the special INSERTED table to the empty table tblLocation. Obviously, it will not return any rows.

Initially, you may be tempted to give up on inserting data into the view and attempt to write directly into the base Temperature table. This, however, won't work either; if you try, you will receive an error due to the foreign key constraint. To solve this dilemma, you need to alter your INSTEAD OF trigger as shown in Listing 6 to insert any necessary rows into the Location table before attempting to add data to the Fahrenheit view.

Listing 6: The Altered INSTEAD OF Trigger

ALTER TRIGGER tiiFahrenheit ON qryFahrenheit 

     INSTEAD OF INSERT
AS
BEGIN

-- Insert any new locations

INSERT INTO tblLocation 
(LocationName)
     SELECT DISTINCT 
I.LocationName
          FROM 
INSERTED I
          WHERE 
I.LocationName
                
NOT IN (SELECT LocationName FROM tblLocation)

-- Insert temperature readings

INSERT INTO tblTemperature 

          (LocationID, Celsius, 
DateTaken)
     SELECT LocationID, Celsius, 
DateTaken
          FROM 
tblLocation 
L,
                  
INSERTED    
I
          WHERE L.LocationName 
= I.LocationName
END

Now your trigger handles all the logic, allowing your developers to insert into the Fahrenheit view without having to worry about the layout of the underlying tables. The next step would be to create update and delete INSTEAD OF triggers. That is, as they say, an exercise that will be left to the reader.

AFTER Triggers
Before wrapping up, I want to take a quick look at AFTER triggers. After triggers are very easy to understand, because they are simply a replacement of the standard FOR triggers in earlier versions of SQL Server. The syntax used in FOR triggers is still maintained for compatibility.

Unlike INSTEAD OF triggers, you may only create AFTER triggers on tables; you cannot create an AFTER trigger on a view. You would use the following syntax to create a simple AFTER trigger on our tblLocation table to prevent deletions of the Pacifica row.

CREATE TRIGGER tdaLocation ON tblLocation
AFTER DELETE
AS 

BEGIN
     SELECT * FROM DELETED WHERE LocationName = 
'Pacifica'
     IF @@RowCount <> 0 ROLLBACK 
TRANSACTION
END