Committing Transaction

Question

Hi

http://msdn.microsoft.com/en-us/library/ms189797.aspx

In this link they are committing a transaction within catch clause IF (XACT_STATE()) = 1, I don't get it, if there is an error why they are committing it? even if the problem in select statement and there is no big deal committing it, why don't just roll it back.

Thanks

Answer 1

The link is demonstrating its use, that's all.

Saying that, it may be that in more complex code you want to do a partial commit, for example, rather than rolling back the entire transaction. However, you may not be able to (for example SET XACT_ABORT ON is used as per example)

Answer 2

It's just demonstration code to show that SET XACT_ABORT ON; makes it impossible to commit a transaction where an error occured.

As an example where you might want to commit a transaction after an error, consider logging code. You typically want the log entries to be committed when possible, even if the new order insert resulted in a primary key violation.