In order to be able to do a rollback, you need to do a begin transaction at the start.

Then you either commit or rollback the actions.

http://msdn.microsoft.com/en-us/library/ms174377

You may find the try/catch syntax easier

http://msdn.microsoft.com/en-us/library/ms175976(v=sql.90).aspx