I have an Insert script which insert value into two table. I need to put the entire insert script under a single transaction and if the the script fails to insert data in any of the table then the whole transaction should be rollbacked.
Can any one please help. I am trying with the below:
Begin tran Begin Insert into A (a) values (1) Insert into B (b) values (2) Insert into B (b) values (3) --suppose fails in this step then the entire traction should be rolled back Insert into B (b) values (4) Insert into B (b) values (5) END IF (@@ERROR<>0) Rollback Tran
I understand one thing now @@ERROR always return the status of the last insert statement. So will it be work if i put the entire thing under a try catch block?
Got my answer :) need to put the whole thing in try block and rollback section in catch block. i.e: begin try Begin tran Begin Insert into A (a) values (1) Insert into B (b) values (2) Insert into B (b) values (3) --suppose fails in this step then the entire traction should be rolled back Insert into B (b) values (4) Insert into B (b) values (5) commit tran END end try begin catch Rollback Tran end catch
I tried and It is working fine.It rollback all the records..
Begin tran Begin
Insert into B (Val) values (2) Insert into B (Val) values ('vava') --suppose fails in this step then the entire traction should be rolled back Insert into B (Val) values (4) Insert into B (Val) values (5) END IF (@@ERROR<>0) Rollback Tran
Rollbck Transaction
Posted - 05/18/2012 : 07:00:39
