Using Triggers - Cascading Deletes

(Page 2 of 4 )

Usually, referential integrity between two tables is implemented with a foreign key, such as in the following illustration:

In such cases, a foreign key prevents the user from deleting records from a parent table (Inventory) if a record is referenced in a linked table (InventoryProperty). The only way to delete the record would be to use the following code:

Delete dbo.InventoryProperty
Where InventoryId = 222

Delete dbo.Inventory
Where InventoryId = 222

In some cases, the system design requirements might call for cascading deletes, which automatically delete records from the linked table when the record in the parent table is deleted. In this case, only one command is required to delete any instance of an asset with an InventoryId value of 222:

Delete dbo.Inventory
Where InventoryId = 222

SQL Server 2000 introduced cascading referential integrity constraints that can implement this behavior. In SQL Server 7.0 and earlier versions, you had to use triggers to implement cascading operations. It is now recommended to use cascading referential integrity constraints, but since that is not possible in some cases (for example, when tables are in different databases), you should know how to do it with triggers.

The following example creates two new tables (without a foreign key), populates them with a few records, and creates a trigger that will implement a cascading delete:

Create Table dbo.MyInventory
     (
     Inventoryid int Not Null Identity (1, 1),
     EqId int Null,
     LocationId int Null,
     StatusId tinyint Null,
     LeaseId int Null,
     LeaseScheduleId int Null,
     OwnerId int Null,
     Rent smallmoney Null,
     Lease smallmoney Null,
     Cost smallmoney Null, 
     AcquisitionTypeID tinyint Null
     )
Go

Alter Table dbo.MyInventory Add Constraint
     PK_Inventory Primary Key Nonclustered
     (
     Inventoryid
     )
Go

Create Table dbo.MyInventoryProperty
     (
     InventoryId int Not Null,
     PropertyId smallint Not Null,
     Value varchar(50) Null
     )
Go
Alter Table dbo.MyInventoryProperty Add Constraint
     PK_InventoryProperty Primary Key Nonclustered
     (
     InventoryId,
     PropertyId
     )
Go
Create Trigger dbo.trMyInventory_CascadingDelete_D
On dbo.MyInventory
After Delete   --For delete
As

If @@Rowcount = 0
     Return
Delete dbo.MyInventoryProperty
where InventoryId In (Select InventoryID from deleted)
Go

Insert into dbo.MyInventory(EqId) Values (1)
Insert into dbo.MyInventory(EqId) Values (2)
Insert into dbo.myInventory(EqId) Values (3)
Insert into dbo.myInventory(EqId) Values (4)
Insert into dbo.myInventory(EqId) Values (5)

Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (1, 1, 'ACME')
Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (1, 2, 'Turbo')
Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (1, 3, '311')
Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (2, 1, 'ACME')
Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (2, 2, 'TurboPro')
Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (2, 3, '312')
Go

Delete MyInventory
Where InventoryId = 1

Select * from MyInventory
Select * from MyInventoryProperty

Next: Aggregates >>
 

More MS SQL Server Articles
More By McGraw-Hill/Osborne

Comments On: Using Triggers

· This article is an excerpt from the book "Microsoft SQL Server 2005 Stored Procedure...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.