If your ultimate goal is to insert row's into another table when a row is inserted/updated/deleted then TRIGGER's could be your answer.
Consider the following :-
DROP TABLE IF EXISTS product_service_category;
DROP TABLE IF EXISTS product_action_log;
DROP TRIGGER IF EXISTS product_service_category_inserted;
DROP TRIGGER IF EXISTS product_service_category_updated;
DROP TRIGGER IF EXISTS product_service_category_deleted;
-- The Main/Original Table
CREATE TABLE IF NOT EXISTS product_service_category
(
_id INTEGER PRIMARY KEY,
product_id INTEGER,
title TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT 0
)
;
-- The Logging table (aka Your Other Table)
CREATE TABLE product_action_log
(
_id INTEGER PRIMARY KEY,
product_service_category_reference INTEGER,
timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
action_taken TEXT
)
;
-- Trigger for logging INSERTS
CREATE TRIGGER IF NOT EXISTS product_service_category_inserted AFTER INSERT ON product_service_category
BEGIN
INSERT INTO product_action_log (product_service_category_reference, action_taken) VALUES(new._id,'INSERTED');
END;
-- Trigger for logging UPDATES
CREATE TRIGGER IF NOT EXISTS product_service_category_updated AFTER UPDATE ON product_service_category
BEGIN
INSERT INTO product_action_log (product_service_category_reference, action_taken) VALUES(new._id,'UPDATED');
END;
-- Trigger for loggin Deletes
CREATE TRIGGER IF NOT EXISTS product_service_category_deleted AFTER DELETE ON product_service_category
BEGIN INSERT INTO product_action_log (product_service_category_reference, action_taken) VALUES(old._id,'DELETED');
END;
-- Add some testing data (4 rows into the main table)
INSERT INTO product_service_category (product_id, title)
VALUES
(1,'Title 1'),(2,'Title 2'),(3,'Title 3'),(4,'and so on')
;
-- Show what the log table looks like (reference data from the main table )
SELECT * FROM product_action_log
JOIN product_service_category ON product_service_category_reference = product_service_category._ID
;
-- Update some data
UPDATE product_service_category SET title = 'changed', updated_at = datetime('now') WHERE _id = 2;
UPDATE product_service_category SET title = 'changed', updated_at = datetime('now') WHERE _id = 4;
-- Show what the log looks like after the updates
SELECT * FROM product_action_log
JOIN product_service_category ON product_service_category_reference = product_service_category._ID;
-- delete a row from the main table
DELETE FROM product_service_category WHERE _id = 1;
-- Show what the data in the log looks like now (note LEFT JOIN)
SELECT * FROM product_action_log
LEFT JOIN product_service_category ON product_service_category_reference = product_service_category._ID;
The first query results in :-

The second query, after some updates, results in :-

The third query, after a deletion, results in :-

