title: Limit SQLite table to have particular number of rows
body: <p>I've Sqlite Handler to save and fetch rows which contains some data. What I want is to limit the number of rows to 10 only at any given moment. Suppose there is already 10 rows added and I'm saving another row, it should delete the oldest row first entered and then insert the latest entry. I searched for it and the only answer I found is by creating a trigger. But there was no explanation of how to do this in SQLite. Is there any better way to do this?</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>public class SQLiteHandler extends SQLiteOpenHelper {
  
      private static final String TAG = "sammy_";
  
      // All Static variables
      // Database Version
      private static final int DATABASE_VERSION = 1;
  
      // Database Name
      private static final String DATABASE_NAME = "android_api";
  
      // Profile Settings table name
      private static final String TABLE_ITEM = "item";
  
      // Profile Settings information names
      private static final String KEY_ID = "id";
      private static final String KEY_TYPE = "type";
      private static final String KEY_TITLE = "title";
      private static final String KEY_SUBTITLE = "subtitle";
      private static final String KEY_APIID = "apiid";
  
  
  
      public SQLiteHandler(Context context) {
          super(context, DATABASE_NAME, null, DATABASE_VERSION);
      }
  
      // Creating Tables
      @Override
      public void onCreate(SQLiteDatabase db) {
  
          String CREATE_PROF_TABLE = "CREATE TABLE " + TABLE_ITEM + "("+KEY_ID+" INTEGER PRIMARY KEY, "+KEY_TYPE+" TEXT, "+KEY_TITLE+" TEXT, "+KEY_SUBTITLE+" TEXT, "+KEY_APIID+" TEXT" + ")";
  
          db.execSQL(CREATE_PROF_TABLE);
  
          System.out.println("sammy_Database tables created");
      }
  
      // Upgrading database
      @Override
      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
          // Drop older table if existed
          db.execSQL("DROP TABLE IF EXISTS " + TABLE_ITEM);
  
          // Create tables again
          onCreate(db);
      }
  
  
      /**
       * Storing Prof_settings details in database
       * */
      public void addContents(String type, String title, String subtitle, String apiid){
  
          SQLiteDatabase db = this.getWritableDatabase();
  
          ContentValues values = new ContentValues();
  
          values.put(KEY_TYPE, type);
          values.put(KEY_TITLE, title);
          values.put(KEY_SUBTITLE, subtitle);
          values.put(KEY_APIID, apiid);
  
          long id = db.insert(TABLE_ITEM, null, values); // insert to 1st row
          db.close(); // Closing database connection
  
          //Log.d(TAG, "New profile settings inserted into sqlite: " + id);
          System.out.println("sammy_New content inserted into sqlite: "+id);
  
      }
  
  
  
  
      /**
       * Getting data from database
       * */
      public ArrayList&lt;HashMap&lt;String, String&gt;&gt; getAllContents()
      {
          ArrayList&lt;HashMap&lt;String, String&gt;&gt; array_list = new ArrayList&lt;HashMap&lt;String, String&gt;&gt;();
  
          SQLiteDatabase db = this.getReadableDatabase();
          //Cursor res = db.rawQuery("SELECT * " + " FROM " + TABLE_PROF + " GROUP BY " + KEY_NAME + " ORDER BY " + KEY_MOBILE + " COLLATE NOCASE;", null);
          String selectQuery = "SELECT  * FROM " + TABLE_ITEM;
          Cursor res = db.rawQuery(selectQuery, null);
          res.moveToFirst();
  
          while (res.isAfterLast() == false)
          {
              HashMap&lt;String, String&gt; hashmap= new HashMap&lt;String, String&gt;();
              hashmap.put("type", res.getString(res.getColumnIndex(KEY_TYPE)));
              hashmap.put("title", res.getString(res.getColumnIndex(KEY_TITLE)));
              hashmap.put("subtitle", res.getString(res.getColumnIndex(KEY_SUBTITLE)));
              hashmap.put("apiid", res.getString(res.getColumnIndex(KEY_APIID)));
  
              array_list.add(hashmap);
              res.moveToNext();
          }
          return array_list;
      }
  
      /**
       * Re create database Delete all tables and create them again
       * */
      public void deleteContents() {
          SQLiteDatabase db = this.getWritableDatabase();
          // Delete All Rows
          db.delete(TABLE_ITEM, null, null);
          db.close();
          System.out.println("sammy_Deleted all contents from sqlite");
          //Log.d(TAG, "Deleted all profile info from sqlite");
      }
  
  }</code></pre>
  </div>
  </div>
  </p>