这可能是更多的是设计的问题，但在这里不用。我正在写一个Android应用程序，使用本地SQLite数据库（多表）与一个MySQL数据库同步每一个现在和 - 然后。我只希望在我的数据库更新修改的行。要做到这一点，我添加一列 LAST_MODIFIED 的每一行表示，当该行加时/更新/更换/等。

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.

我是新来的数据库操作，但我已经看到了一个触发器可以做到这一点的最好办法。我与触发器几个问题，SQLite的，和Android。

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.

我读过此链接：上更新CURRENT_TIMESTAMP使用SQLite 该基本上说，我用正确的方法。我的问题是：

I've read this link: on update current_timestamp with SQLite It basically says that I'm using the right approach. My questions are:

我应该在哪里把 db.execSQL（CREATE TRIGGER ...）语句？之前或之后创建的表？ 我可以用同一个触发器为每个表在我的数据库？也就是说，可这表和行正在更新/插入/替换的/ etc触发自动检测。并通知设置该行的 LAST_MODIFIED 字段，或者我必须为每个表单独触发？ 由于我是相当新的数据库操作，你能提供执行上述行为的一个例子Android的触发声明，或提供资源的例子吗？ 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?

感谢你。

推荐答案

为你一个简短而亲切的回答：

A short and sweet answer for you:

在，所以触发器有一个有效的表引用。 您需要执行一个CREATE TRIGGER您要影响到每个表/列的组合。该数据库不会的假设的，因为另一个表中有一个 LAST_MODIFIED 列，你想这一块有同样的表现... 在您的链接触发是可执行文件（我用它自己），只是改变了表/列名。 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.

最后，使用这样的触发器是的最简单的的办法，我知道，以保持 LAST_MODIFIED 或 last_accessed 时间戳。

Lastly, using a trigger like this is the easiest way I know to maintain last_modified or last_accessed timestamp.

我的触发器（在Java形式）：

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";

添加

按照 SQLite的网站您需要为每种类型的动作触发。换句话说，您不可以使用：

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

这是你最后的评论，你可能已经想通了，来处理我们的目的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);

在此表中，你并不需要创建一个时间戳触发INSERT语句，因为它已经完成。 （有趣的事实： INTEGER PRIMARY KEY 隐式添加 AUTOINCREMENT NOT NULL 以及默认增量价值，我们的 _id 列。）

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