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.
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?
One Solution collect form web for “Using SQLite Trigger to update “LastModified” field”
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_modifiedcolumn 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
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";
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