How to Roll Back Insert,Update and Delete Query in Sql Server 2005?

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.

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in deleting employees in DeleteDepartment.', 16, 1)
    RETURN
 END

Thanks...