Using Triggers to Implement Referential Actions | Inside Microsoft SQL Server 7.0 (Mps)

As we discussed in Chapter 6, SQL Server implements FOREIGN KEY (or REFERENCES) constraints. If you attempt a data modification that would break a relationshipfor example, if you attempt to delete the primary key row while foreign key references existthe data modification is disallowed and the command is aborted. In other words, no action is takenin fact, ANSI SQL-92 calls this the NO ACTION referential action. (Colloquially, NO ACTION is often referred to as RESTRICT, but technically this is incorrect. ANSI uses RESTRICT for security and permissions issues.)

NOTE
A FOREIGN KEY constraint can of course be directed toward a UNIQUE constraint, not just a PRIMARY KEY constraint. But there is no performance difference if the referenced table is declared using UNIQUE. In this section, we'll refer only to PRIMARY KEY in the referenced table for simplicity.

Keep in mind that a FOREIGN KEY constraint affects two tables, not just one. It affects the referenced table as well as the referencing table. While the constraint is declared on the referencing table, a modification that affects the primary key of the referenced table checks to see whether the constraint has been violated. A declared foreign key results in checks for the following actions whenever a data modification occurs in either the referencing or referenced table. (None of these actions is allowed by a FOREIGN KEY constraint.)

Inserting a row in a referencing table so that the value of a foreign key does not match a primary key value in the referenced table.
Updating a foreign key value in a row in a referencing table when no matching primary key value is in the referenced table.
Updating a primary key value in a row in a referenced table so that a foreign key in a row in the referencing table no longer has a matching primary key value.
Deleting a row in a referenced table so that a foreign key in a row in the referencing table no longer has a matching primary key value.

In addition to the NO ACTION referential action used in SQL Server 7, the SQL-92 standard specifies three additional referential actions for modifying the primary key side of a relationship: SET NULL, SET DEFAULT, and CASCADE. The four referential actions have the following characteristics:

NO ACTION Disallows the action if the FOREIGN KEY constraint would be violated. This is the only referential action implemented by SQL Server 7 for a declared FOREIGN KEY constraint, and it's the only one that must be implemented for a product to claim to be "SQL-92 conformant."
SET NULL Updates the referencing table so that the foreign key columns are set to NULL. Obviously, this requires that the columns be defined to allow NULL.
SET DEFAULT Updates the referencing table so that the foreign key columns are set to their DEFAULT values. The columns must have DEFAULT values defined.
CASCADE Updates the referencing table so that the foreign key columns are set to the same values that the primary key was changed to, or deletes the referencing rows entirely if the primary key row is deleted.

A declared FOREIGN KEY constraint, rather than a trigger, is generally the best choice for implementing NO ACTION. The constraint is easy to use and eliminates the possibility of a bug in a trigger that you write. You can use triggers to implement any of the other referential actions not currently available with declared FOREIGN KEY constraints.

You can also use a trigger to customize the referential actions in other ways. For example, you can customize error messages to make them more informative than the generic messages sent with a violation of a declared constraint.

Recall that constraint violations are tested before triggers fire. If a constraint violation is detected , the statement is aborted and execution never gets to the trigger (and the trigger never fires). Therefore, you cannot use a declared FOREIGN KEY constraint to ensure that a relationship is never violated by the update of a foreign key and then also use a trigger to perform a cascading action (or other such action) when the primary key side of the same relationship is changed. If you use triggers to implement CASCADE, SET NULL, or SET DEFAULT behavior for changes to the referenced table, you must also use triggers to restrict invalid inserts and updates to the referencing table. You can and should still declare PRIMARY KEY or UNIQUE constraints on the table to be referenced, however. It's relatively easy to write triggers to perform referential actions, as we'll see in the upcoming examples. For the sake of readability, you can still declare the FOREIGN KEY constraint in your CREATE TABLE scripts but then disable the constraint using ALTER TABLE NO CHECK so that it is not enforced. In this way, you ensure that the constraint still appears in the output of sp_help <table> and similar procedures.

