2

In transact-sql, what is the purpose of enclosing different actions between "begin transaction" and "commit transaction"

I noticed that when an exception is thrown in the middle of the function It Does not roll back the transaction. if an error occurs, How to roll back the transaction?

Martin
  • 745
  • 2
  • 7
  • 23

1 Answers1

2

Best way to have proper error handling using try and catch. Then do actions in catch clause based on the error like roll back tran or etc.

However, if you do not want to change the code at all.Specify following before your code execution.

set xact_abort on

This will cause auto rollback of transaction.But best soltuion will be to catch and handle errors properly.

Gulli Meel
  • 891
  • 4
  • 6
  • Thanks! any idea why it isn't a default behavior in sql server ? Sounds logical that if I am in a transaction, you have to run all or nothing. – Martin Jul 14 '12 at 15:32
  • Yes.It sounds logical to me as well but it is not like that.Maybe due to some historic reason or so. – Gulli Meel Jul 14 '12 at 15:34
  • @Martin because the default behavior is to follow the sql standards. – Hogan Jul 14 '12 at 16:34
  • SSMS 2014 ignored this. My script failed and did not roll back. Cannot run my script again because modified objects are not in the original state. – Alex McMillan Feb 27 '17 at 20:19