I would like to know what the best way to implement undelete would be on an app that used SQL as its underlying structure for storing data. I ask because in SQL once a delete statement is issued, the data can no longer be recovered. However, imagine an app for holding warehouse items customer and supplier data and more in a list. Your mobile app may be in your pocket and go off accidentally deleting data or you may simply make mistakes when using the user interface.

What is the best way to deal with this problem? You could do away with SQL, but then the application would be slow and lines of code and code complexity would surely increase.

Thanks.

You could make sure that any such action requires confirmation.

You could backup the database on a regular basis or prior to any such actions.

You could introduce logging of such actions that allowed actions to be rolled back to a point in time.

There is no specific best way, as what is most suitable would/could depend upon the complexity/simplicity/efficiency of the App.

In all cases, there would be additional code and complexity.

In short, such considerations should be part of the design.

Additional re comment :-

Could you please show me how I could copy a value from the original
table info the log table with the trigger?

Consider the following :-

DROP TRIGGER IF EXISTS logdelete;
DROP TABLE IF EXISTS main;
DROP TABLE IF EXISTS logtable;
CREATE TABLE IF NOT EXISTS logtable (timestamp TEXT DEFAULT CURRENT_TIMESTAMP, logaction TEXT, val1 TEXT, val2 TEXT, val3 TEXT);
CREATE TABLE IF NOT EXISTS main (id INTEGER PRIMARY KEY, val1 TEXT, val2 TEXT, val3 TEXT);
CREATE TRIGGER IF NOT EXISTS logdelete AFTER DELETE ON main
 BEGIN
 INSERT INTO logtable (logaction, val1, val2, val3) VALUES('DLT',old.val1,old.val2,old.val3);
 END
;
INSERT INTO main (val1,val2,val3) VALUES
 ('A','B','C'),('D','E','F'),('G','H','I'),('J','K','L'),('M','N','O');
SELECT * FROM main;
SELECT * FROM logtable;
DELETE FROM main WHERE val1 IN ('D','J');
SELECT * FROM main;
SELECT * FROM logtable;
INSERT INTO main (val1,val2,val3) SELECT val1,val2,val3 FROM logtable WHERE logaction = 'DLT';
UPDATE logtable SET logaction = 'DLTDONE' WHERE logaction = 'DLT';
SELECT * FROM main;
SELECT * FROM logtable;

This

1. DROPS all tables triggers (for convenience of rerunning), then


2. CREATES the logging table logtable as per the main table BUT with two additional columns for the date and time (timestamp) of the deletion and the action taken.


3. CREATES the main table main, with an ID column (alias of the rowid column) and 3 columns for values namely val1, val2 and val3
   


4. CREATES the TRIGGER for when a row is deleted from the main table that inserts a logtable entry.


5. INSERTS some rows into the main table.


6. SELECTS all rows from the main table and then the logtable (result1 and result2), to show the data prior to any deletions.


7. DELETES some rows (2nd and 4th according to the ID column).


8. SELECTS all rows from the main table and then the logtable (result3 and result4), to show the data after the deletions.


9. ROLLS BACK the deletions made to the main according to the logtable, according to ALL (WHERE clause could use the date for roll back to a time or over a period)


10. UPDATES the logtable (could be a trigger, but done according to the same criteria as the ROLL BACK in this example) to reflect the roll backs.


11. SELECTS all rows from the main table and then the logtable (result5 and result6), to show the data after the roll back.

Results :-

1 main

• after inserting data

2 logtable

• (empty as no deletetions) (image not necessary)

3 main

• after deletions (3 or the original 5 rows remain)

4 logtable

• now has 2 log entries with delete action and deleted data

5 main

• after rollback (deleted rows insert. NOTE new id's though (could set id as per original))

6 logtable

• after rollback (log entries now marked as done)

• Note that the logtable rows being marked as DONE is effectively as per the comment :-

Add another column in the relevant tables to track whether an item is
"deleted". Then, you wouldn't actually delete any records; you'd just
soft delete and undelete by modifying that column's value
appropriately

but potentially allowing the logtable to be UNDELETED.

Android Demo of the above:-

The DatabasHelper DBHelper.java

public class DBHelper extends SQLiteOpenHelper 

 public static final String DBNAME = "mydb";
 public static final int DBVERSION = 1;

 public static final String TBL_MAIN = "main";
 public static final String TBL_LOGTABLE = "logtable";
 public static final String TRG_MAINDELETE = "logdelete";

 public static final String COL_MAIN_ID = BaseColumns._ID;
 public static final String COl_MAIN_VAL1 = "val1";
 public static final String COL_MAIN_VAL2 = "val2";
 public static final String COL_MAIN_VAL3 = "val3";

 public static final String COL_LOGTABLE_TIMESTAMP = "timestamp";
 public static final String COl_LOGTABLE_LOGACTION = "logaction";
 public static final String COL_LOGTABLE_VAL1 = COl_MAIN_VAL1;
 public static final String COL_LOGTABLE_VAL2 = COL_MAIN_VAL2;
 public static final String COL_LOGTABLE_VAL3 = COL_MAIN_VAL3;

 public static final String LOGACTION_DLTDONE = "DLTDONE";
 public static final String LOGACTION_DELETE = "DLT";

 private String main_crtsql = "CREATE TABLE IF NOT EXISTS " + TBL_MAIN + "(" +
 COL_MAIN_ID + " INTEGER PRIMARY KEY," +
 COl_MAIN_VAL1 + " TEXT," +
 COL_MAIN_VAL2 + " TEXT, " +
 COL_MAIN_VAL3 + " TEXT" +
 ")";

 private String logtable_crtsql = "CREATE TABLE IF NOT EXISTS " + TBL_LOGTABLE + "(" +
 COL_LOGTABLE_TIMESTAMP + " TEXT DEFAULT CURRENT_TIMESTAMP, " +
 COl_LOGTABLE_LOGACTION + " TEXT, " +
 COL_LOGTABLE_VAL1 + " TEXT, " +
 COL_LOGTABLE_VAL2 + " TEXT, " +
 COL_LOGTABLE_VAL3 + " TEXT " +
 ")";


 private String logdelete_crtsql = "CREATE TRIGGER IF NOT EXISTS " + TRG_MAINDELETE +
 " AFTER DELETE ON " + TBL_MAIN +
 " BEGIN " +
 "INSERT INTO " + TBL_LOGTABLE + "(" +
 COl_LOGTABLE_LOGACTION + "," +
 COL_LOGTABLE_VAL1 + "," +
 COL_LOGTABLE_VAL2 + "," +
 COL_LOGTABLE_VAL3 +
 ")" +
 " VALUES(" +
 "'DLT'," +
 "old." + COL_LOGTABLE_VAL1 + "," +
 "old." + COL_LOGTABLE_VAL2 + "," +
 "old." + COL_LOGTABLE_VAL3 +
 ")" +
 ";" +
 " END";


 SQLiteDatabase mDB;

 public DBHelper(Context context) 
 super(context, DBNAME, null, DBVERSION);
 mDB = this.getWritableDatabase();
 

 @Override
 public void onCreate(SQLiteDatabase db) 
 db.execSQL(logtable_crtsql);
 db.execSQL(main_crtsql);
 db.execSQL(logdelete_crtsql);
 

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 

 

 public long addMain(String val1, String val2, String val3) 
 ContentValues cv = new ContentValues();
 cv.put(COl_MAIN_VAL1,val1);
 cv.put(COL_MAIN_VAL2,val2);
 cv.put(COL_MAIN_VAL3,val3);
 return mDB.insert(TBL_MAIN,null,cv);
 

 public int deleteMain(String val) 
 String whereclause = COl_MAIN_VAL1 + "=?";
 String[] whereargs = new String[]val;
 return mDB.delete(TBL_MAIN,whereclause,whereargs);
 

 //INSERT INTO main (val1,val2,val3) SELECT val1,val2,val3 FROM logtable WHERE logaction = 'DLT';
 //UPDATE logtable SET logaction = 'DLTDONE' WHERE logaction = 'DLT';
 public void rollbackMain() 
 String columns = " (" + COl_MAIN_VAL1 + "," + COL_MAIN_VAL2 + "," +COL_LOGTABLE_VAL3 + ")";
 String values = " SELECT " + COL_LOGTABLE_VAL1 + "," + COL_LOGTABLE_VAL2 + "," + COL_LOGTABLE_VAL3 +
 " FROM " + TBL_LOGTABLE + " WHERE " + COl_LOGTABLE_LOGACTION + "='" + LOGACTION_DELETE + "'";
 String insertsql = "INSERT INTO " + TBL_MAIN + columns + values;
 mDB.beginTransaction();
 mDB.execSQL(insertsql);
 ContentValues cv = new ContentValues();
 cv.put(COl_LOGTABLE_LOGACTION,LOGACTION_DLTDONE);
 String wherecluase = COl_LOGTABLE_LOGACTION + "=?";
 String[] whereargs = new String[]LOGACTION_DELETE;
 mDB.update(TBL_LOGTABLE,cv,wherecluase,whereargs);
 mDB.setTransactionSuccessful();
 mDB.endTransaction();
 

 public void logtables() 
 Cursor csr = mDB.query(TBL_MAIN,null,null,null,null,null,null);
 DatabaseUtils.dumpCursor(csr);
 csr = mDB.query(TBL_LOGTABLE,null,null,null,null,null,null);
 DatabaseUtils.dumpCursor(csr);
 csr.close();
 

An activity - MainActivity.java

public class MainActivity extends AppCompatActivity 

 DBHelper mDBHlpr;

 @Override
 protected void onCreate(Bundle savedInstanceState) 
 super.onCreate(savedInstanceState);
 setContentView(R.layout.activity_main);
 mDBHlpr = new DBHelper(this);
 // Empty main and logtable
 mDBHlpr.getWritableDatabase().delete(DBHelper.TBL_MAIN,null,null);
 mDBHlpr.getWritableDatabase().delete(DBHelper.TBL_LOGTABLE,null,null);
 // Add some data
 mDBHlpr.addMain("Fred","Banana","Rock");
 mDBHlpr.addMain("Mary","Orange","Scissors");
 mDBHlpr.addMain("Sue","Apple","Paper");
 mDBHlpr.logtables();
 //Delete some data
 mDBHlpr.deleteMain("Mary");
 mDBHlpr.deleteMain("MrNobody");
 mDBHlpr.deleteMain("Sue");
 mDBHlpr.logtables();
 //Rollback
 mDBHlpr.rollbackMain();
 mDBHlpr.logtables();
 

Result

(equivalent to previous results albeit differnt data)

1.

2019-03-27 12:44:37.136 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@7f0608d
2019-03-27 12:44:37.137 I/System.out: 0 
2019-03-27 12:44:37.137 I/System.out: _id=1
2019-03-27 12:44:37.137 I/System.out: val1=Fred
2019-03-27 12:44:37.137 I/System.out: val2=Banana
2019-03-27 12:44:37.137 I/System.out: val3=Rock
2019-03-27 12:44:37.137 I/System.out: 
2019-03-27 12:44:37.137 I/System.out: 1 
2019-03-27 12:44:37.137 I/System.out: _id=2
2019-03-27 12:44:37.137 I/System.out: val1=Mary
2019-03-27 12:44:37.137 I/System.out: val2=Orange
2019-03-27 12:44:37.137 I/System.out: val3=Scissors
2019-03-27 12:44:37.137 I/System.out: 
2019-03-27 12:44:37.137 I/System.out: 2 
2019-03-27 12:44:37.137 I/System.out: _id=3
2019-03-27 12:44:37.137 I/System.out: val1=Sue
2019-03-27 12:44:37.138 I/System.out: val2=Apple
2019-03-27 12:44:37.138 I/System.out: val3=Paper
2019-03-27 12:44:37.138 I/System.out: 
2019-03-27 12:44:37.138 I/System.out: <<<<<

2.

2019-03-27 12:44:37.138 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8d24242
2019-03-27 12:44:37.138 I/System.out: <<<<<

3.

2019-03-27 12:44:37.140 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@2c28253
2019-03-27 12:44:37.140 I/System.out: 0 
2019-03-27 12:44:37.140 I/System.out: _id=1
2019-03-27 12:44:37.141 I/System.out: val1=Fred
2019-03-27 12:44:37.141 I/System.out: val2=Banana
2019-03-27 12:44:37.141 I/System.out: val3=Rock
2019-03-27 12:44:37.141 I/System.out: 
2019-03-27 12:44:37.141 I/System.out: <<<<<

4.

2019-03-27 12:44:37.142 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@fb8f790
2019-03-27 12:44:37.142 I/System.out: 0 
2019-03-27 12:44:37.142 I/System.out: timestamp=2019-03-27 01:44:37
2019-03-27 12:44:37.142 I/System.out: logaction=DLT
2019-03-27 12:44:37.142 I/System.out: val1=Mary
2019-03-27 12:44:37.142 I/System.out: val2=Orange
2019-03-27 12:44:37.142 I/System.out: val3=Scissors
2019-03-27 12:44:37.143 I/System.out: 
2019-03-27 12:44:37.143 I/System.out: 1 
2019-03-27 12:44:37.143 I/System.out: timestamp=2019-03-27 01:44:37
2019-03-27 12:44:37.143 I/System.out: logaction=DLT
2019-03-27 12:44:37.143 I/System.out: val1=Sue
2019-03-27 12:44:37.143 I/System.out: val2=Apple
2019-03-27 12:44:37.143 I/System.out: val3=Paper
2019-03-27 12:44:37.143 I/System.out: 
2019-03-27 12:44:37.144 I/System.out: <<<<<

5.

2019-03-27 12:44:37.145 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@68f7889
2019-03-27 12:44:37.145 I/System.out: 0 
2019-03-27 12:44:37.146 I/System.out: _id=1
2019-03-27 12:44:37.146 I/System.out: val1=Fred
2019-03-27 12:44:37.146 I/System.out: val2=Banana
2019-03-27 12:44:37.146 I/System.out: val3=Rock
2019-03-27 12:44:37.146 I/System.out: 
2019-03-27 12:44:37.146 I/System.out: 1 
2019-03-27 12:44:37.146 I/System.out: _id=2
2019-03-27 12:44:37.146 I/System.out: val1=Mary
2019-03-27 12:44:37.146 I/System.out: val2=Orange
2019-03-27 12:44:37.146 I/System.out: val3=Scissors
2019-03-27 12:44:37.146 I/System.out: 
2019-03-27 12:44:37.146 I/System.out: 2 
2019-03-27 12:44:37.146 I/System.out: _id=3
2019-03-27 12:44:37.146 I/System.out: val1=Sue
2019-03-27 12:44:37.146 I/System.out: val2=Apple
2019-03-27 12:44:37.146 I/System.out: val3=Paper
2019-03-27 12:44:37.146 I/System.out: 
2019-03-27 12:44:37.146 I/System.out: <<<<<

6.

2019-03-27 12:44:37.147 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@4c6408e
2019-03-27 12:44:37.147 I/System.out: 0 
2019-03-27 12:44:37.147 I/System.out: timestamp=2019-03-27 01:44:37
2019-03-27 12:44:37.147 I/System.out: logaction=DLTDONE
2019-03-27 12:44:37.147 I/System.out: val1=Mary
2019-03-27 12:44:37.147 I/System.out: val2=Orange
2019-03-27 12:44:37.147 I/System.out: val3=Scissors
2019-03-27 12:44:37.147 I/System.out: 
2019-03-27 12:44:37.147 I/System.out: 1 
2019-03-27 12:44:37.147 I/System.out: timestamp=2019-03-27 01:44:37
2019-03-27 12:44:37.148 I/System.out: logaction=DLTDONE
2019-03-27 12:44:37.148 I/System.out: val1=Sue
2019-03-27 12:44:37.148 I/System.out: val2=Apple
2019-03-27 12:44:37.148 I/System.out: val3=Paper
2019-03-27 12:44:37.148 I/System.out: 
2019-03-27 12:44:37.148 I/System.out: <<<<<