NOTE
Although we're about to see triggers that perform referential actions, you might want to consider another option entirely. If you want to define constraints and still have update and delete capability beyond NO ACTION, a good alternative is using stored procedures that exclusively perform the update and delete operations. A stored procedure can easily perform the referential action (within the scope of a transaction) before the constraint would be violated. No violation occurs because the corresponding "fix-up" operation is already performed in the stored procedure. If an INSERT or UPDATE statement is issued directly (and you can prevent that in the first place by not granting such permissions), the FOREIGN KEY constraint still ensures that the relationship is not violated.

Here is an example of using triggers to implement a referential action. Rather than simply implementing NO ACTION, we want to implement ON DELETE CASCADE and ON UPDATE SET DEFAULT for the foreign key relationship between titleauthor and titles . We must create the following three triggers (or suitable alternatives):

 -- 1. INSERT and UPDATE trigger on referencing table. -- Disallow any insert or update if the foreign key title_id  -- here in the referencing table titleauthor does not match -- the primary key title_id in the referenced table titles. CREATE TRIGGER INS_UPD_titleauthor     ON titleauthor     FOR INSERT, UPDATE AS -- Do any rows exist in the inserted table that do not have  -- a matching ID in titles? IF EXISTS     (SELECT * FROM inserted WHERE inserted.title_id NOT IN         (SELECT titles.title_id FROM titles))     BEGIN     RAISERROR('No matching title found. Statement will be aborted.', 16, 1)     ROLLBACK TRAN     END GO -- 2. If primary key in referenced table titles is changed, -- update any rows in titleauthor to the DEFAULT value. -- This implements ON UPDATE SET DEFAULT. Notice this trigger could -- be easily changed to set the column to NULL instead of DEFAULT, -- which would implement ON UPDATE SET NULL. CREATE TRIGGER UPD_titles     ON titles      FOR UPDATE  AS     DECLARE @counter int     IF UPDATE(title_id)         BEGIN         UPDATE titleauthor         SET titleauthor.title_id=DEFAULT         FROM titleauthor, deleted         WHERE titleauthor.title_id=deleted.title_id              SET @COUNTER=@@ROWCOUNT         -- If the trigger resulted in modifying rows of         -- titleauthor, raise an informational message         IF (@counter > 0)             RAISERROR('%d rows of titleauthor were updated to DEFAULT title_id as a result of an update to titles table', 10, 1, @counter)         END GO -- 3. DELETE of referenced table titles will CASCADE to referencing -- table titleauthor and delete any rows that referenced the row  -- deleted CREATE TRIGGER DelCascadeTrig     ON titles     FOR DELETE AS     DECLARE @counter int     DELETE titleauthor     FROM titleauthor, deleted     WHERE titleauthor.title_id=deleted.title_id     SET @counter=@@ROWCOUNT          IF (@counter > 0)         RAISERROR('%d rows of titleauthor were deleted as a result of a delete to the titles table', 10, 1, @counter) GO

NOTE
For these examples to run, you must drop or suspend the existing FOREIGN KEY constraints on titleauthor that reference titles as well as FOREIGN KEY constraints on two other tables ( sales and roysched ) that reference titles . The code on the companion CD does this.

The following is a cascading update trigger on the titles (referenced) table that updates all rows in the titleauthor table with matching foreign key values. The cascading update is tricky. It requires that you associate both the before and after values (from the inserted and deleted pseudotables) to the referencing table. In a cascading update, you by definition change that primary key or unique value. This means that typically the cascading update works only if one row of the referenced table is updated. If you change the values of multiple primary keys in the same update statement, you lose the ability to correctly associate the referencing table. You can easily restrict an update of a primary key to not affect more than one row by checking the value of the @@ROWCOUNT function, as follows :

 CREATE TRIGGER UpdCascadeTrig1     ON titles     FOR UPDATE AS     DECLARE @num_affected int, @title_id varchar(11),         @old_title_id varchar(11)     SET @num_affected=@@ROWCOUNT     IF (@num_affected=0)    -- No rows affected, so nothing to do         RETURN     IF UPDATE(title_id)         BEGIN         IF (@num_affected=1)             BEGIN             SELECT @title_id=title_id FROM inserted             SELECT @old_title_id=title_id FROM deleted             UPDATE titleauthor             SET title_id=@title_id             FROM titleauthor             WHERE titleauthor.title_id=@old_title_id             SELECT @num_affected=@@ROWCOUNT             RAISERROR ('Cascaded update in titles of Primary Key                 from %s to %s to %d rows in titleauthor', 10, 1,                 @old_title_id, @title_id, @num_affected)             END         ELSE             BEGIN             RAISERROR ('Cannot update multiple Primary Key values                 in a single statement due to Cascading Update                 trigger in existence.', 16, 1)             ROLLBACK TRANSACTION             END         END

