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".