前言

生活不只有眼前的苟且，还有Ctrl + C 和 V

经常我们会碰到这种需求：

    需要记录一下用户的浏览过哪些商品；或者搜索历史，每次搜索时需要更新搜索历史；...
通常这些需求我们需要先创建一个相关历史的表his_table，在每次insert之前需要检索下这条数据是否已存在。
繁琐的点在于每次insert都需要query，才可以确定接下来的inser or update操作。
甚至更复杂的业务，修改A表的后又需要修改B表的数据，这样的话就需要操作两个表，使我们的代码更加臃肿。
    而数据库触发器就可以解决这类问题，今天就来介绍一下SQLite3数据库触发器在Android的使用。

Show your code！！！

SQLite 触发器

触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行

比方说我们的数据建表语句是这样的

CREATE TABLE db_list_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    item_id INTEGER NOT NULL,
    date TimeStamp DEFAULT (datetime('now','localtime'))
 );

// 插入时间戳yyyy-MM-dd HH:mm:ss，更多请查看文章末尾SQLite时间函数
// 时间函数是基本可以满足常见的格式
date TimeStamp DEFAULT (datetime('now','localtime'))

你以为这样就结束了？(ಡωಡ)(ಡωಡ)

 CREATE TRIGGER auto_remove BEFORE INSERT
    ON db_list_table
    BEGIN
        DELETE FROM db_list_table WHERE NEW.user_id=user_id AND NEW.item_id=item_id;
    END;

这句SQL的意思就是创建了一个触发器，在每次插入数据之前删除和这次插入数据相同的旧数据。下面会分别解释各个字段什么意思。

SQLite 触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行

SQLite仅支持FOR EACH ROW触发器，不支持FOR EACH STATEMENT触发器，所以不用显式的声明FOR EACH ROW
可以指定在特定的数据库表发生 DELETE、INSERT 或 UPDATE 时触发，或在一个或多个指定表的列发生更新时触发
BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作
如果提供 WHEN 语句，则只针对 WHEN 语句为true的指定行执行 SQL 语句。如果没有提供 WHEN 语句，则针对所有行执行 SQL 语句
WHEN 语句和触发器动作都可以使用 NEW.column-name 和 OLD.column-name 来引用当前操作行的value，其中 column-name 是从与触发器关联的表的列名
当触发器相关联的表删除时，自动删除触发器
要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图

创建触发器

我们可以拆解一下这条创建触发器SQL

 CREATE TRIGGER // 创建触发器
 auto_remove    // 触发器名称，后期可以用来查询和移除触发器
 BEFORE         // 在事件之前触发，改为AFTER就是之后触发
 INSERT         // 在插入事件触发，还支持DELETE、UPDATE
 ON db_list_table   // 操作哪个表
    BEGIN   // 触发语句开始
        // 触发语句，删除db_list_table表中和当前插入数据的user_id、item_id相同的数据
        DELETE FROM db_list_table WHERE user_id=NEW.user_id AND item_id=NEW.item_id;// 不要忘了分号
        // 因为触发事件是INSERT，所以表单数据要用NEW.column-name引用；
        // 可能比较绕，你品品，你细品，是不是很有道理(ಡωಡ)
    END;    // 触发语句结束

NEW 和OLD 关键字的英文文档

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", 
where column-name is the name of a column from the table that the trigger is associated with.
OLD and NEW references may only be used in triggers on events for which they are relevant, as follows:

    INSERT	NEW references are valid    // 插入时NEW有效
    UPDATE	NEW and OLD references are valid    // 均有效
    DELETE	OLD references are valid    // 删除时OLD有效
    // 这里的INSERT，UPADATE，DELETE指的是触发动作类型，不是触发语句类型。就是BEFOR/AFTER后面的操作

创建完这个触发器之后每次插入新数据前就会自动检索已存在的数据，存在的话就会删除，然后再插入。这样的话我们就不需要维候插入和更新的逻辑，全部交由触发器自动管理。

当然我们也可以使用触发器更新其他表的数据，比如这里做了一下数据库数据的备份，记录主表所有的插入记录。

// 创建备份表
CREATE TABLE db_list_backup_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    item_id INTEGER NOT NULL,
    date TimeStamp NOT NULL
);
// 创建备份触发器
CREATE TRIGGER back_up AFTER INSERT 
ON db_list_table 
    BEGIN 
        INSERT INTO db_list_backup_table (user_id,item_id,date) VALUES (NEW.user_id,NEW.item_id,NEW.date); 
        // 这里触发动作是INSERT，使用NEW关键字引用value
    END;

插入一些数据看一下备份效果

可以看到数据在db_list_table中是没有重复的，但是db_list_backup_table是有主表的所有插入数据的

大家可能已经发现了，BEGIN 和END中间的触发语句就是一条SQL的删除语句，没错触发语句就是一条普通的删除语句，也可以插入一些默认值（比如更详细的时间戳），甚至使用更复杂的SQL来完成更高级的功能。但是它也有一些限制：

要在UPDATE，DELETE或INSERT 语句中修改的表只支持同一个数据库下的表
不支持UPDATE和DELETE 语句的ORDER BY和LIMIT子句
不支持INSERT语句的'INSERT INTO table_name DEFAULT VALUES'形式

比如，产品经理又加了 ~~一个插入数据时自动删除失效数据~~ 的需求，不慌只需要修改一下触发器。不过触发器不支持修改，只能删除后新建

删除触发器

drop trigger trigger_name // 根据触发器名称删除

CREATE TRIGGER auto_remove BEFORE INSERT
    ON db_list_table
    BEGIN
        DELETE FROM db_list_table WHERE 
            strftime('%s','now') - strftime('%s',date) >= 30 // 30s以前的数据算失效
        OR 
            (NEW.user_id=user_id AND NEW.item_id=item_id);
    END;

// 将当前时间转换为秒数
strftime('%s','now')

查询触发器

// 通过sqlite_master 查询，不是查询我们自己的表明
SELECT name FROM sqlite_master WHERE type = 'trigger';

执行多条SQL时记得使用事务，保证语句的在预期内执行

db.beginTransaction()
try {
    db.execSQL("sql a")
    db.execSQL("sql b")
    // 数据库事务成功
    db.setTransactionSuccessful()
} catch (e: SQLException) {
    e.printStackTrace()
    // todo 异常处理
} finally {
    db.endTransaction()
}

总结

执行多条SQL时记得使用事务，保证语句的在预期内执行（废话）
SQLite Trigger是数据库的回调函数，它会在数据库表发生 DELETE、INSERT 或 UPDATE 时的前后自动执行
触发语句虽然很强大，但是也有相应的限制，并不能支持所有语法
可以使用 NEW.column-name 和 OLD.column-name 来引用当前操作行的value
触发器可以增删查，但是不方便修改。如果表结构修改时，记得迁移触发器

应用截图

项目源码已上传Github

如果有什么错误或者不足的地方，欢迎指正

Github
邮箱 dede.hu@qq.com

End

相关链接

SQLite 触发器文档
 SQLite 时间函数文档

SQLite 时间函数中文文档

SQLite 英文文档