1

I created a table in using SQLite in android like below

query_user_log = "CREATE TABLE " + USER_LOG_TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, USERID TEXT, TIMESTAMP_UP DATETIME DEFAULT CURRENT_TIMESTAMP)";
db.execSQL(query_user_log);

I created a SQLite trigger function for insert a value into USER_LOG_TABLE_NAME once in hour for specific key USERID (not unique) like below.

db.execSQL("DROP TRIGGER IF EXISTS validate");
        db.execSQL(" CREATE TRIGGER  validate BEFORE INSERT ON " + USER_LOG_TABLE_NAME +
                " FOR EACH ROW BEGIN SELECT CASE WHEN (SELECT COUNT(userid) " +
                "FROM " + USER_LOG_TABLE_NAME + " WHERE userid = NEW.userid AND  ((julianday(CURRENT_TIMESTAMP) - julianday(timestamp_up)) * 86400.0)/60 < "+update_time+") > 0" +
                " THEN RAISE(ABORT, 'cannot update') END; END");

here update_time = 60

I got timestamp different from here

but above trigger allow insert not as I expected. can anybody explain my mistake?

Zameel NM
  • 43
  • 8
  • I figured out my mistake. there is no problem with my trigger function. Problem is my another thread deleting all the datas in the USER_LOG_TABLE in every five minutes. – Zameel NM May 23 '20 at 08:05

0 Answers0