Begin Tran, Commit Tran

Question

 

Hi,

 

I want to rollback my t-sql if it encounters an error. I wrote this code:

 

begin tran mytrans;

insert into table1 values (1, 'test');

insert into table1 values (1, 'jsaureouwrolsjflseorwurw');   -- it will encounter error here since max value to be inputted is 10

commit tran mytrans;

 

I forced my insert to have an error by putting a value that exceeds the data size. However, I didn't do any rollback. Anything i missed out?

 

cherriesh

Answer 1

Use the following code :

Code Block

set xact_abort on;

go

begin tran mytrans;

insert into table1 values (1, 'test');

insert into table1 values (1, 'jsaureouwrolsjflseorwurw');   -- it will encounter error here since max value to be inputted is 10

commit tran mytrans;

go

 

 

Now your whole transaction is rolled back.

More info, refer Books Online, SET XACT_ABORT ON.

 

Thanks

Naras.

 

Answer 2

is it much better compared to rollback trans?

 

Answer 3

Check out this article on error handling. It might be useful:

 

http://www.sommarskog.se/error-handling-I.html

HTH!