none
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

    Monday, October 08, 2007 11:09 PM

Answers

  • 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.

     

    Tuesday, October 09, 2007 3:31 AM

All replies

  • 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.

     

    Tuesday, October 09, 2007 3:31 AM
  • is it much better compared to rollback trans?

     

    Tuesday, October 09, 2007 4:24 AM
  • Check out this article on error handling. It might be useful:

     

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


    HTH!

    Tuesday, October 09, 2007 9:15 AM