locked
Transaction will not rollback RRS feed

  • Question

  • User871725404 posted

    I have this SP and I'm using transaction in it. The problem is that it will be inserted in the first table but not in the second. When I insert in the second tabel there is an error, shouldn't the transaction rollback?

    BEGIN TRAN T1
    INSERT INTO Property(MemberId, PropertyNumber, OldPropertyNumber)
    SELECT ID, Fastnr, Fastnrgl
    FROM DemoMedlem ud
    INSERT INTO MemberProperty(PropertyId, MemberId)
    SELECT PropertyId, MemberId
    FROM Property
    COMMIT TRAN T1


     

    Friday, December 10, 2010 11:38 PM

Answers

  • User-1598917946 posted

    BEGIN TRANSACTION
    INSERT INTO Property(MemberId, PropertyNumber, OldPropertyNumber)
    SELECT ID, Fastnr, Fastnrgl
    FROM DemoMedlem ud
    INSERT INTO MemberProperty(PropertyId, MemberId)
    SELECT PropertyId, MemberId
    FROM Property
     IF(@@ERROR !=0)    
      BEGIN    
       ROLLBACK TRANSACTION    
      END    
      ELSE    
      BEGIN    
       COMMIT TRANSACTION
         END       
    Here try this one you were never checking whether an error has occured

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 10, 2010 11:52 PM

All replies

  • User-1469158370 posted

    Yes the transaction is supposed to rollback. But the problem might be that it will be committing savepoints. 

    Friday, December 10, 2010 11:45 PM
  • User871725404 posted

    how can I solve this? Do I have to look for error and rollback the transaction if there is an error? 

    Friday, December 10, 2010 11:48 PM
  • User-1598917946 posted

    BEGIN TRANSACTION
    INSERT INTO Property(MemberId, PropertyNumber, OldPropertyNumber)
    SELECT ID, Fastnr, Fastnrgl
    FROM DemoMedlem ud
    INSERT INTO MemberProperty(PropertyId, MemberId)
    SELECT PropertyId, MemberId
    FROM Property
     IF(@@ERROR !=0)    
      BEGIN    
       ROLLBACK TRANSACTION    
      END    
      ELSE    
      BEGIN    
       COMMIT TRANSACTION
         END       
    Here try this one you were never checking whether an error has occured

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 10, 2010 11:52 PM