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

Question

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

You have committed the transaction immediately after your update, hence why the transaction is no longer available — Steve Ford, Sep 15 '20 at 22:29
where else would I do the commit.... certainly not after the rollback ?? — Hemant Rathi, Sep 17 '20 at 14:43
If you want to rollback the transaction you don't need the commit. It is an either / or. If you want to commit your changes to the DB use Commit, if you want to rollback use rollback. — Steve Ford, Sep 17 '20 at 20:17
This solved the problem, thank you.... but then, why would anyone use COMMIT, seeing that even without that, the database gets updated, and the new values are stored. — Hemant Rathi, Sep 18 '20 at 21:09
If you use a begin tran and don't commit the transaction will be rolled back when you close the query window. https://stackoverflow.com/questions/4896479/what-happens-if-you-dont-commit-a-transaction-to-a-database-say-sql-server. If you don't use the begin tran then the query will be committed — Steve Ford, Sep 19 '20 at 23:10

score 2 · Accepted Answer · answered Sep 17 '20 at 20:20

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

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

1 Answers1