The inability to update more than one primary key value at a time is not a limitationif anything, it might save you from making a mistake. You typically won't want to make mass updates to a primary key. If you declare a PRIMARY KEY or UNIQUE constraint (as you should), mass updates usually won't work because of the constraint's need for uniqueness. Although such an update is rare, you might want to set the value to some expression, such as multiplying all Customer ID values by 1000 to renumber them. For such unusual cases, you can easily perform the UPDATE in a loop, one row at a time, and use a cascading trigger.

Another situation in which you might want to update multiple primary keys in the same statement is if you have a mail-order customer list that uses the customer phone number as the primary key. As phone companies add numbers to accommodate fax machines, e-mail, and cellular phones, some area codes run out of possible numbers , which leads to the creation of new area codes. If a mail-order company needs to keep up with such changes, it must update the primary key for all customers getting new area codes. If you assume that the phone number is stored as a character string of 10 digits, you must update the first three characters only. If the new area code is 425, for example, your update statement would look something like this:

 UPDATE customer_list     SET phone = '425' +  substring(phone, 4, 10)     WHERE zip IN (list of affected zip codes)

This UPDATE would affect the primary key of multiple rows, and it would be difficult to write a trigger to implement a cascade operation (perhaps into an orders table).

You might think that you can write a trigger to handle multiple updates to the primary key by using two cursors to step through the inserted and deleted tables at the same time. However, this won't work. There's no guarantee that the first row in the deleted table corresponds to the first row in the inserted table, so you cannot determine which old foreign key values in the referencing tables should be updated to which new values.

Although it is logically the correct thing to do, you are not required to create a PRIMARY KEY or UNIQUE constraint on a referenced table. (Unless, of course, if you are going to actually declare a FOREIGN KEY constraint to reference a column in the referenced table.) And there is no hard-and-fast requirement that the constraint be unique. (However, not making it unique violates the logical relationship.) In fact, the "one row only" restriction in the previous cascading trigger is a tad more restrictive than necessary. If uniqueness were not required on title_id , the restriction could be eased a bit to allow the update to affect more than one row in the titles table as long as all affected rows were updated to the same value for title_id . When uniqueness on titles.title_id is required (as should usually be the case), the two restrictions are actually redundant. Since all affected rows are updated to the same value for title_id , you no longer have to worry about associating the new value in the referencing table, titleauthor . There is only one value. The trigger looks like this:

 CREATE TRIGGER UpdCascadeTrig2     ON titles     FOR UPDATE AS     DECLARE @num_distinct int, @num_affected int,         @title_id varchar(11)     SET @num_affected=@@ROWCOUNT     IF (@num_affected=0)    -- No rows affected, so nothing to do         RETURN     IF UPDATE(title_id)         BEGIN         SELECT @num_distinct=COUNT(DISTINCT title_id) FROM inserted         IF (@num_distinct=1)             BEGIN             -- Temporarily make it return just one row              SET ROWCOUNT 1              SELECT @title_id=title_id FROM inserted             SET ROWCOUNT 0     -- Revert ROWCOUNT back             UPDATE titleauthor             SET titleauthor.title_id=@title_id             FROM titleauthor, deleted             WHERE titleauthor.title_id=deleted.title_id             SELECT @num_affected=@@ROWCOUNT             RAISERROR ('Cascaded update of Primary Key to value in                 titles to %d rows in titleauthor', 10, 1,                 @title_id, @num_affected)             END         ELSE             BEGIN             RAISERROR ('Cannot cascade a multirow update that                 changes title_id to multiple different values.', 16, 1)             ROLLBACK TRANSACTION             END         END

Using COUNT(DISTINCT title_id) FROM inserted ensures that even if multiple rows are affected, they are all set to the same value. So @title_id can pick up the value of title_id for any row in the inserted table. You use the SET ROWCOUNT 1 statement to limit the subsequent SELECT to only the first row. This is not required, but it's good practice and a small optimization. If you don't use SET ROWCOUNT 1 , the assignment still works correctly but every inserted row is selected and you end up with the value of the last row (which, of course, is the same value as any other row). Note that you can't use the TOP clause in a SELECT statement that assigns to a variable. It's not good practice to do a SELECT or a variable assignment that assumes that just one row is returned unless you're sure that only one row can be returned. You can also ensure a single returned row by doing the assignment like this:

 SELECT @title_id=title_id FROM titles GROUP BY title_id HAVING      COUNT(DISTINCT title_id)=1

Because the previous IF allows only one distinct title_id value, you are assured that the preceding SELECT statement returns only one row.

In the previous two triggers, you first determine whether any rows were updated; if not, you do a RETURN. This illustrates something that might not be obvious: a trigger fires even if no rows were affected by the update. Recall that the plan for the trigger is appended to the rest of the statements' execution plan. You don't know the number of rows affected until execution. This is a feature, not a bug; it allows you to take action when you expect some rows to be affected but none are. Having no rows affected is not an error in this case. However, you can use a trigger to return an error when this occurs. The previous two triggers use RAISERROR to provide either a message that indicates the number of rows cascaded or an error message. If no rows are affected, we don't want any messages to be raised, so we return from the trigger immediately.

Recursive Triggers

SQL Server 7 allows triggers to recursively call themselves if the database option recursive triggers is set to TRUE. This means that if a trigger modifies the table on which the trigger is based, the trigger might fire a second time. But then, of course, the trigger modifies the table and fires the trigger again, which modifies the table and fires the trigger againand so on. This process does not result in an infinite loop because SQL Server has a maximum nesting depth of 32 levels; after the 32nd call to the trigger, SQL Server generates the error shown next . The batch stops executing, and all data modifications since the original one that caused the trigger to be fired are rolled back.

 Server: Msg 217, Level 16, State 1, Procedure up_rt1, Line 4 Maximum stored procedure nesting level exceeded (limit 32).

Writing recursive routines is not for the faint of heart. You must be able to determine what further actions to take based on what has already happened , and you must make sure that the recursion stops before the nesting reaches its limit. Let's look at a simple example. Suppose we have a table that keeps track of budgets for various departments. Departments are members (or children) of other departments, so the budget for a parent department includes the budgets for all its child departments. If a child department's budget increases or decreases, the change must be propagated to the parent department, the parent's parent, and so forth. The following code creates a small budget table and inserts three rows into it.

 CREATE TABLE BUDGET     (dept_name      varchar(30)    not null,      parent_name    varchar(30)    null,      budget_amt     money          not null) GO INSERT INTO budget values ('Internal Training', 'Training',)  INSERT INTO budget values ('Training', 'Services', 0)  INSERT INTO budget values ('Services', NULL, 0)  GO

The following trigger is fired if a single row in the budget table is updated. If the department is not the highest-level department, its parent name is not NULL, so you can adjust the budget for the parent department by the same amount that the current row was just updated. (The amount of the change is equal to the new amount in the inserted table minus the old amount in the deleted table.) That new update causes the trigger to fire again. If the next department has a non-null parent, the update is applied again. The recursion stops when you get to the highest level and the parent department is null. In that case, the trigger simply returns and all work is done.

 CREATE TRIGGER update_budget ON budget FOR update AS IF (@@rowcount > 0 BEGIN     PRINT 'Only one row can be updated at a time'     ROLLBACK TRAN     RETURN END IF (SELECT parent_name FROM inserted) IS NULL RETURN  UPDATE budget      SET budget_amt = budget_amt + (SELECT budget_amt FROM inserted) -                                    (SELECT budget_amt FROM deleted) WHERE dept_name = (SELECT parent_name FROM inserted)