Try/catch Generate Error & rollback/commit

How to rollback automatically if either one of the transaction failed to commit.

In the table t1 consist of three records and someone intends to delete the third record in the table t1 and also insert duplicate record in the primary column called id. To simulate this process by using store procedure as shown as below.

Create PROCEDURE test   

AS   

begin   

SET NOCOUNT ON;   

BEGIN TRY   

BEGIN TRANSACTION t1    

delete from t1 where id=3 --- this data should be deleted but it will rollback

insert into t1 values('1','faizal')--try to insert duplicate value in primary key column 

COMMIT TRANSACTION t1    

END TRY   

BEGIN CATCH   

 -- Determine if an error occurred.   

 IF @@TRANCOUNT >0 

 ROLLBACK TRANSACTION t1    

-- Return the error information.   

select    

ERROR_MESSAGE() as ErrorMessage,   

ERROR_NUMBER() as ErrorNumber,    

ERROR_LINE() as ErrorLine,   

ERROR_PROCEDURE() as ErrorProcedure,   

ERROR_SEVERITY() as ErrorSeverity   

END CATCH;   

End

Execute Test

After the execution of SP, the table t1 still consist of three records as shown as below

Conclusion

While the T-SQL try to overwrite the duplicate record in the primary id column ,it creates the exception that penetrate in to Catch block and thus the transaction rolled back finally even-though the delete operation succeeds. If @@TRANCOUNT >0 supports transaction rollabck otherwise if @@TRANCOUNT < 0 doesn’t support transaction rollback and this result will be shown as below.