Enforcing Referential Integrity with Triggers

Several options are available to enforce RI, but the trigger is still a viable alternative. The trigger provides a great deal of flexibility and allows you to customize your RI solution to fit your needs. Some of the other alternatives do not provide the same degree of customization.

TIP

If you are in a database environment in which multiple databases are used with related data, then the trigger can be invaluable for enforcing referential integrity. The trigger has the ability to span databases, and it can ensure that data rows that are inserted into a table in one database are valid based on rows in another database.

In Listing 29.6, you will re-create and populate the customers and orders tables in the sample Pubs database.

Listing 29.6 Creating and Populating the customers and orders Tables

 if exists (select * from sysobjects    where id = object_id('orders') and sysstat & 0xf = 3)        drop table orders GO if exists (select * from sysobjects    where id = object_id('customers') and sysstat & 0xf = 3)        drop table customers GO CREATE TABLE customers (customer_id INT PRIMARY KEY NOT NULL, customer_name NVARCHAR(25) NOT NULL, customer_comments NVARCHAR(22) NULL) CREATE TABLE orders (order_id INT  PRIMARY KEY NOT NULL, customer_id INT, order_date DATETIME, CONSTRAINT FK_orders_customers        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)) INSERT customers (customer_id, customer_name, customer_comments) VALUES(1, 'Hardware Suppliers AB','Stephanie is contact.') INSERT customers (customer_id, customer_name, customer_comments) VALUES(2, 'Software Suppliers AB','Elisabeth is contact.') INSERT customers (customer_id, customer_name, customer_comments) VALUES(3, 'Firmware Suppliers AB','Mike is contact.') INSERT orders (order_id, customer_id, order_date) VALUES(100, 1, GETDATE()) INSERT orders (order_id, customer_id, order_date) VALUES(101, 1, GETDATE()) INSERT orders (order_id, customer_id, order_date) VALUES(102, 1, GETDATE()) SELECT * FROM customers SELECT * FROM orders customer_id customer_name             customer_comments ----------- ------------------------- ---------------------- 1           Hardware Suppliers AB     Stephanie is contact. 2           Software Suppliers AB     Elisabeth is contact. 3           Firmware Suppliers AB     Mike is contact. order_id    customer_id order_date            ----------- ----------- --------------------------- 100         1           1998-09-17 18:29:46.943 101         1           1998-09-17 18:29:46.973 102         1           1998-09-17 18:29:46.973 

The foreign key constraint prohibits the following:

• Inserting rows into the orders table for customer numbers that don't exist in the customers table

• Updating the orders table, changing the customer number to values that don't exist in the customers table

• Deleting rows in the customers table for which orders exist

• Updating the customers table, changing the customer number for which orders exist

You might want a cascading action instead of a restriction for the previous two actions. This would include automatically cascading the DELETE or UPDATE statement executed on the customers table to the orders table. You can do this with triggers.

Cascading Deletes

TIP

SQL Server 2000 has added a new feature that allows you to define cascading actions on your foreign key constraint. When defining the constraints on a table, you can use the ON UPDATE CASCADE or the ON DELETE CASCADE clauses, which cause changes to the primary key of a table to cascade to the related foreign key tables. Refer to Chapter 12 for further information on this option.

A cascading delete is relatively simple to create. Listing 29.7 shows a cascading delete trigger for the customers table.

Listing 29.7 Cascading Delete for the customers Table

 CREATE TRIGGER cust_del_orders ON customers FOR DELETE AS IF @@ROWCOUNT = 0  RETURN DELETE orders  FROM orders o , deleted d  WHERE o.customer_id = d.customer_id IF @@ERROR <> 0  BEGIN   RAISERROR ('ERROR encountered in cascading trigger.', 16, 1)   ROLLBACK TRAN   RETURN  END 

The following DELETE statement deletes the row for customer 1, so all three rows for that customer in the orders table should be deleted by the trigger:

 DELETE customers WHERE customer_id = 1  Server: Msg 547, Level 16, State 1 DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_orders_customers'. The conflict occurred in database 'pubs', table 'orders', column 'customer_id'. The statement has been aborted. 

This might not be what you expected. The foreign key constraint restricted the DELETE statement, so the trigger never fired . The trigger in this example is an AFTER trigger. Therefore, the trigger never fired, and the cascading action never took place.

You have several options to get around this:

