Is it possible to rollback DELETE, DROP and TRUNCATE?

Question

We can rollback a delete query but not so for truncate and drop. When I execute queries then successfully done with rollback in delete, drop & truncate.

We can rollback the data in conditions of Delete, Truncate & Drop. But must be used Begin Transaction before executing query Delete, Drop & Truncate.

Here is example:

Create Database Ankit

Create Table Tbl_Ankit(Name varchar(11))

insert into tbl_ankit(name) values('ankit');
insert into tbl_ankit(name) values('ankur');
insert into tbl_ankit(name) values('arti');

Select * From Tbl_Ankit

/*======================For Delete==================*/
Begin Transaction
Delete From Tbl_Ankit where Name='ankit'

Rollback
Select * From Tbl_Ankit

/*======================For Truncate==================*/
Begin Transaction
Truncate Table Tbl_Ankit 

Rollback
Select * From Tbl_Ankit

/*======================For Drop==================*/
Begin Transaction
Drop Table Tbl_Ankit 

Rollback
Select * From Tbl_Ankit

Answer 1

For MySql:

13.3.2 Statements That Cannot Be Rolled Back

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement.

https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html

Answer 2

All of the above 3 transactions can be rolled back because all of them generate detail log. See this SO answer for more information. And this blog for detailed explanation with examples.

Answer 3

DELETE(DML)------------------- Used to delete the tuples(row) of the table and have facility to DELETE ONE OR MULTIPLE ROW USING WHERE CLAUSE delete EX ->  delete from emp ; it will delete all the row of table  delete from emp where id = 2; it will delete one row ----------------------Drop(DDL)------------------- This command will delete the whole structure of table with data so that's why it is very important or crucial command and the access is to this command is only to database administrator (DBA) dropEX -> DROP EMP1; it will vanish whole table with structure -------------------TRUNCATE(DDL)---------------- Used to delete all rows (tuple) of the table. truncate EX -> TRUNCATE EMP1; ----------------------QUESTIONS------------------- QUES 1: IF WE CAN DELETE ALL ROWS FROM BOTH 'DELETE' COMMAND AND 'TRUNCATE' COMMAND THEN WHAT IS THE DIFFERENCE DELETE AND TRUNCATE DIFFERENCE

1. DELETE command is DML command and TRUNCATE is DDL
2. TRUNCTE command will delete all rows because we can not use where clause with truncate but WHERE clause can be applied to DELETE command and can delete one or more rows through where condition QUES 2: WHY TRUNCATE IS DDL ALTHOUGH IT IS WORKING ON DATA AND DELETE IS DML IT IS ALSO WORKING ON DATA? ANSWER:  When TRUNCATE Command deletes all the row then we can never ROLLBACK our deleted data because in truncate COMMIT is called implicitly  But in the case of delete the log(backend file) is created from where you can get your data ROLLBACK if deleted by mistake  REMEMBER: In DELETE case ROLLBACK can be done only before the COMMIT