You need to rollback the query whenever there is any issue occurs while performing these operations, otherwise you will commit the transaction.
So, go through the articles below to know more.
1.
Maintaining Database Consistency with Transactions[
^].
2.
Managing Transactions in SQL Server Stored Procedures[
^].
Basically, you need to handle Errors, if any, like below, for instance.
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in deleting employees in DeleteDepartment.', 16, 1)
RETURN
END
Thanks...