- Note that the LEFT JOIN includes the rows that reference the deleted product_service_category (since it's been been deleted the reference to it is null).
The only additional coding required would be the creation of the TRIGGERS and then any queries etc that process the logged data (which would have been on the cards anyway).
Applied to Android
The use of triggers could be implemented as follows (this taking into consideration the data that you want to record, albeit not the SQL used rather the data that could then be used to construct the SQL).
First a basic equivalent of productServiceCategory.java
public class productServiceCategory {
public long id;
public long project_id;
public String title;
public String created_at;
public String updated_at;
public productServiceCategory(long id, long project_id, String title, String created_at, String updated_at) {
this.id = id;
this.project_id = project_id;
this.title = title;
this.created_at = created_at;
this.updated_at = updated_at;
}
}
Second an extended equivalent of ProductServiceCategoryColumn.java with the majority of the changes for the Triggers and the log table, the table have columns for the old and new values :-
public class ProductServiceCategoryColumn {
public class Entry {
public static final String TABLE_PRODUCT_SERVICE_CATEGORY = "product_service_category";
public static final String ID = BaseColumns._ID;
public static final String PROJECT_ID = "project_id";
public static final String TITLE = "title";
public static final String CREATE_AT = "create_at";
public static final String UPDATE_AT = "update_at";
public static final String CREATETABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_PRODUCT_SERVICE_CATEGORY +
"(" +
ID + " INTEGER PRIMARY KEY," +
PROJECT_ID + " INTEGER," +
TITLE + " TEXT," +
CREATE_AT + " TEXT DEFAULT CURRENT_TIMESTAMP," +
UPDATE_AT + " TEXT DEFAULT NULL" +
")";
}
public class Log {
public static final String TABLE_LOG_QUERIES = "tbl_log";
public static final String ID = BaseColumns._ID;
public static final String LOGGED_QUERIES = "logged_queries";
public static final String LOGGED_DATE = "logged_date";
public static final String LOGGED_ACTION = "logged_action";
public static final String OLD_PROJECT_ID = "old_" + Entry.PROJECT_ID;
public static final String NEW_PROJECT_ID = "new_" + Entry.PROJECT_ID;
public static final String OLD_TITLE = "old_" + Entry.TITLE;
public static final String NEW_TITLE = "new_" + Entry.TITLE;
public static final String OLD_CREATE_AT = "old_" + Entry.CREATE_AT;
public static final String NEW_CREATE_AT = "new_" + Entry.CREATE_AT;
public static final String OLD_UPDATE_AT = "old_" + Entry.UPDATE_AT;
public static final String NEW_UPDATE_AT = "new_" + Entry.UPDATE_AT;
public static final String CREATETABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_LOG_QUERIES +
"(" +
ID + " INTEGER PRIMARY KEY," +
LOGGED_QUERIES + " TEXT, " +
LOGGED_DATE + " TEXT, " +
LOGGED_ACTION + " TEXT," +
OLD_PROJECT_ID + " INTEGER," +
NEW_PROJECT_ID + " INTEGER," +
OLD_TITLE + " TEXT," +
NEW_TITLE + " TEXT," +
OLD_CREATE_AT + " TEXT," +
NEW_CREATE_AT + " TEXT, " +
OLD_UPDATE_AT + " TEXT," +
NEW_UPDATE_AT + " TEXT" +
")";
}
public class EventInsertTrigger {
public static final String EVENT_INSERT_TRIGGER = "eventinserttrigger";
public static final String CREATETRIGGER = "CREATE TRIGGER IF NOT EXISTS " + EVENT_INSERT_TRIGGER +
" AFTER INSERT ON " + Entry.TABLE_PRODUCT_SERVICE_CATEGORY +
" BEGIN " +
" INSERT INTO " + Log.TABLE_LOG_QUERIES + " (" +
Log.LOGGED_DATE + "," +
Log.LOGGED_ACTION + "," +
Log.NEW_PROJECT_ID + "," +
Log.NEW_TITLE + "," +
Log.NEW_CREATE_AT + "," +
Log.LOGGED_QUERIES +
") VALUES (" +
"datetime('now')," +
"'INSERT'," +
"new." + Entry.PROJECT_ID + "," +
"new." + Entry.TITLE + "," +
"new." + Entry.CREATE_AT + "," +
"''" +
");" +
" END ";
}
public class EventUpdateTrigger {
public static final String EVENT_UPDATE_TRIGGER = "eventupdatetrigger";
public static final String CREATETRIGGER = "CREATE TRIGGER IF NOT EXISTS " + EVENT_UPDATE_TRIGGER +
" AFTER UPDATE ON " + Entry.TABLE_PRODUCT_SERVICE_CATEGORY +
" BEGIN " +
" INSERT INTO " + Log.TABLE_LOG_QUERIES + " (" +
Log.LOGGED_DATE + "," +
Log.LOGGED_ACTION + "," +
Log.OLD_PROJECT_ID + "," +
Log.NEW_PROJECT_ID + "," +
Log.OLD_TITLE + "," +
Log.NEW_TITLE + "," +
Log.OLD_CREATE_AT + "," +
Log.NEW_CREATE_AT + "," +
Log.OLD_UPDATE_AT + "," +
Log.NEW_UPDATE_AT + "," +
Log.LOGGED_QUERIES +
") VALUES (" +
"datetime('now')," +
"'UPDATE'," +
"old." + Entry.PROJECT_ID + "," +
"new." + Entry.PROJECT_ID + "," +
"old." + Entry.TITLE + "," +
"new." + Entry.TITLE + "," +
"old." + Entry.CREATE_AT + "," +
"new." + Entry.CREATE_AT + "," +
"old." + Entry.UPDATE_AT + "," +
"new." + Entry.UPDATE_AT + "," +
"''" +
");" +
" END ";
}
public class EventDeleteTrigger {
public static final String EVENT_DELETE_TRIGGER = "eventdeletetrigger";
public static final String CREATETRIGGER = "CREATE TRIGGER IF NOT EXISTS " + EVENT_DELETE_TRIGGER +
" AFTER DELETE ON " + Entry.TABLE_PRODUCT_SERVICE_CATEGORY +
" BEGIN " +
" INSERT INTO " + Log.TABLE_LOG_QUERIES + " (" +
Log.LOGGED_DATE + "," +
Log.LOGGED_ACTION + "," +
Log.OLD_PROJECT_ID + "," +
Log.OLD_TITLE + "," +
Log.OLD_CREATE_AT + "," +
Log.OLD_UPDATE_AT + "," +
Log.LOGGED_QUERIES +
") VALUES (" +
"datetime('now')," +
"'DELETE'," +
"old." + Entry.PROJECT_ID + "," +
"old." + Entry.TITLE + "," +
"old." + Entry.CREATE_AT + "," +
"old." + Entry.UPDATE_AT + "," +
"''" +
");" +
" END ";
}
}
Third a database helper DBHelper.java
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "events";
public static final int DBVERSION = 1;
SQLiteDatabase mDB;
public DBHelper(@Nullable Context context) {
super(context, DBNAME, null, DBVERSION);
mDB = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(ProductServiceCategoryColumn.Entry.CREATETABLE);
db.execSQL(ProductServiceCategoryColumn.Log.CREATETABLE);
db.execSQL(ProductServiceCategoryColumn.EventInsertTrigger.CREATETRIGGER);
db.execSQL(ProductServiceCategoryColumn.EventUpdateTrigger.CREATETRIGGER);
db.execSQL(ProductServiceCategoryColumn.EventDeleteTrigger.CREATETRIGGER);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
// --- add addProductServiceCategory ---//
public long addProductServiceCategory(productServiceCategory productServiceCategory){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(ProductServiceCategoryColumn.Entry.ID, productServiceCategory.id);
values.put(ProductServiceCategoryColumn.Entry.PROJECT_ID, productServiceCategory.project_id);
values.put(ProductServiceCategoryColumn.Entry.TITLE, productServiceCategory.title);
values.put(ProductServiceCategoryColumn.Entry.CREATE_AT, productServiceCategory.created_at);
values.put(ProductServiceCategoryColumn.Entry.UPDATE_AT, productServiceCategory.updated_at);
long todo_id = db.insert(ProductServiceCategoryColumn.Entry.TABLE_PRODUCT_SERVICE_CATEGORY, null, values);
return todo_id;
}
public int updateProductServiceCategory(long id, productServiceCategory psc) {
SQLiteDatabase db = this.getWritableDatabase();
String whereclause = ProductServiceCategoryColumn.Entry.ID + "=?";
String[] whereargs = new String[]{String.valueOf(id)};
ContentValues cv = new ContentValues();
cv.put(ProductServiceCategoryColumn.Entry.PROJECT_ID,psc.project_id);
cv.put(ProductServiceCategoryColumn.Entry.TITLE,psc.title);
cv.put(ProductServiceCategoryColumn.Entry.CREATE_AT,psc.created_at);
cv.put(ProductServiceCategoryColumn.Entry.UPDATE_AT,psc.updated_at);
return db.update(ProductServiceCategoryColumn.Entry.TABLE_PRODUCT_SERVICE_CATEGORY,cv,whereclause,whereargs);
}
public int deleteProductServiceCategory(long id) {
SQLiteDatabase db = this.getWritableDatabase();
String whereclause = ProductServiceCategoryColumn.Entry.ID + "=?";
String[] whereargs = new String[]{String.valueOf(id)};
return db.delete(ProductServiceCategoryColumn.Entry.TABLE_PRODUCT_SERVICE_CATEGORY,whereclause,whereargs);
}
}
Last invocation of the inserts, update and delete (designed to run once) from an activity's onCreate :-
public class MainActivity extends AppCompatActivity {
//AdminSQLiteOpenHelper mASQLiteHlpr;
//SQLCon mSQLcon;
DBHelper mDBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDBHlpr = new DBHelper(this);
mDBHlpr.addProductServiceCategory(new productServiceCategory(1,1,"Title1","2018-01-01 10:30", ""));
mDBHlpr.addProductServiceCategory(new productServiceCategory(2,2,"hello title","2018-02-28",""));
mDBHlpr.addProductServiceCategory(new productServiceCategory(3,2,"Something else","2018-03-21",""));
mDBHlpr.addProductServiceCategory(new productServiceCategory(4,1,"A N Other","2017-11-10",""));
mDBHlpr.updateProductServiceCategory(1,new productServiceCategory(1,2,"Changed Title","2018-01-01 10:30","2018-10-16 19:52"));
mDBHlpr.deleteProductServiceCategory(3);
Cursor csr = mDBHlpr.getWritableDatabase().query(ProductServiceCategoryColumn.Log.TABLE_LOG_QUERIES,null,null,null,null,null,null);
DatabaseUtils.dumpCursor(csr);
csr.close();
}
}
Running the above results in the cursor dump of the extract of all rows from the log table :-
10-26 09:41:13.401 3148-3148/axtest.axtest I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534ad948
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: 0 {
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: _id=1
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: logged_queries=
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: logged_date=2018-10-26 09:41:13
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: logged_action=INSERT
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: old_project_id=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: new_project_id=1
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: old_title=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: new_title=Title1
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: old_create_at=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: new_create_at=2018-01-01 10:30
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: old_update_at=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: new_update_at=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: }
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: 1 {
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: _id=2
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: logged_queries=
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: logged_date=2018-10-26 09:41:13
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: logged_action=INSERT
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: old_project_id=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: new_project_id=2
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: old_title=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: new_title=hello title
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: old_create_at=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: new_create_at=2018-02-28
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: old_update_at=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: new_update_at=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: }
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: 2 {
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: _id=3
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: logged_queries=
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: logged_date=2018-10-26 09:41:13
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: logged_action=INSERT
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: old_project_id=null
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: new_project_id=2
10-26 09:41:13.405 3148-3148/axtest.axtest I/System.out: old_title=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_title=Something else
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_create_at=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_create_at=2018-03-21
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_update_at=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_update_at=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: }
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: 3 {
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: _id=4
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: logged_queries=
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: logged_date=2018-10-26 09:41:13
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: logged_action=INSERT
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_project_id=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_project_id=1
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_title=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_title=A N Other
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_create_at=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_create_at=2017-11-10
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_update_at=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_update_at=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: }
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: 4 {
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: _id=5
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: logged_queries=
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: logged_date=2018-10-26 09:41:13
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: logged_action=UPDATE
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_project_id=1
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_project_id=2
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_title=Title1
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_title=Changed Title
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_create_at=2018-01-01 10:30
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_create_at=2018-01-01 10:30
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_update_at=
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_update_at=2018-10-16 19:52
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: }
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: 5 {
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: _id=6
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: logged_queries=
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: logged_date=2018-10-26 09:41:13
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: logged_action=DELETE
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_project_id=2
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_project_id=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_title=Something else
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_title=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_create_at=2018-03-21
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_create_at=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: old_update_at=
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: new_update_at=null
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: }
10-26 09:41:13.409 3148-3148/axtest.axtest I/System.out: <<<<<