Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize
24 out of 27 rated this helpful - Rate this topic

Implementing Referential Integrity and Cascading Actions

SQL Server 2000
 

Itzik Ben-Gan and Thomas Moreau

October 2000

Summary: This article is a chapter excerpt from the book Advanced Transact-SQL for SQL Server 2000 from APress Books. This chapter covers the use of declarative referential integrity as well as the use of triggers to maintain data integrity. (42 printed pages)

Contents

Introduction
The Relationship Scenario
Referential Integrity Enforcement Methods
Implementing Cascading Operations Using a FOREIGN KEY
Implementing Cascading Operations Using Stored Procedures
Implementing Cascading Operations Using Triggers
Conclusion

Introduction

Data is usually spread across several tables, which are related to each other through key columns. Database normalization is used to avoid duplicates in a database, thus avoiding potential errors. Although it's true that database normalization minimizes the chance for errors, it doesn't eliminate them. There is still the need for a set of data-integrity rules that will enforce the relationships between tables and keep the database as consistent as possible. This set of rules is called referential integrity, and it is a part of the wider set of rules that enforce data integrity in general.

This article explores the rules used to maintain referential integrity in various table relationships, with a focus on cascading actions. Cascading actions are actions that must be performed on a secondary table to compensate when a primary table is modified.

The Relationship Scenarios

You might face different situations, each of which requires a different approach and different rules that need to be implemented. This article focuses on cascading modifications from one table to related tables. It discusses two relationship scenarios:

  • The relationship between a primary and a secondary table. An example is a one-to-many relationship between a primary table and a secondary table, such as an Orders table and an OrderDetails table, where each order in the Orders table has one or more order parts in the OrderDetails table. Another example is a one-to-one relationship between a primary table and a secondary subtype table, such as a Customers table and a ForeignCustomers table, where each customer in the Customers table can have no more than one row in the ForeignCustomers table. There is no need to discuss other variations of these relationships because they are treated the same way in terms of cascading. Therefore, only the Orders:OrderDetails scenario will be used in the examples in this article.
  • The relationships within one table. An example of such a scenario is an Employees table, where each employee reports to a manager, who is also an employee.

The Orders and OrderDetails Tables

The following script shows how to create the Orders and OrderDetails tables. They will be used to illustrate the rules necessary to maintain referential integrity between primary and secondary tables.

Schema creation script for the Orders and OrderDetails tables

CREATE TABLE Orders(
OrderID    int NOT  NULL,
CustomerID char(5)  NOT NULL,
OrderDate  datetime NOT NULL,
CONSTRAINT PK_Orders_OrderID PRIMARY KEY(Orderid))

CREATE TABLE OrderDetails(
OrderID  int NOT NULL,
PartID   int NOT NULL,
Quantity int NOT NULL,
CONSTRAINT PK_OrderDetails_OrderID_partid PRIMARY KEY(OrderID, PartID))

INSERT INTO Orders VALUES(10001, 'FRODO', '19990417')
INSERT INTO Orders VALUES(10002, 'GNDLF', '19990418')
INSERT INTO Orders VALUES(10003, 'BILBO', '19990419')

INSERT INTO OrderDetails VALUES(10001, 11, 12)
INSERT INTO OrderDetails VALUES(10001, 42, 10)
INSERT INTO OrderDetails VALUES(10001, 72, 5)
INSERT INTO OrderDetails VALUES(10002, 14, 9)
INSERT INTO OrderDetails VALUES(10002, 51, 40)
INSERT INTO OrderDetails VALUES(10003, 41, 10)
INSERT INTO OrderDetails VALUES(10003, 61, 35)
INSERT INTO OrderDetails VALUES(10003, 65, 15)

The following tables show the contents of the Orders table and the OrderDetails table.

Content of the Orders table

orderidcustomeridorderdate
10001FRODO1999-04-17 00:00:00.000
10002GNDLF1999-04-18 00:00:00.000
10003BILBO1999-04-19 00:00:00.000

Content of the OrderDetails table

orderidpartidquantity
100011112
100014210
10001725
10002149
100025140
100034110
100036135
100036515

Figure 1 shows the schema of the Orders and OrderDetails tables.

Aa902684.sql_refintegrity01(en-us,SQL.80).gif

Figure 1. Schema of the Orders and OrderDetails tables

The Employees Table

The following script shows how to create the Employees table. It will be used to illustrate the rules necessary to maintain referential integrity within a single table.

Schema creation script for the Employees table

CREATE TABLE Employees
(empid int NOT NULL,
 mgrid int NULL,
 empname varchar(25) NOT NULL,
 salary money NOT NULL,
 CONSTRAINT PK_Employees_empid PRIMARY KEY(empid))

INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES( 1, NULL, 'Nancy',  $10000.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES( 2,    1, 'Andrew',  $5000.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES( 3,    1, 'Janet',   $5000.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES( 4,    1, 'Margaret',$5000.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES( 5,    2, 'Steven',  $2500.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES( 6,    2, 'Michael', $2500.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES( 7,    3, 'Robert',  $2500.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES( 8,    3, 'Laura',   $2500.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES( 9,    3, 'Ann',     $2500.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES(10,    4, 'Ina',     $2500.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES(11,    7, 'David',   $2000.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES(12,    7, 'Ron',     $2000.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES(13,    7, 'Dan',     $2000.00)
INSERT INTO employees(empid, mgrid, empname, salary) 
  VALUES(14,   11, 'James',   $1500.00)

The following table shows the content of the Employees table.

Content of the Employees Table

empidmgridempnamesalary
1NULLNancy10000.0000
21Andrew5000.0000
31Janet5000.0000
41Margaret5000.0000
52Steven2500.0000
62Michael2500.0000
73Robert2500.0000
83Laura2500.0000
93Ann2500.0000
104Ina2500.0000
117David2000.0000
127Ron2000.0000
137Dan2000.0000
1411James1500.0000

Figure 2 shows the schema of the Employees table.

Aa902684.sql_refintegrity02(en-us,SQL.80).gif

Figure 2. Schema of the Employees table

Referential Integrity Enforcement Methods

There are several mechanisms for enforcing referential integrity in general and cascading operations, which is one of the referential integrity actions, specifically. They are divided into two main groups:

  • Declarative Referential Integrity (DRI), where rules are declared as part of the table's schema. The main mechanism used to enforce DRI is a FOREIGN KEY constraint.
  • Procedural Referential Integrity, where rules are checked in a procedural code. There are several mechanisms that implement procedural referential integrity—code in the client application, stored procedures, and triggers.

Implementing Cascading Operations Using a FOREIGN KEY

The ANSI SQL-92 standard specifies four referential integrity actions that define the activity that should occur when the tables involved in the relationship are modified: NO ACTION, CASCADE, SET DEFAULT, and SET NULL. In a relationship between two tables, the table that contains the FOREIGN KEY—also known as the referencing table—is the secondary or subtype table.

In the first OrderDetails table example, the FOREIGN KEY is placed on the related column in the referencing table, and it references the primary table's (also known as the referenced table's) related column, on which a PRIMARY KEY or UNIQUE constraint must be defined. In this example, it is placed on the orderid column in the OrderDetails table, and it references the orderid column in the Orders table.

In the second example—the Employees table—the FOREIGN KEY is placed on the mgrid column, and it references the empid column.

The following sections explore the four referential integrity actions in more detail, using the sample tables to illustrate the way they are implemented in Microsoft® SQL Server™.

NO ACTION (restrict)

This type of action was the only one supported by the FOREIGN KEY constraint up until, and including, SQL Server version 7.0. It always enforces all of the following integrity rules:

  • You can't delete a row from the primary table if it has related rows in the secondary table. In the Orders and OrderDetails scenario, you can't delete an order that has order details. In the Employees scenario, you can't delete a manager who is in charge of employees.
  • You can't update the primary table's primary key if the row being modified has related rows in the secondary table. In the Orders and OrderDetails scenario, you can't update an order ID if that order has order parts. In the Employees scenario, you can't update a manager ID if that manager is in charge of employees.
  • You can't insert a row into the secondary table if there is no related row in the primary table. For example, you can't insert an order detail for an order that doesn't exist. Also, you can't insert an employee if the entry for the employee's manager doesn't exist.
  • You can't update the secondary table's FOREIGN KEY column if it doesn't have a related row in the primary table. For example, you can't shift an order detail to an order that doesn't exist, and you can't assign an employee to a manager if an entry for the manager doesn't exist.

You can create a FOREIGN KEY as part of the CREATE TABLE statement, or you can add it later by using the ALTER TABLE statement. You'll use the latter method in the examples.

To add a FOREIGN KEY to OrderDetails, run the following script.

Adding a FOREIGN KEY to the OrderDetails table

ALTER TABLE OrderDetails ADD CONSTRAINT FK_OrderDetails_Orders
  FOREIGN KEY(orderid)
  REFERENCES Orders(orderid)

To add a FOREIGN KEY to the Employees table, run the following script.

Adding a FOREIGN KEY to the Employees table

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_Employees
  FOREIGN KEY(mgrid)
  REFERENCES Employees(empid)

Microsoft SQL Server 2000 enables you to specify NO ACTION explicitly with the FOREIGN KEY constraint, because it supports other actions as well, as opposed to a FOREIGN KEY constraint in previous versions. NO ACTION is the default if you don't specify an action. Thus, you could rewrite the previous OrderDetails code as follows.

Add a FOREIGN KEY with NO ACTION to the OrderDetails table

ALTER TABLE OrderDetails ADD CONSTRAINT FK_OrderDetails_Orders
  FOREIGN KEY(orderid)
  REFERENCES Orders(orderid)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
Note   If you are running the code samples and want to create a new FOREIGN KEY instead of an existing one, you have to drop the existing one first by including the following code in your script:
ALTER TABLE OrderDetails DROP CONSTRAINT FK_OrderDetails_Orders

You should be aware that using a FOREIGN KEY, even to enforce NO ACTION, incurs a performance penalty. If you review the four integrity rules a FOREIGN KEY enforces, it is clear that for each of them, when you modify one table, SQL Server has to access the related table to see if the rule is going to be broken. On the other hand, those rules are checked before the modification occurs—so if any of the rules are broken, nothing is written to the transaction log, and the operation is canceled. Some implementations prefer to enforce these rules in the client application instead. Just keep in mind that one application might enforce the rules, but another might not, compromising the integrity of your database.

CASCADE

The ANSI SQL-92 standard supports both the DELETE CASCADE and the UPDATE CASCADE actions in the REFERENCES clause of the FOREIGN KEY constraint.

ON DELETE CASCADE means that when a row in the primary table is deleted, you want all the related rows in the secondary table, which has a FOREIGN KEY pointing to the primary table, to be automatically deleted. In the Orders and OrderDetails example, if you delete the order with order ID 10002 from the Orders table, the two order details in the OrderDetails table belonging to that order will be automatically deleted.

ON UPDATE CASCADE means that if you update a column in the primary table, which is pointed at by the FOREIGN KEY in the secondary table, the FOREIGN KEY column in all of the related rows will also be updated with the same values. In the Orders and OrderDetails example, if you change the value of orderid from 10002 to 10004 in the Orders table, the value of the orderid column of the two order parts belonging to that order in the OrderDetails table will automatically be updated to 10004 as well.

Note   Declarative cascading actions were not supported prior to SQL Server 2000, and they have been on the wish lists of many programmers and DBAs. At last, they are supported as of SQL Server 2000.

To try out these new concepts, you can run some tests on both examples. First, create a FOREIGN KEY that supports both cascade actions (don't forget to drop the existing foreign key first, if you created one), as shown in the following script.

Adding a FOREIGN KEY with CASCADE to the OrderDetails table

ALTER TABLE OrderDetails ADD CONSTRAINT FK_OrderDetails_Orders
  FOREIGN KEY(orderid)
  REFERENCES Orders(orderid)
  ON DELETE CASCADE
  ON UPDATE CASCADE

Each of the following examples expects the tables to contain the same data as initially loaded. Instead of reloading the data each time, you can encapsulate the modification in a transaction and roll it back after you check the result, so the changes will not be committed to the database. You can use the pattern shown in the following script, and just incorporate your DELETE/UPDATE statement instead of the one in this example.

Template for modifying data without committing the changes

BEGIN TRAN
  SELECT * FROM Orders
  SELECT * FROM OrderDetails
  DELETE... / UPDATE...
  SELECT * FROM Orders
  SELECT * FROM OrderDetails
ROLLBACK TRAN

Next, modify the Orders table and check the results. First, delete the order with the order ID 10002, as shown in the following script.

Testing ON DELETE CASCADE

DELETE FROM Orders
WHERE orderid = 10002

The result of this DELETE statement is shown in the following tables.

Testing ON DELETE CASCADE, Orders table

orderidcustomeridorderdate
10001FRODO1999-04-17 00:00:00.000
10003BILBO1999-04-19 00:00:00.000

Testing ON DELETE CASCADE, OrderDetails table

orderidpartidquantity
100011112
100014210
10001725
100034110
100036135
100036515

You can see that the related order parts in the OrderDetails table were automatically deleted. Examine the execution plan of this DELETE, shown in Figure 3.

Click here for larger image

Figure 3. Execution plan for ON DELETE CASCADE (click image to see larger picture)

The steps in this execution plan are as follows:

  • Delete the row with order ID 10002 from the Orders table (clustered index delete).
  • Store the deleted order ID 10002 in a temporary table.
  • Read the temporary table created in Step 2.
  • Perform a clustered index seek in the OrderDetails table to find matching order parts.
  • Join the temporary table from Step 3 to the OrderDetails rows found in Step 4 to find the order details that need to be deleted (using a nested-loops join algorithm).
  • Store the order details' keys in a temporary table.
  • DELETE order details from the OrderDetails table based on the keys stored in the temporary table from Step 5 (clustered index delete).
  • Perform the modifications in sequence (top to bottom).

Now you can issue an UPDATE against the Orders table that changes order ID 10002 to 10004, as shown in the following script.

Testing ON UPDATE CASCADE

UPDATE Orders
  SET orderid = 10004
WHERE orderid = 10002

The result of this UPDATE statement is shown in the following tables.

Testing ON UPDATE CASCADE, Orders table

orderidcustomeridorderdate
10001FRODO1999-04-17 00:00:00.000
10003BILBO1999-04-19 00:00:00.000
10004GNDLF1999-04-18 00:00:00.000

Testing ON UPDATE CASCADE, OrderDetails table

orderidpartidquantity
100011112
100014210
10001725
100034110
100036135
100036515
10004149
100045140

Notice that the orderid column of the related order details in the OrderDetails table was updated correctly. Cascade actions also support multirow modifications. Consider the UPDATE statement shown in the following script.

Testing ON UPDATE CASCADE with a multirow update

UPDATE Orders
  SET orderid = orderid + 1

The result of this UPDATE statement is shown in the following tables.

Testing ON UPDATE CASCADE with a multirow update, Orders table

orderidcustomeridorderdate
10002FRODO1999-04-17 00:00:00.000
10003GNDLF1999-04-18 00:00:00.000
10004BILBO1999-04-19 00:00:00.000

Testing ON UPDATE CASCADE with a multirow update, OrderDetails table

orderidpartidquantity
100021112
100024210
10002725
10003149
100035140
100044110
100046135
100046515

This might seem trivial at first glance, but if you look at the other alternatives to implementing cascading operations, which are mainly stored procedures and triggers (both of which will be explained later in this article), you'll see that an UPDATE like this cannot be cascaded with any other mechanism.

Unfortunately, you cannot implement cascading actions in the Employees table the same way. If you try to create a foreign key on the Employees table that supports cascade actions, you will get the error shown in the following message.

Cyclic or Multiple Cascade Paths error when trying to add a foreign key

Server: Msg 1785, Level 16, State 1, Line 1
Introducing Foreign key Constraint 'FK_Employees_Employees' on table 
   'Employees' may cause cycles or multiple cascade paths.  Try using 
      instead option 'On Delete (Update) No Action' or modifying 
         other Foreign key constraints
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint.  See previous errors.

As you can see, SQL Server noticed that your cascade operation is cyclic, and it does not allow this type of cascading. This is true not only for a self-referencing table, but also for any chain of relationships between tables in which the cascading operations have a potential to be cyclical. In order to enforce cascading actions in relationships that are potentially cyclic, you'll need to revert to enforcement mechanisms that are used in releases earlier than SQL Server 2000, such as triggers. Such mechanisms are covered later in this article.

If you go over the four rules that are enforced by NO ACTION, you should keep in mind that when you use ON DELETE CASCADE, it compensates for a DELETE in the primary table by deleting the related rows in the secondary table instead of enforcing Rule 1 (which would prevent the DELETE). Similarly, ON UPDATE CASCADE compensates for an UPDATE to the primary table by updating the related rows in the secondary table instead of enforcing Rule 2 (which would prevent the UPDATE). If either of the cascade actions is not used, the related rule will be enforced. Notice that cascade actions compensate only for modifications to the primary table. Rules 3 and 4 concern illegal modifications to the secondary table that result in a row that has no related row in the primary table. These two rules are always enforced by the constraint, even when you define both cascade actions.

When you use declarative constraints, you don't need to worry about Rules 3 and 4 because they are taken care of automatically. However, you should keep them in mind because you will need to be aware of them when you use other mechanisms to enforce cascade actions.

SET NULL and SET DEFAULT

The referential actions SET NULL and SET DEFAULT compensate for modifications to the primary table by setting the related columns in the child rows in the secondary table to NULL or to their default value. These actions are not supported as declarative referential constraints in SQL Server 2000, but you can implement them with triggers, which will be discussed later in this article, in the Implementing Cascading Operations Using Triggers section.

Implementing Cascading Operations Using Stored Procedures

Using stored procedures to implement cascading actions does not provide a self-maintained solution. It does, however, allow you to keep your FOREIGN KEY enabled, even in SQL Server 7.0, where this is not possible when implementing a trigger-based solution.

To use stored procedures this way, you create a stored procedure that substitutes each type of modification for both the referencing and referenced tables, making sure that the users will modify the data only through the stored procedures so that the cascade actions will take place. You can accomplish this by granting the users permissions to execute the stored procedures, but not allowing them to directly update the underlying tables. Another "gotcha" with this solution is that the stored procedures take care of single-row modifications on the referenced table.

The following examples are run on the Orders and OrderDetails scenario. Before you continue, make sure you have a FOREIGN KEY with no cascade actions enabled, as shown in the following script.

Re-creating the FOREIGN KEY with NO ACTION (implicitly)

ALTER TABLE Employees DROP CONSTRAINT FK_Employees_Employees
GO

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_Employees
  FOREIGN KEY(mgrid)
  REFERENCES Employees(empid)
GO

You'll implement a stored procedure for each type of modification on both the Orders and OrderDetails tables.

Deleting a Row in the Primary Table

Taking care of cascade deletes in the Orders table is quite simple. You first delete matching rows from the OrderDetails table, and then delete the row from the Orders table. This way you don't break the FOREIGN KEY. The creation script for the usp_OrdersDelete stored procedure is shown in the following script.

Creation Script for the usp_OrdersDelete Stored Procedure

CREATE PROC dbo.usp_OrdersDelete
  @orderid int
AS

BEGIN TRAN

-- delete matching rows from OrderDetails
DELETE FROM OrderDetails
WHERE orderid = @orderid

-- delete row from Orders
DELETE FROM Orders
WHERE orderid = @orderid

COMMIT TRAN
  GO

Updating a Row in the Primary Table

Updating a row in the Orders table is a bit more complex than deleting a row. First, you need to cascade the UPDATE only if the orderid column is modified. You also need to check whether it is modified to a new value; otherwise, it is the same as not modifying it.

If the orderid value doesn't change, you can perform a regular update. If it changes, the FOREIGN KEY does not allow a regular update. To handle this situation, you can break the UPDATE up into a DELETE operation followed by an INSERT operation. After all, you can think of an UPDATE operation as deleting the old value and inserting the new one. First you insert a row with the new order ID and update the matching rows in the OrderDetails table. Then you can delete the row with the old order ID.

The creation script for the usp_OrdersUpdate stored procedure is shown in the following script.

Creation script for the usp_OrdersUpdate stored procedure

  CREATE PROC dbo.usp_OrdersUpdate
  @orderid    int,
  @neworderid int      = NULL,
  @customerid char(5)  = NULL,
  @orderdate  datetime = NULL
AS

-- perform cascade update only if the orderid column is modified
--   and also, it is different from the existing order id
-- split the update to insert and then delete so the foreign key 
   will not be broken
IF @neworderid IS NOT NULL AND @orderid <> @neworderid
BEGIN
  BEGIN TRAN

  -- insert a row with the new order id to Orders
  INSERT INTO Orders(orderid, customerid, orderdate)
    SELECT
      @neworderid,
      ISNULL(@customerid, customerid),
      ISNULL(@orderdate, orderdate)
    FROM
      Orders
    WHERE
      orderid = @orderid

  -- update the orderid column for the matching rows in OrderDetails
  UPDATE OrderDetails
    SET orderid = @neworderid
  WHERE orderid = @orderid

  -- delete the row with the old order id from Orders
  DELETE FROM Orders
  WHERE orderid = @orderid

  COMMIT TRAN
END
-- if the orderid column was not modified, perform a regular update
ELSE
  UPDATE Orders
    SET customerid = ISNULL(@customerid, customerid),
        orderdate  = ISNULL(@orderdate, orderdate)
  WHERE
      orderid = @orderid
  GO

There are a few issues that this stored procedure deals with. In order to allow the update of only some of the columns in the row, you need to have a signal indicating that certain columns should keep their current values. Because all of the columns in the Orders table do not allow NULLs, this is easy—you can safely use NULLs as your signal. This, however, is not so simple with columns that allow NULLs. If you want to allow a column with a NULL value to be updated, you need another signal indicating that the column should keep its current value. Adding a parameter for each column that allows NULLs can solve this—it can be used as a flag that indicates whether it is a NULL that should be placed in the column, or whether the current column value should be kept.

Also note that this solution is only possible if none of the columns other than the PRIMARY KEY is enforced with a UNIQUE constraint or a UNIQUE index. If there is such a column, your INSERT will fail on a duplicate key. In such a case, you might decide to drop the FOREIGN KEY and handle the UPDATE with an UPDATE statement instead of breaking it into an INSERT followed by a DELETE. Another option that does not require you to drop the FOREIGN KEY is to move all the affected secondary table rows to a temporary table and UPDATE them there. This way you can perform the UPDATE as a DELETE followed by an INSERT and then copy the rows back to the OrderDetails table, and you won't violate the FOREIGN KEY.

Inserting a Row into the Primary Table

Inserting a row into the Orders table is pretty straightforward. The creation script for the usp_OrdersInsert stored procedure is shown as follows.

Creation script for the usp_OrdersInsert stored procedure

  CREATE PROC dbo.usp_OrdersInsert
  @orderid    int,
  @customerid char(5),
  @orderdate  datetime
AS

INSERT INTO Orders(orderid, customerid, orderdate)
  VALUES(@orderid, @customerid, @orderdate)
  GO

Here you don't have the NULLs problem discussed earlier because NULLs are not used as a signal in this script. However, you do have a problem if some of the columns in the table have default values and you want to allow the user to use the default for those columns by not specifying a value. If the default values are constants, this could be solved easily by making the default values of the columns also the default values of the parameters in the stored procedure. For example, suppose the customerid column had the default value "ZZZZZ", you could slightly modify the stored procedure as follows.

Creation script for the usp_OrdersInsert stored procedure, with defaults included

  CREATE PROC dbo.usp_OrdersInsert
  @orderid    int,
  @customerid char(5) = 'ZZZZZ',
  @orderdate  datetime
AS

INSERT INTO Orders(orderid, customerid, orderdate)
  VALUES(@orderid, @customerid, @orderdate)
  GO

If the default values for the columns had been expressions, such as a system function like GETDATE(), this modification wouldn't have been so simple, because a default value for a stored procedure's parameter can only be a constant. In such a situation, you need to add parameters to indicate that a default value is desired for a column and then issue the INSERT using the DEFAULT keyword instead of using a specific value for the column.

Inserting, Updating, and Deleting a Row in the Secondary Table

Inserting, updating, and deleting a row in the OrderDetails table are all straightforward operations, and they have the same issues regarding NULLs and default values as discussed earlier. The following contains the creation script for the usp_OrderDetailsInsert, usp_OrderDetailsUpdate, and usp_OrderDetailsDelete stored procedures.

Creation script for the usp_OrderDetailsInsert, usp_OrderDetailsUpdate, and usp_OrderDetailsDelete stored procedures

  CREATE PROC dbo.usp_OrderDetailsInsert
  @orderid  int,
  @partid   int,
  @quantity int
AS

INSERT INTO OrderDetails(orderid, partid, quantity)
  VALUES(@orderid, @partid, @quantity)
GO

CREATE PROC dbo.usp_OrderDetailsUpdate
  @orderid    int,
  @partid     int,
  @neworderid int = NULL,
  @newpartid  int = NULL,
  @quantity   int = NULL
AS

UPDATE OrderDetails
  SET orderid = ISNULL(@neworderid, orderid),
      partid = ISNULL(@newpartid, partid),
      quantity = ISNULL(@quantity, quantity)
WHERE
    orderid = @orderid
  AND
    partid = @partid
GO

CREATE PROC dbo.usp_OrderDetailsDelete
  @orderid int,
  @partid  int
AS

DELETE FROM OrderDetails
WHERE
    orderid = @orderid
  AND
    partid = @partid
  GO

Note that you didn't need to deal with illegal modifications that result in orphaned order details because the FOREIGN KEY takes care of that.

Encapsulating the Logic

Instead of manually creating a set of stored procedures for each table, you can encapsulate the logic in a stored procedure that creates the relevant CREATE PROCEDURE statements and executes them dynamically.

To get an idea of how this can be implemented, visit Dejan Sarka's SQL User's Group Web site and select English from the pull-down menu at the bottom of the left pane, Old Presentations from the Meetings list in the middle of the left pane, and a sample script from Procedures that create procedures in the center pane.

Implementing Cascading Operations Using Triggers

You can use triggers to enforce referential integrity and cascading actions when you need a self-maintained solution—you don't need to modify the data through special stored procedures. You can use the same INSERT, UPDATE, and DELETE statements to modify the base tables as you would normally use, and they will cause the triggers to fire and enforce referential integrity and cascade your modifications.

Prior to SQL Server 2000, triggers were the only self-maintained solution you could implement to enforce referential integrity with cascading actions. SQL Server 2000 introduced new features to Transact-SQL that enable you to approach cascading actions in a variety of new ways, some of which were discussed earlier in this article. Others will be discussed in the following sections. Because cascading actions are approached differently in SQL Server 2000 as compared to previous versions, this paper will discuss the two approaches separately.

Using Triggers Prior to SQL Server 2000

The first thing that you need to keep in mind when you want to provide a solution with AFTER triggers in all versions of SQL Server is that they fire after the modification has occurred. This means that you have to drop all existing FOREIGN KEY constraints in order to allow the trigger's code to run after a modification takes place. Otherwise, the constraint will prevent the modification from taking place. In other words, if you keep the constraints and modify a row in the primary table, and related rows in the secondary table exist, the modification will be rejected by the constraint and the trigger will never fire. Furthermore, once you drop the constraint, your trigger solution needs to take care of all referential integrity rules that might be broken, in addition to implementing the cascading actions.

Tip   Instead of dropping the foreign keys, you can just disable them using ALTER TABLE NOCHECK CONSTRAINT <constraint_name>. This will enable visual tools such as the Enterprise Manager's database designer to recognize the relationships and display them. Also, when you use the sp_help system stored procedure to investigate the tables' schema, it will be easier to recognize the relationships.

Implementing ON DELETE CASCADE and ON UPDATE CASCADE with triggers

Before continuing, you need to drop the existing foreign keys, as shown in the following script.

Dropping the foreign keys

  ALTER TABLE OrderDetails DROP CONSTRAINT FK_OrderDetails_Orders
GO
ALTER TABLE Employees DROP CONSTRAINT FK_Employees_Employees
  GO

Now that you are aware of the implications of using triggers in your solution, take a look at the flow diagram shown in Figure 4, which shows the components you should use depending on the types of cascading actions you want to support. Each component is discussed in detail in the following sections.

Click here for larger image

Figure 4. Flow diagram of trigger solution to referential integrity enforcement (click image to see larger picture)

Cascading deletes

In the first junction shown in the preceding illustration, you check whether your solution should implement NO ACTION. If this is the case, you simply create a FOREIGN KEY (Component 1 in the preceding illustration), as discussed earlier in the NO ACTION (restrict) section of the article, and your flow diagram ends.

If you implement any kind of cascade action, you go to the second junction, where you check whether your solution handles the DELETE CASCADE action. If it does, you implement a DELETE CASCADE trigger (Component 2 in the preceding illustration). You need to implement the cascade trigger a bit differently in a relationship between two tables than in a single-table relationship. In the Orders and OrderDetails example, you can implement the trigger as shown in the following script.

Creation script for the trg_d_orders_on_delete_cascade trigger

  CREATE TRIGGER trg_d_orders_on_delete_cascade ON Orders FOR DELETE
AS

DELETE FROM OrderDetails
FROM
    OrderDetails AS OD
  JOIN
    deleted      AS D ON OD.orderid = D.orderid
  GO
Note   In order to make the code shorter, most of the triggers displayed in this article do not include the IF @@rowcount = 0 check. This check is important to ensure that the code in the trigger will not be invoked if the trigger is fired as a result of a modification that did not affect any rows in the table. In a production environment, you should add these checks.

To understand how the trigger works, delete an order from the Orders table, as shown in the following script. (Remember, you can wrap the modification in a transaction in the examples so the changes will not commit in the database.)

Testing the trg_d_orders_on_delete_cascade trigger

DELETE FROM Orders
WHERE orderid = 10002

Take a look at Figure 5, which shows what's happening inside the trigger.

Aa902684.sql_refintegrity05(en-us,SQL.80).gif

Figure 5. Inside the CASCADE DELETE trigger

The DELETE operation in the preceding script starts the following activity:

  1. The DELETE operation is written to the transaction log.
  2. Order 10002 is deleted from the Orders table.
  3. The DELETE trigger is fired. While inside the trigger, order 10002 does not exist in the Orders table. The deleted table, which you can think of as a view on the deleted row in the transaction log, contains the row for order 10002.
  4. The DELETE trigger deletes all related rows from the OrderDetails table by performing a join between the deleted table and the OrderDetails table. The join is used here to filter only the related rows in the OrderDetails table.

Thus you get the required result, as shown in the following tables.

Testing the trg_d_orders_on_delete_cascade trigger, Orders table

orderidcustomeridorderdate
10001FRODO1999-04-17 00:00:00.000
10003BILBO1999-04-19 00:00:00.000

Testing the trg_d_orders_on_delete_cascade trigger, OrderDetails table

orderidpartidquantity
100011112
100014210
10001725
100034110
100036135
100036515

The trg_d_orders_on_delete_cascade trigger also handles multirow deletes.

On the face of it, it looks like you could implement the same trigger on the Employees table. After all, if you delete an employee, you want to delete all his or her subordinates, as well. The difference here is that each of the subordinates might have subordinate employees, so your trigger needs to handle recursion; otherwise, it will delete the subordinates of the employee you deleted, but will leave their subordinates without a manager.

The ability of triggers to fire recursively is determined in the database level by the setting of the recursive triggers database option, which is disabled by default. If you want to allow recursive triggers, you first need to enable this option, but then you face another problem. Triggers fire as a result of a DELETE operation even if the DELETE operation did not affect any rows. To understand the consequence of the fact that triggers fire even if no rows are affected, suppose you enabled recursive triggers, implemented the DELETE cascade trigger, and deleted Robert's row from the Employees table.

Currently, the Employees table should look like the one shown in the following table.

Employees table

empidmgridempnamesalary
1NULLNancy10000.0000
21Andrew5000.0000
31Janet5000.0000
41Margaret5000.0000
52Steven2500.0000
62Michael2500.0000
73Robert2500.0000
83Laura2500.0000
93Ann2500.0000
104Ina2500.0000
117David2000.0000
127Ron2000.0000
137Dan2000.0000
1411James1500.0000

Now try the DELETE statement shown in the following script.

Testing a DELETE CASCADE trigger on the Employees table with recursive triggers enabled

DELETE FROM Employees
WHERE empid = 7

Robert's row is removed from the table, and the trigger fires for the first time. The deleted table contains Robert's row. Then the trigger deletes Robert's subordinates (David, Ron, and Dan) by joining the Employees table to the deleted table. The result is shown in the following table.

Testing a DELETE CASCADE trigger on the Employees table with recursive triggers enabled the first time the trigger fires

empidmgridempnamesalary
1NULLNancy10000.0000
21Andrew5000.0000
31Janet5000.0000
41Margaret5000.0000
52Steven2500.0000
62Michael2500.0000
83Laura2500.0000
93Ann2500.0000
104Ina2500.0000
1411James1500.0000

The trigger is fired again for the second time recursively. The deleted table contains the rows of David, Ron, and Dan, and the trigger deletes their subordinates (in this case, only David has a subordinate—James) by joining the Employees table to the deleted table. The result is shown in the following table.

Testing a DELETE CASCADE trigger on the Employees table with recursive triggers enabled the second time the trigger fires

empidmgridempnamesalary
1NULLNancy10000.0000
21Andrew5000.0000
31Janet5000.0000
41Margaret5000.0000
52Steven2500.0000
62Michael2500.0000
83Laura2500.0000
93Ann2500.0000
104Ina2500.0000

The trigger is fired again for the third time recursively. The deleted table contains James' row. Notice that James doesn't have subordinates, but the trigger is unaware of that because it doesn't perform any existence checks prior to the DELETE, hence the DELETE is issued. The DELETE operation doesn't affect any rows, but it is a DELETE just the same, so another trigger is fired with no rows in the deleted table.

This recursive loop would continue endlessly if there were no limitation to the number of nesting levels. However, because there is a nesting level limitation (16 in SQL Server version 6.5 and 32 in later versions), all modifications are rolled back once that limitation is reached. To solve the problem of the trigger firing even when the modification affects no rows, you can simply add an existence check to the trigger prior to the DELETE, as shown in the following script.

Creation script for the trg_d_employees_on_delete_cascade trigger

  CREATE TRIGGER trg_d_employees_on_delete_cascade ON Employees FOR DELETE
AS

IF EXISTS(SELECT *
          FROM
              Employees AS E
            JOIN
              deleted   AS D ON E.mgrid = D.empid)
  DELETE FROM Employees
  FROM
      Employees AS E
    JOIN
      deleted   AS D ON E.mgrid = D.empid
  GO
Note   Be careful when you issue a DELETE to the Employees table. Think of what would happen if you were to remove Nancy's row.

Preventing illegal deletes from the referenced table

Continuing with the flow diagram shown previously (Flow Diagram of Trigger Solution to Referential Integrity Enforcement), suppose you don't want to support the DELETE CASCADE action. If you have come this far in the flow diagram, it means that you support the UPDATE CASCADE action; hence, you don't have a foreign key to prevent an illegal DELETE of a row in the primary table that has related rows in the secondary table. Therefore, you need to enforce this rule with a prevent_delete trigger (Component 3 in the flow diagram). This trigger is implemented the same way in a relationship between two tables as in a single-table relationship.

Using the Orders and OrderDetails relationship as an example, create the trigger shown in the following script.

Creation script for the trg_d_orders_prevent_delete trigger

  CREATE TRIGGER trg_d_orders_prevent_delete ON Orders FOR DELETE
AS

IF EXISTS(SELECT *
          FROM
              OrderDetails AS OD
            JOIN
              deleted      AS D ON OD.orderid = D.orderid)
BEGIN 
  RAISERROR('The Orders you are trying to delete have related rows 
            in OrderDetails.  TRANSACTION rolled back.', 10, 1)
  ROLLBACK TRANSACTION
END
  GO

The fact that the activity inside the trigger is part of a transaction started before the modification that fires the trigger enables you to roll back the transaction if you discover an illegal DELETE attempt.

You can use the same kind of trigger in the Employees table, as the following script shows.

Creation script for the trg_d_employees_prevent_delete trigger

  CREATE TRIGGER trg_d_employees_prevent_delete ON Employees FOR DELETE 
AS

IF EXISTS(SELECT *
          FROM
              Employees AS E
            JOIN
              deleted   AS D ON E.mgrid = D.empid)
BEGIN 
  RAISERROR('The employees you are trying to delete have subordinates.  
            TRANSACTION rolled back.', 10, 1)
  ROLLBACK TRANSACTION
END
  GO

Cascading Updates

At the next junction in the flow diagram, you check whether your solution handles the UPDATE CASCADE action. If it does, you implement an UPDATE CASCADE trigger (Component 4 in the diagram). This trigger is more complex than a DELETE CASCADE trigger. It's not just a matter of locating the related rows in the secondary table—joining the deleted table to the secondary table can easily do this. You also need to update the relevant rows in the secondary table with the updated values from the primary table.

For example, if you change the order ID of order 10002 to 10004, you need first to locate all order details belonging to order 10002, and then UPDATE their orderid column to 10004. It is not too complex to handle such an UPDATE as long as only one order ID changes.

In this section, you'll start with single-row updates and then look at the problems (and solutions) involved with multirow updates. Begin by writing a trigger that implements this UPDATE CASCADE operation on the Orders table, as shown in the following script.

Creation script for the trg_u_orders_on_update_cascade trigger

  CREATE TRIGGER trg_u_orders_on_update_cascade ON Orders FOR UPDATE
AS

DECLARE @numrows int
SET @numrows = @@rowcount
IF UPDATE(orderid)
  IF @numrows = 1
    UPDATE OrderDetails
      SET orderid = (SELECT orderid FROM inserted)
    FROM
        OrderDetails AS OD
      JOIN
        deleted      AS D ON OD.orderid = D.orderid
  ELSE IF @numrows > 1
  BEGIN
    RAISERROR('Updates to more than one row in Orders are not allowed.  
              TRANSACTION rolled back.', 10, 1)
    ROLLBACK TRANSACTION
  END
  GO

You first save the number of affected rows in a variable so you can later check it. Next you check whether the orderid column was updated. If it wasn't, this UPDATE doesn't involve cascading; hence, it is not your concern. If it was, you continue and check whether more than one row was modified. If this is the case, your trigger cannot handle this situation and it rolls back the modification. If only one row was modified, you perform the CASCADE UPDATE operation. To understand how the CASCADE UPDATE works, UPDATE an order from the Orders table, as shown in the following script.

Testing the trg_u_orders_on_update_cascade trigger

  UPDATE Orders
  SET orderid = 10004
  WHERE orderid = 10002

Take a look at Figure 6, which shows what's happening inside the trigger. You join the deleted table with the OrderDetails table to filter only the related order details, and then update their orderid column to the new value, which is fetched from the inserted table by using a subquery. You can issue such a subquery because you know that there is one and only one row in the inserted table; otherwise, the subquery would have returned more than one value and the update would have failed. Thus you get the required result, as shown in the following tables.

Aa902684.sql_refintegrity06(en-us,SQL.80).gif

Figure 6. Inside the CASCADE UPDATE trigger

Testing the trg_u_orders_on_update_cascade trigger, Orders table

orderidcustomeridorderdate
10001FRODO1999-04-17 00:00:00.000
10003BILBO1999-04-19 00:00:00.000
10004GNDLF1999-04-18 00:00:00.000

Testing the trg_u_orders_on_update_cascade trigger, OrderDetails table

orderidpartidquantity
100011112
100014210
10001725
100034110
100036135
100036515
10004149
100045140

There are no problems with locating the related rows in the OrderDetails table—you simply join it to the deleted table because it still holds the old order ID values. But how do you relate the rows in the inserted table to the relevant rows in the OrderDetails table now that they have new order IDs? Your immediate reaction might be to try to access the rows in the deleted and inserted tables by using cursors, based on the assumption that a record in one cursor is in the same position as the related record in the other cursor. If you could guarantee that, you could loop through both cursors, one record at a time, and update the related rows in the OrderDetails table, as both the old order ID and the new order ID would be related.

However, this cannot be guaranteed at all. There is no way you can guarantee that the order of the records in the cursor on the deleted table will have the same order as the related records in a cursor you place on the inserted table. You're pretty much stuck, unless you devise a way to relate the rows prior to the UPDATE to the rows after the UPDATE. One way to handle this is to add a surrogate key to the Orders table, to which UPDATEs will not be allowed. The sole purpose of this key is to be the join column between the deleted and the inserted tables. You can use the IDENTITY property to automatically generate its values. Also, you will need your UPDATE trigger to make sure that UPDATEs to this column will be rejected.

First, add the surrogate_key column to your Orders table, as shown in the following script.

Adding a surrogate key to the Orders table

  ALTER TABLE Orders
  ADD surrogate_key int NOT NULL IDENTITY(1,1)
      CONSTRAINT UNQ_orders_surrogate_key UNIQUE

The result of this addition is shown in the following table.

The Orders table with the addition of the surrogate key

orderidcustomeridorderdatesurrogate_key
10001FRODO1999-04-17 00:00:00.0001
10002GNDLF1999-04-18 00:00:00.0002
10003BILBO1999-04-19 00:00:00.0003

Now you can modify the trigger to support multirow UPDATE statements, as shown in the following script.

Adding multirow support for the trg_u_orders_on_update_cascade trigger

  ALTER TRIGGER trg_u_orders_on_update_cascade ON Orders FOR UPDATE
AS

DECLARE @numrows int
SET @numrows = @@rowcount
IF UPDATE(surrogate_key)
BEGIN
  RAISERROR('Updates to surrogate_key are not allowed.  
            TRANSACTION rolled back.', 10, 1)
  ROLLBACK TRANSACTION
END
ELSE
  IF UPDATE(orderid) AND @numrows > 0
    UPDATE OrderDetails
      SET orderid = I.orderid
    FROM
        OrderDetails AS OD
      JOIN
        deleted      AS D ON OD.orderid      = D.orderid
      JOIN
        inserted     AS I ON D.surrogate_key = I.surrogate_key
  GO

First, you make sure that the UPDATE doesn't affect the surrogate key. Then, you check whether the orderid column was updated and also whether any row was affected by the UPDATE at all. If the orderid column was updated and at least one row was affected by the UPDATE, you perform the cascade UPDATE operation. In all other cases, you don't need to take any action.

To understand how the CASCADE UPDATE works, update all of the orders, as shown in the following script.

Testing the trg_u_orders_on_update_cascade trigger with a multirow update

UPDATE Orders
  SET orderid = 20004 - orderid

Take a look at Figure 7, which shows what's happening inside the trigger.

Click here for larger image

Figure 7. Inside the multirow UPDATE CASCADE trigger (click image to see larger picture)

As you can see, you join the OrderDetails table with the deleted table to filter the relevant rows as you did before. You also join the deleted table to the inserted table to correlate the rows before and after the UPDATE. You can do this thanks to the surrogate key you added. Now you can use the updated value of the orderid column from the inserted table to update the orderid column in the OrderDetails table. Take a look at the results shown in the following tables.

Testing the trg_u_orders_on_update_cascade trigger with a multirow update, Orders table

orderidcustomeridorderdatesurrogate_key
10001BILBO1999-04-19 00:00:00.0003
10002GNDLF1999-04-18 00:00:00.0002
10003FRODO1999-04-17 00:00:00.0001

Testing the trg_u_orders_on_update_cascade trigger with a multirow update, OrderDetails table

orderidpartidquantity
100014110
100016135
100016515
10002149
100025140
100031112
100034210
10003725

You can do basically the same thing in the Employees table, except you don't need recursion because when you update the empid of a certain employee, you only want to update the mgrid of this employee's direct subordinates. You don't need to worry about the fact that you turned on recursion for the database because you first check whether the empid column was updated before you perform the cascade UPDATE.

To see how this works, begin by adding a surrogate key, as shown in the following script.

Adding a surrogate key to the Employees table

  ALTER TABLE Employees
  ADD surrogate_key int NOT NULL IDENTITY(1,1)
      CONSTRAINT UNQ_employees_surrogate_key UNIQUE
  GO

The following table shows the Employees table contents with the surrogate key added. The table shows that the IDENTITY values were generated in order of empid, because the Employees table was scanned in that order, but keep in mind that there is no guarantee of that.

The Employees table with the addition of the surrogate key

empidmgridempnamesalarysurrogate_key
1NULLNancy10000.00001
21Andrew5000.00002
31Janet5000.00003
41Margaret5000.00004
52Steven2500.00005
62Michael2500.00006
73Robert2500.00007
83Laura2500.00008
93Ann2500.00009
104Ina2500.000010
117David2000.000011
127Ron2000.000012
137Dan2000.000013
1411James1500.000014

Now create the cascade UPDATE trigger by running the following script.

Creation script for the trg_u_employees_on_update_cascade trigger

  CREATE TRIGGER trg_u_employees_on_update_cascade ON Employees FOR UPDATE
AS

DECLARE @numrows int
SET @numrows = @@rowcount
IF UPDATE(surrogate_key)
BEGIN
  RAISERROR('Updates to surrogate_key are not allowed.  TRANSACTION 
   rolled back.', 10, 1)
  ROLLBACK TRANSACTION
END
ELSE
  IF UPDATE(empid) AND @numrows > 0
    UPDATE Employees
      SET mgrid = I.empid
    FROM
        Employees AS E
      JOIN
        deleted   AS D ON E.mgrid         = D.empid
      JOIN
        inserted  AS I ON D.surrogate_key = I.surrogate_key
  GO

As you can see, the trigger looks the same as the one you used for the Orders table. To see if it works, you can issue the UPDATE shown in the following script.

Testing the trg_u_employees_on_update_cascade trigger

UPDATE Employees
  SET empid = 15 - empid

The result in the following table shows that the trigger did its job.

Testing the trg_u_employees_on_update_cascade trigger with a multirow update, Employees table

empidmgridempnamesalarysurrogate_key
14James1500.000014
28Dan2000.000013
38Ron2000.000012
48David2000.000011
511Ina2500.000010
612Ann2500.00009
712Laura2500.00008
812Robert2500.00007
913Michael2500.00006
1013Steven2500.00005
1114Margaret5000.00004
1214Janet5000.00003
1314Andrew5000.00002
14NULLNancy10000.00001

Preventing illegal updates to the referenced table

Continuing with the flow diagram, suppose you don't want to support the UPDATE cascade action. Instead of creating a cascade UPDATE trigger, you could create a prevent_update trigger (Component 5 in the diagram), which looks like the prevent_delete trigger discussed earlier. All you need to do is find out whether the rows you're trying to update in the primary table have related rows in the secondary table.

The following script creates the trigger on the Orders table.

Creation script for the trg_u_orders_prevent_update trigger

  CREATE TRIGGER trg_u_orders_prevent_update ON Orders FOR UPDATE
AS

IF EXISTS(SELECT *
          FROM
              OrderDetails AS OD
            JOIN
              deleted      AS D ON OD.orderid = D.orderid)
BEGIN 
  RAISERROR('The Orders you are trying to update have related rows 
            in OrderDetails.  TRANSACTION rolled back.', 10, 1)
  ROLLBACK TRANSACTION
END
  GO

And this script creates the trigger on the Employees table.

Creation script for the trg_u_employees_prevent_update trigger

  CREATE TRIGGER trg_u_employees_prevent_update ON Employees FOR UPDATE 
AS

IF EXISTS(SELECT *
          FROM
              Employees AS E
            JOIN
              deleted   AS D ON E.mgrid = D.empid)
BEGIN 
  RAISERROR('The employees you are trying to update have subordinates.  
            TRANSACTION rolled back.', 10, 1)
  ROLLBACK TRANSACTION
END
  GO

Preventing orphaned rows in the referencing table

You have one more thing to do. If you've gotten this far in the flow diagram, it signifies that you chose to use triggers to enforce referential integrity. This means that Rules 3 and 4 discussed in the NO ACTION (restrict) section of the article need to be enforced with a trigger as well. These rules disallow INSERT and UPDATE operations that result in a row in the secondary table with no related row in the primary table—in other words, operations that create an orphaned row. Because you need to ensure that the result is legal, you can use one trigger that checks the rows in the inserted table for both INSERT and UPDATE operations. Unlike all previous triggers, this trigger will be created on the secondary table.

Run the following script to create the trg_iu_orderdetails_prevent_insupd trigger.

Creation script for the trg_iu_orderdetails_prevent_insupd trigger

  CREATE TRIGGER trg_iu_orderdetails_prevent_insupd 
  ON OrderDetails FOR INSERT, UPDATE
AS

DECLARE @numrows int
SET @numrows = @@rowcount

IF UPDATE(orderid) AND @numrows > 0
  IF @numrows <> (SELECT COUNT(*)
                  FROM Orders AS O JOIN inserted AS I
                    ON O.orderid = I.orderid)
  BEGIN
    RAISERROR('Result rows in OrderDetails are orphaned. 
              TRANSACTION rolled back.', 10, 1)
    ROLLBACK TRANSACTION
  END
  GO

Notice how the code in the trigger checks whether you have orphaned rows. If you don't have orphaned rows, an inner join between the inserted table, which holds the new order details, and the Orders table will result in the same number of rows as in the inserted table, because each order detail has a matching order. If at least one of the order details doesn't have a matching order, the number of rows in the inserted table will be greater than the number of rows resulting from the inner join. This trigger can also be implemented with an IF NOT EXISTS() to check to see if order detail rows don't have a matching row in the Orders table. You can try to implement this trigger using the NOT EXISTS() predicate as an exercise.

For the Employees table, you need to take a slightly different approach. Keeping in mind that top-level employees have NULLs in the mgrid column, the number of rows affected by an UPDATE to a top-level employee will be different than the number of rows returned by a join between the affected employees and their managers, as NULLs do not match. You can tackle the problem by removing top-level employees from the equation. You compare the number of rows in inserted, excluding NULLs, to the number of rows in the result of the join between inserted and the primary table, which, in this case, is Employees. Run the following script to create the trg_iu_employees_prevent_insupd trigger.

Creation script for the trg_iu_employees_prevent_insupd trigger

  CREATE TRIGGER trg_iu_employees_prevent_insupd 
  ON Employees FOR INSERT, UPDATE
AS

IF @@rowcount > 0 AND UPDATE(mgrid)
BEGIN
  DECLARE @numrows int

  SELECT
    @numrows = COUNT(*)
  FROM
    inserted
  WHERE
    mgrid IS NOT NULL

  IF @numrows <> (SELECT COUNT(*)
                  FROM Employees AS E JOIN inserted AS I
                    ON E.empid = I.mgrid)
  BEGIN
    RAISERROR('Result rows in Employees are orphaned. 
              TRANSACTION rolled back.', 10, 1)
    ROLLBACK TRANSACTION
  END
END
  GO

Notice that this time you join the Employees table to the inserted table ON E.empid = I.mgrid, as opposed to the previous triggers, where you used the join condition ON E.mgrid = I.empid. Now you are looking for illegal inserts to the secondary table, so you look at the Employees table from the managers' point of view and not from the employees' point of view. The inserted table contains new employees, and you want to check whether their mgrid column represents an existing employee in the Employees table.

Encapsulating the Logic

As you've seen in the previous section, if you want to enforce referential integrity with triggers, there's a pretty straightforward flow diagram that shows you which triggers to create. You follow the same flow diagram for any pair of tables in a relationship. The only difference between the triggers in each implementation is the names of the tables and the columns.

You can create a stored procedure that encapsulates the logic of the flow diagram and creates the set of triggers for you. It will receive the table and column names as parameters. You would execute the stored procedure as follows.

Invoking the sp_CreateRelationship stored procedure

  EXEC sp_CreateRelationship
  @prmtbl        = Orders,
  @sectbl        = OrderDetails,
  @prmcol        = orderid,
  @seccol        = orderid,
  @deletecascade = 1,
  @updatecascade = 1

EXEC sp_CreateRelationship
  @prmtbl        = Employees,
  @sectbl        = Employees,
  @prmcol        = empid,
  @seccol        = mgrid,
  @deletecascade = 1,
  @updatecascade = 1

Implementing SET NULL and SET DEFAULT with Triggers

ON DELETE SET NULL and ON UPDATE SET NULL are implemented very much the same way as their respective cascade triggers. However, there is a new issue you need to keep in mind. In both a 1:1 (one-to-one) relationship and a 1:M (one-to-many) relationship (such as the one between the Orders table and the OrderDetails table), you cannot keep a PRIMARY KEY or UNIQUE constraint on the secondary table that includes the referencing column as part of the key. In such a case, your cascading operation might result in duplicate keys and would therefore be rolled back. If your primary key contains the referencing column, you can substitute it with an existing alternate key, if one exists in the table, or create a surrogate PRIMARY KEY. Also, if you are implementing SET NULL, the referencing column in the secondary table must allow NULLs, in order to accept them.

If you want to implement ON DELETE SET NULL in your Orders and OrderDetails example, instead of creating a trigger that implements the DELETE cascade logic, as in the previous section, you can create the trigger shown in the following script (though first you would need to drop the PRIMARY KEY from the OrderDetails table and alter the orderid column so that it would allow NULLs).

Creation script for the trg_d_orders_on_delete_set_null trigger

  CREATE TRIGGER trg_d_orders_on_delete_set_null ON Orders FOR DELETE
AS

UPDATE OrderDetails
  SET orderid = NULL
FROM
    OrderDetails AS OD
  JOIN
    deleted      AS D ON OD.orderid = D.orderid
  GO

If you want to support the SET DEFAULT action instead, assuming that there is a DEFAULT value defined for the referencing column, you would implement a trigger that looks almost like the trigger that implements the SET NULL action. You just replace the NULL in the SET clause with the keyword DEFAULT, as shown in the following script.

Creation script for the trg_d_orders_on_delete_set_default trigger

  CREATE TRIGGER trg_d_orders_on_delete_set_default ON Orders FOR DELETE
AS

UPDATE OrderDetails
  SET orderid = DEFAULT
FROM
    OrderDetails AS OD
  JOIN
    deleted      AS D ON OD.orderid = D.orderid
  GO

You can implement the ON UPDATE SET NULL or ON UPDATE SET DEFAULT actions in a similar way by slightly modifying the logic of the respective UPDATE cascade triggers, which were discussed previously in this article.

Using Triggers in SQL Server 2000

In SQL Server 2000, there is no compelling reason to use triggers to enforce cascading operations. Declarative referential constraints can take care of this, as discussed in the Implementing Cascading Operations Using a FOREIGN KEY section. However, in certain situations, declarative referential constraints can't be implemented. For example, you can't create a FOREIGN KEY that points to a table in another database, but you can still enforce referential integrity with triggers the same way you would in versions prior to SQL Server 2000. In fact, everything covered in the previous section is valid for SQL Server 2000, as well.

The only types of triggers that were supported in previous versions of SQL Server are what are now called AFTER triggers, because SQL Server 2000 supports INSTEAD OF triggers as well. Remember that AFTER triggers fire only after constraints are checked, so if you decide to enforce referential integrity with AFTER triggers, you have to drop or disable all existing foreign keys, just as in previous versions; otherwise, the AFTER triggers will never fire.

With INSTEAD OF triggers, things are slightly different. Because these triggers substitute for the modification that was submitted to the database, they fire before constraints are checked. INSTEAD OF triggers on the primary table can coexist with declarative cascading referential constraints on the secondary table, so you don't need to implement the cascading actions in the trigger if you need to take preliminary actions before the modification actually happens in the primary table.

Before going into details, it's important to review a few issues, as they will come into play here. Now that you have declarative referential constraints that can take care of cascade actions, and also INSTEAD OF triggers and AFTER triggers, a modification to the primary table will be processed in the following order:

  1. Modification is issued.
  2. If an INSTEAD OF trigger is defined on the relevant type of modification, it substitutes the modification. The INSTEAD OF trigger is responsible for resubmitting the modification.
  3. If an INSTEAD OF trigger was not defined, or if the modification was resubmitted by the INSTEAD OF trigger, the constraint performs its safety checks and the cascade operation fires, if one was defined on relevant type of modification. Note that if the INSTEAD OF trigger modifies the table on which it is placed, it will not be called recursively. However, if it performs modifications that do not result in recursive calls to itself, those modifications will be treated just as any other modification—in other words, they will be processed in the same order described here, starting from Step 1.
  4. If the modification was not rejected by a constraint, any defined AFTER triggers fire.

However, if you need an INSTEAD OF trigger on the secondary table, it cannot coexist with a declarative cascading referential constraint of the same type. For example, an INSTEAD OF DELETE trigger cannot coexist with a FOREIGN KEY with ON DELETE CASCADE defined, and an INSTEAD OF INSERT or UPDATE trigger cannot coexist with a FOREIGN KEY with ON UPDATE CASCADE defined. If you need to support both an INSTEAD OF trigger on the secondary table and also cascading actions, you have the following options:

  • 10.Drop or disable the FOREIGN KEY, and enforce cascade actions with AFTER triggers.
  • 11.Keep the FOREIGN KEY with NO ACTION in the problematic operation, and implement cascade actions for that operation with an INSTEAD OF trigger on the primary table.

You've already learned how to implement the first option. The second option might look appealing at first glance, but don't count your chickens before they're hatched…

First, you need to try and take care of a situation where you have an INSTEAD OF DELETE trigger on the secondary table that has nothing to do with your cascade actions. You'll use the Orders and OrderDetails scenario to run your examples.

Note   Before you start to add triggers, re-create and repopulate the Orders and OrderDetails tables, and add the surrogate key to the OrderDetails table.

Suppose there's a very important INSTEAD OF DELETE trigger on the OrderDetails table, as shown in the following script.

Creation script for the trg_d_orderdetails_on_delete_print_hello trigger

  CREATE TRIGGER trg_d_orderdetails_on_delete_print_hello 
  ON OrderDetails  INSTEAD OF DELETE
AS

PRINT 'Hello from instead of delete trigger on OrderDetails'

-- resubmit the delete
DELETE FROM OrderDetails
FROM
    OrderDetails AS OD
  JOIN
    deleted AS D ON  OD.orderid = D.orderid
                 AND OD.partid  = D.partid
  GO

Now you need to add cascade actions for both DELETE and UPDATE. You cannot use the ON DELETE CASCADE option because you have an INSTEAD OF DELETE trigger on the OrderDetails table. Instead, you add the FOREIGN KEY shown in the following script.

Adding a foreign key to the OrderDetails table with ON DELETE NO ACTION

  ALTER TABLE OrderDetails ADD CONSTRAINT FK_OrderDetails_Orders
  FOREIGN KEY(orderid)
  REFERENCES Orders(orderid)
  ON DELETE NO ACTION
  ON UPDATE CASCADE
  GO

If you want to support a cascade DELETE action, you have to do it in an INSTEAD OF DELETE trigger on the Orders table; otherwise the constraint will prevent it. The task doesn't look too hard, as the following script shows.

Creation script for the trg_d_orders_ON_DELETE_CASCADE trigger

  CREATE TRIGGER trg_d_orders_ON_DELETE_CASCADE ON Orders INSTEAD OF DELETE
AS

-- perform the delete cascade action
DELETE FROM OrderDetails
FROM
    OrderDetails AS OD
  JOIN
    deleted      AS D ON OD.orderid = D.orderid

-- resubmit the delete
DELETE FROM Orders
FROM
    Orders  AS O
  JOIN
    deleted AS D ON O.orderid = D.orderid
  GO

You first delete the rows from the OrderDetails table, and then resubmit the DELETE for the Orders table. This way, at no point do you leave orphaned order details, so you don't break any referential integrity rules enforced by the FOREIGN KEY. The tables should now look the same as they did in the beginning of the article (see the first two tables), with the addition of the surrogate key to the Orders table.

You can now perform both an UPDATE and a DELETE to see that your solution works (remembering to wrap the modifications in a transaction that you can roll back, so the changes will not persist in the database, of course). First try the update shown in the following script.

Testing an UPDATE to see if the DRI UPDATE CASCADE works

  UPDATE Orders
  SET orderid = 10004
  WHERE orderid = 10002

The result of this UPDATE statement is shown in the following tables.

Testing an UPDATE to see if the DRI UPDATE CASCADE works, Orders table

orderidcustomeridorderdate
10001FRODO1999-04-17 00:00:00.000
10003BILBO1999-04-19 00:00:00.000
10004GNDLF1999-04-18 00:00:00.000

Testing an UPDATE to see whether the DRI UPDATE CASCADE works, OrderDetails table

orderidpartidquantity
100011112
100014210
10001725
100034110
100036135
100036515
10004149
100045140

Next, you can try the DELETE statement shown in the following script.

Testing the trg_d_orders_ON_DELETE_CASCADE trigger

  DELETE FROM Orders
WHERE orderid = 10002

  Hello from instead of delete trigger on OrderDetails

Apart from the "Hello" message, you can see the result of this DELETE statement in the following tables.

Testing the trg_d_orders_ON_DELETE_CASCADE trigger, Orders table

orderidcustomeridorderdate
10001FRODO1999-04-17 00:00:00.000
10003BILBO1999-04-19 00:00:00.000

Testing the trg_d_orders_ON_DELETE_CASCADE trigger, OrderDetails table

orderidpartidquantity
100011112
100014210
10001725
100034110
100036135
100036515

You can rub your hands with joy as you head on to the puzzle, where you'll take care of a situation where an INSTEAD OF UPDATE trigger exists on the OrderDetails table. This time you'll need to perform some acrobatics to leave the FOREIGN KEY and the INSTEAD OF UPDATE trigger on the OrderDetails table, and implement cascade updates as well.

Conclusion

As you have read, Microsoft SQL Server 2000 has flexible tools for maintaining data integrity. Planning for data integrity as part of the database normalization process is vital. This article has covered the role of Declarative Referential Integrity and Triggers in data integrity.

Show:
© 2014 Microsoft. All rights reserved.