September 5, 2012 at 10:48 pm
I've recently inherited a database that I've been working on for the last 6 months or so. I'm also studying for the MCTS SQL Developer Exam and I noticed that some of the examples of error handling in the certification book I'm reading differ from what's used throughout the database I work on. The book has the TRAN / TRY blocks in the following order:
BEGIN TRY
BEGIN TRAN
COMMIT TRAN
END TRY
BEGIN CATCH
RAISERROR()
ROLLBACK TRAN
END CATCH
The code in the database I work on is structured like so:
BEGIN TRAN
BEGIN TRY
END TRY
BEGIN CATCH
RAISERROR()
ROLLBACK TRAN
END CATCH
COMMIT TRAN
My question would be... Are both ways correct? Are there any advantages to one over the other?
Thanks!
September 6, 2012 at 2:25 am
Hi,
Every BEGIN TRANSACTION must end up with COMMIT TRANSACTION or ROLLBACK TRANSACTION
I’ll try to insert something that causes primary key constraint violation (locally on my machine).
The first approach:
BEGIN TRY
BEGIN TRAN
insert into tbl values(2,'string')
COMMIT TRAN
END TRY
BEGIN CATCH
RAISERROR('Primary key constraint violation. Cannot insert this record.',16,1)
ROLLBACK TRAN
END CATCH
Output is:
Msg 50000, Level 16, State 1, Line 7
Primary key constraint violation. Cannot insert this record.
Everyting is OK.
Second approach:
BEGIN TRAN
BEGIN TRY
insert into tbl values(2,'string')
END TRY
BEGIN CATCH
RAISERROR('Primary key constraint violation. Cannot insert this record.',16,1)
ROLLBACK TRAN
END CATCH
COMMIT TRAN
Output is:
Msg 50000, Level 16, State 1, Line 6
Primary key constraint violation. Cannot insert this record.
Msg 3902, Level 16, State 1, Line 9
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
The transaction ended up within the CATCH block with ROLLBACK, but the COMMIT TRAN expects a BEGIN TRANSACTION which in this case finished.
So the first approach is better option.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply