How to Create a Cascading Delete Trigger

To avoid errors when deleting a record that is referenced by other records in the same or other tables, it is most convenient to create a Cascading Delete trigger.

Added on 29 Jul 2008

General Solutions:

Solution Summary:

Delete all records referencing the record you want to delete in a simple Trigger.

Solution Details:

Whenever you add a foreign key that references another record in the same or another table, you can add it to your delete trigger to avoid referencial errors.

You can either change the reference to another existing record or delete the record entirely. In the following example Categories might have children categories; in which case we want to delete all children; however, the existing assets that reference the category to be deleted should remain in the database, but be "moved" to the default Category (in this case with an ID of 1).

Each of the subcategories will be deleted first, and if they have their own subcategories, they too will be deleted. Hence, the reason it's called a "cascading delete trigger".

CREATE TRIGGER [dbo].[Categories_Delete_Trigger] ON  [dbo].[app_Categories]
INSTEAD OF DELETE
AS
BEGIN

     SET NOCOUNT ON;
      -- Don't delete the related asset, but instead relate it to the default category.
     UPDATE [app_Assets] SET CategoryId = 1 FROM deleted, [app_Assets] WHERE [app_Assets].CategoryId = deleted.CategoryId
      -- Delete child categories
     DELETE [app_Categories] FROM deleted, [app_Categories] WHERE [app_Categories].ParentId = deleted.CategoryId
      -- Then delete the actual category
     DELETE [app_Categories] FROM deleted, [app_Categories] WHERE [app_Categories].CategoryId = deleted.CategoryId

END

Was this solution useful? Yes No

Added on 29 Jul 2008

Rating: