Search code examples
sqlsql-servertransactionsrollback

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION, but still getting an error

Simple query: Before the UPDATE, the value of EffectiveStartDt is NULL After the UPDATE and the ROLLBACK, I want it to go back to NULL

begin tran t1
update MasterPayor 
set EffectiveStartDt = '2020-09-17' 
where MasterPayorId = 1
commit

rollback tran t1

I have defined the transactions, but I'm still getting the Rollback error

Solution

  • The COMMIT commits the update to the Database and clears the transaction begun with the matching BEGIN TRAN, hence the error when you try to rollback. If you want to ROLLBACK use the ROLLBACK without the COMMIT:

    begin tran t1
update MasterPayor 
set EffectiveStartDt = '2020-09-17' 
where MasterPayorId = 1

rollback tran t1