There are some situaties for wich the cascade action is very handy. I have an Order table and the belonging OrderRules table. If I delete an OrderRule nothing needs to happen with the order in the Order table. But if I delete an Order all belonging rules in the OrderRules must be deleted.
So, there is choise to handle this in code, it's possible and not diffecult to implement. But the better choise is to implement a foreign key constraint with a cascade delete action.
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_OrderType] FOREIGN KEY([OrderId]) REFERENCES [dbo].[OrderType] ([OrderId]) ON DELETE cascade