Using SQLite Trigger to update “LastModified” field

This might be more of a design question, but here goes. I’m writing an Android app that uses a local SQLite database (with multiple tables) that syncs with a MySQL database every now-and-then. I only want to update modified rows in my database. To do this, I’m adding a column “last_modified” to each row that indicates the time when that row was added/updated/replaced/etc.

I’m new to database operations, but I’ve seen that a Trigger might be the best way to do this. I have a couple questions relating to Triggers, SQLite, and Android.

  • Is there an Android GCM equivalent to the iOS Push Notification Feedback Service?
  • Setting Ellipsize on TextView reduces lines shown by one (instead of only ellipsizing last)
  • How to hide Soft Keyboard when activity starts
  • Gradle resValue causes duplicate string resources
  • How to call main activity's function from custom ArrayAdapter?
  • Does hard coding of string affect performance?
  • I’ve read this link: on update current_timestamp with SQLite It basically says that I’m using the right approach. My questions are:

    1. Where should I put the db.execSQL("CREATE TRIGGER...") statement? Before or after I create the tables?
    2. Can I use the same Trigger for every table in my database? i.e, can the Trigger automatically detect which table and row is being updated/inserted/replaced/etc. and notify to set that row’s “last_modified” field, or do I have to create a separate Trigger for each table?
    3. Since I’m quite new to database operations, could you provide an example Android Trigger statement that performs the above behavior, or provide a resource to an example?

    Or if Triggers are a bad idea, are there any better alternatives?

    Thank you.

    Related posts:

    How to Create a circular progressbar in Android which rotates on it?
    Don't see Android SDK and AVD Manager when execute android tool command
    Converting Timestamp as String to Date in android
    How to set HttpResponse timeout for Android in Java
    Permanently hide Android Status Bar
    Unable to resolve imports from external dependencies in Android Studio 2.0 Preview 5 + Preview 6
  • error: Error parsing XML: not well-formed (invalid token) …?
  • iScroll with native scrolling on one axis
  • How to use Android Studio with Cocos2d-x
  • How to close Android application?
  • Android Studio rendering problems
  • Is “AndroidAnnotaions” reliable?
  • One Solution collect form web for “Using SQLite Trigger to update “LastModified” field”

    A short and sweet answer for you:

    1. After, so the trigger has a valid table to reference.
    2. You need to execute a CREATE TRIGGER for every table / column combination you want affected. The database won’t assume because another table has a last_modified column that you want this one to behave the same…
    3. The trigger in your link is executable (I used it myself), just change the table / column names.

    Lastly, using a trigger like this is the easiest way I know to maintain last_modified or last_accessed timestamp.

    My trigger (in java form):

    private static final String UPDATE_TIME_TRIGGER =
        "CREATE TRIGGER update_time_trigger" + 
        "  BEGIN " +
            "UPDATE " + TABLE_NAME + 
            "  SET " + TIME + " = current_timestamp" +
            "  WHERE " + ID + " = old." + ID + ";" +
        "  END";


    According to the SQLite website you need to create a trigger for each type of action. In other words, you cannot use:

    CREATE TRIGGER trigger_name 

    From your last comment you may have figured out the best way to handle an INSERT statement for our purpose:

    CREATE TABLE foo (
      last_modified TIMESTAMP NOT NULL DEFAULT current_timstamp);

    In this table, you do not need to create a timestamp trigger for an INSERT statement, since it is done already. (Fun fact: INTEGER PRIMARY KEY implicitly adds AUTOINCREMENT NOT NULL as well as the default incremental value to our _id column.)

    Android Babe is a Google Android Fan, All about Android Phones, Android Wear, Android Dev and Android Games Apps and so on.