• Remove the foreign key constraint from orders to customers.

• Disable the foreign key constraint from orders to customers.

• Keep the foreign key constraint and perform all cascading in stored procedures.

• Keep the foreign key constraint and perform all cascading in the application.

• Use an INSTEAD OF trigger in place of the AFTER trigger.

• Use the new cascading referential integrity constraints.

The second option will be exploited, as shown in Listing 29.8.

Listing 29.8 Disabling the Foreign Key Constraint to the customers Table So That Cascading Delete Can Occur

 ALTER TABLE orders  NOCHECK CONSTRAINT FK_orders_customers GO GO DELETE customers WHERE customer_id = 1 SELECT * FROM customers SELECT * FROM orders customer_id customer_name             customer_comments ----------- ------------------------- ---------------------- 2           Software Suppliers AB     Elisabeth is contact. 3           Firmware Suppliers AB     Mike is contact. order_id    customer_id order_date            ----------- ----------- --------------------------- 

The cascading took place and the foreign key constraint was disabled. A trigger for cascading updates is more complex and not so common. That will be discussed in the next section.

If you disable the constraint, you have a potential integrity problem. If rows are inserted or updated in the orders table, no verification ensures that the customer number exists in the customer table. You can take care of that with an INSERT and UPDATE trigger on the orders table (see Listing 29.9).

Listing 29.9 Handling a Restriction with a Trigger on the orders Table

 if exists (select * from sysobjects where id = object_id('dbo.ord_ins_upd_cust')         and sysstat & 0xf = 8)         drop trigger dbo.ord_ins_upd_cust GO CREATE TRIGGER ord_ins_upd_cust ON orders FOR INSERT, UPDATE AS IF EXISTS (SELECT * FROM inserted            WHERE customer_id NOT IN           (SELECT customer_id FROM customers))  BEGIN   RAISERROR('No customer with such customer number', 16, 1)   ROLLBACK TRAN   RETURN  END 

Cascading Updates

The cascading update is tricky to achieve. Modifying a primary key, per definition, is really deleting a row and inserting a new row. That is the problem. You lose the connection between the old and the new row in the customers table. How do you know which changes to cascade to which rows?

It's simpler if you can restrict the changes to one row (see Listing 29.10) because you have only one row in the deleted and the inserted tables. You know the customer number before and after the modification.

Listing 29.10 Cascading UPDATE in a Trigger

 if exists (select * from sysobjects where id = object_id('dbo.cust_upd_orders')         and sysstat & 0xf = 8)         drop trigger dbo.cust_upd_orders GO CREATE TRIGGER cust_upd_orders ON customers FOR UPDATE AS DECLARE @rows_affected int, @c_id_before int, @c_id_after int SELECT @rows_affected = @@ROWCOUNT IF @rows_affected = 0  RETURN -- No rows changed, exit trigger IF UPDATE(customer_id) BEGIN  IF @rows_affected = 1  BEGIN    SELECT @c_id_before = customer_id FROM deleted    SELECT @c_id_after = customer_id FROM inserted    UPDATE orders     SET customer_id = @c_id_after     WHERE customer_id = @c_id_before  END ELSE   BEGIN      RAISERROR ('Cannot update more than 1 row.', 16, 1)      ROLLBACK TRAN      RETURN   END END 

If several rows were updated, it's not easy to know which order belongs to which customer. You can easily modify the preceding trigger to handle the situation, where several rows change to the same value; however, this is not allowed because of the primary key on the customers table. Modifying several rows and changing the primary key value is rare, and you are not likely to encounter it.

NOTE

SQL Server 2000's new cascading foreign key constraints are an excellent alternative to triggers, and they are efficient. If you choose not to use the cascading feature, I still like the simplicity of constraints. That leaves handling cascading actions in stored procedures or in client applications.

Stored procedures are often a good choice because they essentially give application developers a function-based interface for modifications. If the implementation details (table structure or rules) change, client applications can be isolated from the changes, as long as the interfaces to the stored procedures stay the same. The question of how to handle a cascade is a matter of personal preference, however.

A note on cascading updates handled in a client application or stored procedure: This is a chicken-and-the-egg situation. You cannot change the primary key table first because other tables reference it. You also cannot change the referencing table because no row exists in the primary key table with a corresponding value.

The solution is to insert a new row in the referenced table with the new primary key value, change the referencing rows, and then delete the old row in the referenced table.