Using SQLite Trigger to update "LastModified" field

Question

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.

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.

Answer 1

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.)