28

My question is kind of easy but i'm still doubting after I created this transaction. If I execute the following code:

BEGIN TRANSACTION
      DROP TABLE Table_Name

Can I perform a ROLLBACK TRANSACTION that recovers the dropped table? I'm asking because I don't know what happens in the 'Object Explorer' and I didn't found any question of this topic, so I think that it could be a useful issue.

Improve this question
5
  • Why don't you try it out? hint: yes it will work but you have done 99.9% of the testing already. Just try it.
    – Sean Lange
    Commented Jul 18, 2014 at 14:15
  • I think that you are right, but I couldn't find any answer to this topic, and it will help to anyone who faces this problem. I need to delete a few tables, and I think that causes an implicit commit, because the ROLLBACK that I made after the first DROP, doesn't return the table... so that's why I'm asking!
    – Mauro Bilotti
    Commented Jul 18, 2014 at 14:25
  • When it come to this type of thing, don't think it may work. Test it and find out if you are unsure. Any DBMS will behave the same here. This is part of the ACID principal. There is no implicit commit anywhere. If you begin a transaction, there is nothing that will cause an implicit commit.
    – Sean Lange
    Commented Jul 18, 2014 at 14:29
  • Ok Sean, Thanks a lot for your help. Anyway, I still have one more doubt... why I can't perform a SELECT statement after I rollback the operation inside the same tab? Is a kind of weird behaviour because I'm saying 'hey, you, give me back my table!'
    – Mauro Bilotti
    Commented Jul 18, 2014 at 14:32
  • You can. See my example in the answer I posted.
    – Sean Lange
    Commented Jul 18, 2014 at 14:33

3 Answers 3

Reset to default
27

DROP TABLE can be rolled back and it does not auto-commit.

Improve this answer
2
  • 1
    So, why you can't do something like this? BEGIN TRANSACTION DROP TABLE TABLENAME ROLLBACK SELECT * FROM TABLENAME ...If I ROLLBACK the operation, why I can't perform a select statement inside the tab?? Thanks for your answer! =)
    – Mauro Bilotti
    Commented Jul 18, 2014 at 14:28
  • 2
    For anyone else reading you can't in all RDBMs but can in SQL Server which was the OPs DB of choice. MySQL for example: dev.mysql.com/doc/refman/5.7/en/implicit-commit.html
    – Diziet
    Commented May 5, 2017 at 9:25
25

This is incredibly easy to test.

create table TransactionTest
(
    ID int identity primary key clustered,
    SomeValue varchar(20)
)

insert TransactionTest
select 'Here is a row'

begin transaction
    drop table TransactionTest
rollback transaction

select * from TransactionTest
Improve this answer
2
  • 1
    You are right, thank you so much! I swear that I couldn't do the SELECT the first time, but performing this test it works. Again, thanks for your time!
    – Mauro Bilotti
    Commented Jul 18, 2014 at 14:38
  • 2
    Same is the case with Truncate, it can be rolled back.
    – Niraj
    Commented Oct 8, 2015 at 16:48
3

I just want to add that I tried in Oracle 11g, Mysql 5.7 and MSSQL 2016. It only rolled back (worked) with MSSQL RDBMS. I would expect that most other RDBMS won't support it since it execute schema changes.

ORACLE PL/SQL EX:

savepoint mysave;
DROP TABLE test_table;
ROLLBACK TO mysave;
select * from test_table;
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.