Nested Transaction -How it is important.

 Recently I was  encountered with issue  ,it was related with Transaction .I can share code with respect to adventurework database
Script:

SELECT Count(*)
FROM   SALESLT.SalesOrderDetail
WHERE  SalesOrderID IN ( 71774, 71780 )

BEGIN TRY
--Added try catch to avoid exception
    BEGIN TRAN
--First Transaction
    BEGIN TRAN
--Second Transaction
    BEGIN TRAN
--Third Transaction

    UPDATE SALESLT.SalesOrderDetail
    SET    UnitPrice = 5000
    WHERE  SalesOrderID = 71774

    COMMIT -- First Transaction End

    UPDATE SALESLT.SalesOrderDetail
    SET    UnitPrice = 1000
    WHERE  SalesOrderID = 71780

    COMMIT -- Second Transaction End

    UPDATE SALESLT.SalesOrderDetail
    SET    UnitPrice = 1000
    WHERE  SalesOrderID = 71781
--No Entry in Table So it will raise manual error

    IF @@ROWCOUNT <= 1
      RAISERROR('Call Rollback',16,1)
--it will go to Catch block

    COMMIT
END try

BEGIN catch
    PRINT 'Call Rollback'

    ROLLBACK
--Data will be rollback
END Catch

SELECT Count(*)
FROM   SALESLT.SalesOrderDetail
WHERE  SalesOrderID IN ( 71774, 71780 )

   Result of both query will be same as all transaction are rollback.
  Many of us can confuse with respect to result.




Summary:
   It has total  three transaction  out of which two transaction succeed  and one is failed .  Though inner transaction are committed without error but outer transaction got rollback..So all data got rollback. While implementing transaction  if it is required we need take care of this case.