Microsoft SQL Server 2008 R2 Unleashed- P103:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 964 CHAPTER 30 Creating and Managing Triggers 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 customerid 1 Server Msg 547 Level 16 State 1 The DELETE statement conflicted with COLUMN REFERENCE constraint FK_orders_customers . The conflict occurred in database BigPubs2008 table orders column customer_id . The statement has been terminated. This result might not be what you expected. The FOREIGN KEY constraint here restricts the DELETE statement so the trigger never fires. The trigger in this example is an AFTER trigger. Therefore the trigger does not fire and the cascading action never takes place. You have several options to get around this situation 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 actions in stored procedures. Keep the FOREIGN KEY constraint and perform all cascading actions in the application. Use an INSTEAD OF trigger in place of the AFTER trigger. Use the new cascading referential integrity constraints. Listing shows how you can disable the FOREIGN KEY constraint so that a cascading delete can occur. LISTING Disabling the FOREIGN KEY Constraint to the customers Table So That a 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 customerid customername customercomments Download from Using DML Triggers 965 2 Software Suppliers AB Elisabeth is contact. 3 Firmware Suppliers AB Mike is contact. orderid customerid orderdate In Listing the cascading deletes occur via the trigger because the FOREIGN KEY constraint is disabled. Compared to a trigger for cascading deletes a trigger for cascading updates is more complex and not as .
