Implementing Cascading Updates and Deletes

SQL Server Declarative Referential Integrity (DRI) does not include the ability to cascade updates or deletes. You can implement this feature with triggers or allow the Upsizing Wizard to write the trigger for you.

Here is a sample of the triggers created by the Upsizing Wizard when the Northwind Orders and Customers tables are upsized with cascade updates selected.

This is the update trigger written by the Upsizing Wizard for the Northwind Customers table:

If exists (select * from sysobjects where id = object_id('dbo.Customers_UTrig') and sysstat & 0xf = 8)
   drop trigger dbo.Customers_UTrig
GO

CREATE TRIGGER Customers_UTrig ON Customers FOR UPDATE AS

/*
 * PREVENT NULL VALUES IN 'CompanyName'
 */
IF (SELECT Count(*) FROM inserted WHERE CompanyName IS NULL) > 0
    BEGIN
        RAISERROR 44444 'Field ''CompanyName'' cannot contain a null value.'
        ROLLBACK TRANSACTION
    END
/*
 * CASCADE UPDATES TO 'Orders'
 */
IF UPDATE(CustomerID)
    BEGIN
       UPDATE Orders
       SET Orders.CustomerID = inserted.CustomerID
       FROM Orders, deleted, inserted
       WHERE deleted.CustomerID = Orders.CustomerID
    END

GO

If the CustomerID field is changed (IF UPDATE(CustomerID)) in the Customers table, this trigger updates the Orders table, changing the old CustomerID to the new CustomerID (SET Orders.CustomerID = inserted.CustomerID) for every order that contained the old CustomerID value (WHERE deleted.CustomerID = Orders.CustomerID).

This trigger also includes code to prevent null values for CustomerID in the Customers table. The wizard also creates three other triggers to enforce the referential integrity:

• Customers-Delete trigger
  
  
• Orders-Insert trigger
  
  
• Orders-Update trigger

For more information, see Knowledge Base article Q142480, "INF: Cascading Deletes and Updates of Primary Keys."