CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
user1993412
user1993412

Reputation: 862

SQL Server : Rollback without BEGIN TRANSACTION

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 !!!

Upvotes: 6

Views: 23943

Answers (4)

Hugo Legua
Hugo Legua

Reputation: 5

The best option, not to suffer, put BEGIN TRAN QUERY

In that case it is not necessary SET IMPLICIT_TRANSACTIONS ON

Upvotes: -1

Mr Moose
Mr Moose

Reputation: 6344

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

Upvotes: 6

gerrytan
gerrytan

Reputation: 41123

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'.

Upvotes: 4

bobs
bobs

Reputation: 22184

You must have a BEGIN TRANSACTION before you can use the ROLLBACK command. You can't go back to the previous state.

Upvotes: 3

Related Questions