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.

  • Android Studio + Volley = NoClassDefFound?
  • I can't see Android SDK javadoc in Eclipse
  • Duplicate files when adding compiling with Gradle in Android Studio
  • How to access html content of AccessibilityNodeInfo of a WebView element using Accessibility Service in Android?
  • MonoDroid: Unhandled Exception Recovery
  • Using color and color.darker in Android?
  • 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:

    Genymotion emulators giving Open GL error on Kubuntu 13.10 64-bit
    What is a binder callback on Android?
    How to automatically detect the need for iScroll?
    GoogleApiClient.connect()' was expected to be of type interface but was found to be virtual
    How to set position in spinner?
    CordovaActivity cannot be resolved to a type in Phonegap 3.4
  • Import .csv file to Sqlite in Android
  • How to determine if lat long coordinates are on a road with Google Maps API v2
  • How to pass a variable from Activity to Fragment, and pass it back?
  • How to place buttons over Image in android?
  • runOnUiThread(new Runnable(){}) is undefined for the type new Thread(){}
  • What does Watchdog !@Sync error indicate?
  • 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" + 
        "  AFTER UPDATE ON " + TABLE_NAME + " FOR EACH ROW" +
        "  BEGIN " +
            "UPDATE " + TABLE_NAME + 
            "  SET " + TIME + " = current_timestamp" +
            "  WHERE " + ID + " = old." + ID + ";" +
        "  END";
    

    Addition

    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 
      AFTER UPDATE, INSERT ...
    

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

    CREATE TABLE foo (
      _id INTEGER PRIMARY KEY,
      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.