SQL Server : Rollback without BEGIN TRANSACTION
Solution 1
To expand on gerrytans answer when you explicitly set IMPLICIT_TRANSACTIONS ON, you can use a ROLLBACK. See the MSDN doco related to this. Note that this isn't the default autocommit transaction mode.
This allows me to run a statement like;
SET IMPLICIT_TRANSACTIONS ON
INSERT INTO my_table (item_type, start_date_time)
VALUES ('TEST', CURRENT_TIMESTAMP)
ROLLBACK
-- Shouldn't return the 'TEST' value inserted above.
SELECT * FROM my_table ORDER BY start_date_time DESC
Solution 2
As SQL server error tells you -- no you can't. And many people would be curious why would you want that in the first place.
Keep in mind SQL server has an implicit transaction -- that is for DML you issue without explicit BEGIN TRAN, SQL server will start and finish a transaction for you behind the screen.
A common usage of ROLLBACK is for error handling. If somewhere in the middle of the transaction you realize you cannot proceed further due to bad user input or other reason -- then a reasonable action is to ROLLBACK to return to the starting point
The worst thing that can happen is leave your data state 'somewhere in the middle'.
Solution 3
You must have a BEGIN TRANSACTION
before you can use the ROLLBACK
command. You can't go back to the previous state.
user1993412
Updated on June 24, 2022Comments
-
user1993412 almost 2 years
Is there a way we can rollback to previous state of the transaction using ROLLBACK without
BEGIN TRANSACTION
?delete from table1; ROLLBACK
Message:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Any input would be of great help.
Thanks !!!
-
user1993412 over 11 yearsYes , but does sql server supports any form of rollback without BEGIN TRANSACTION .
-
DWright over 11 yearsCould you provide and example or a link to how Oracle supports this?
-
bobs over 11 yearsI think Oracle defaults with a transaction. SQL Server default is to not have a transaction. You must explicitly define the transaction.