Using SQLite Trigger to update "LastModified" field

android, sqlite, triggers

Solution

A short and sweet answer for you:

- After, so the trigger has a valid table to reference.

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

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

Problem

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: - Where should I put the `db.execSQL("CREATE TRIGGER...")` statement? Before or after I create the tables? - 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? - 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.

Original source

Related problems