August 18, 2011 at 5:39 am
I have a four tables and inserting values in all these four table.I want to make sure that if error occur during insert then all table will do the rollback.
I tried like this
Begin Transaction
Insert into table1 values (1,11)
Insert into table1 values (1,11)
Insert into table1 values (1,9999999999999999)
Insert into table1 values (1,88888888888888888)
if @@error <> 0
rollback
commit transaction
but in this case first two values are inserted in my table and they are not rollback.
Any guess?
August 18, 2011 at 6:03 am
Remember:
The @@ERROR function can be used to capture the number of an error generated by the previous Transact-SQL statement. @@ERROR only returns error information immediately after the Transact-SQL statement that generates the error.
In your case modify your code to:
Because @@ERROR gets a new value when every Transact-SQL statement completes, process @@ERROR in one of two ways:
Test or use @@ERROR immediately after the Transact-SQL statement.
Save @@ERROR in an integer variable immediately after the Transact-SQL statement completes. The value of the variable can be used later.
August 18, 2011 at 6:08 am
@@error only checks the error of the last statement, not the entire batch. Rather use Try Catch
http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply