当UPDATE发生SQL时间戳不会改变 [英] SQL timestamp does not change when UPDATE happens
问题描述
我用下面的SQL命令来创建的产品的一款Android客户端上桌子。
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;
}
在新的价值都在那里,所有的列被更新,除非该列的 modifiedAt 的。结果
注:批量命令裹成一个事务。我离开了code,以保持这个问题很简单。
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.
问:
我如何更新的时间戳的 modifiedAt 的柱每次的更新的情况?
How can I update the timestamp of the modifiedAt column every time an update happens?
推荐答案
您可以让数据库处理,通过设置触发器:
You could let the database handle that by setting up triggers:
未经测试...
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;");
这篇关于当UPDATE发生SQL时间戳不会改变的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!