SQL timestamp does not change when UPDATE happens

Question

I use the following SQL command to create a products table on an Android client.

CREATE TABLE IF NOT EXISTS 'products' (
  '_id' INTEGER PRIMARY KEY AUTOINCREMENT,
  'name' TEXT,
  'serverId' INTEGER, 
  'modifiedAt' TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  UNIQUE ( 'serverId' ) 
ON CONFLICT REPLACE );

When I load data from a server and insert it into the local database, I use the following commands in the content provider to either update a row or insert new values.

public int bulkInsert(Uri uri, ContentValues[] values) {
    final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
    long rowId;
    int rowsAdded = 0;
    for (ContentValues contentValues : values) {
        int affectedRows = db.update("products", contentValues, 
                "serverId = ?", 
                new String[] { contentValues.getAsString("serverId") });
        if (affectedRows == 0) {
            rowId = db.insert("products", null, contentValues);
            if (rowId > 0) {
                rowsAdded++;
            }
        }
    }
    return rowsAdded;
}

All columns are updated when new values are there, except the column modifiedAt.
Note: The bulk commands are wrapped into a transaction. I left out the code to keep the question simple.

Question:

How can I update the timestamp of the modifiedAt column every time an update happens?

hint: `bulkInsert()` is a good place to wrap the inserts/updates into a transaction. — zapl, Aug 17 '12 at 23:13

score 1 · Answer 1 · answered Aug 17 '12 at 22:52

1

You could let the database handle that by setting up triggers:

Untested...

database.execSQL("CREATE TRIGGER updateLastModifiedDate " +
     "AFTER INSERT ON products FOR EACH ROW BEGIN " +
     "UPDATE products SET modifiedAt = date('now') " +
     "WHERE _id = NEW.id " +
     "END;");

database.execSQL("CREATE TRIGGER updateLastModifiedDate " +
     "AFTER UPDATE ON products FOR EACH ROW BEGIN " +
     "UPDATE products SET modifiedAt = date('now') " +
     "WHERE _id = NEW.id " +
     "END;");

answered Aug 17 '12 at 22:52

jenzz

7,239
6
49
69

you don't need a trigger for inserting since that is done by the default already and time stamp is `datetime('now')` if I'm not mistaken. – zapl Aug 17 '12 at 23:11
1

As @zapl noticed, I should wrap the bulk commands into a transaction. **I do that**, but left the relevant code out, for simplicity of the question. Now, if I run a trigger on each *update*: Would that not destroy the sense of the transaction: I/O performance? – JJD Aug 18 '12 at 00:22
@jdr88 Actually, I experienced **negative performance results using triggers** as I described in the [summary of this question](http://stackoverflow.com/q/11818828/356895). I am happy to hear other opinions from you. – JJD Aug 18 '12 at 12:33
@jdr88 When exactly does the *trigger* execute? After the *transaction* finished for all *updates* that had happened OR within the transaction, right after each *update*? – JJD Aug 18 '12 at 12:37
@JJD triggers are executed as part of the transaction each time a row has to be updated. If you update several rows in a single `UPDATE` you execute the trigger several times before the `UPDATE` is finished. Since triggers add code that has to be executed they obviously have a negative impact on performance but it's usually not much. And regarding transactions you still have all the modifications including triggers written to disk in a single I/O operation. – zapl Aug 18 '12 at 18:22

SQL timestamp does not change when UPDATE happens

1 Answers1

Linked