Transaction will not rollback

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

 

Answer 1

User-1469158370 posted

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

Answer 2

User871725404 posted

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

Answer 3

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