SQLite3附加数据库和触发器

本文介绍如何在当前数据库中调用外部数据库文件，以及如何通过触发器在表数据发生变动后自动完成指定的操作。

附加数据库

SQLite3中，附加数据库的功能就是在当前数据库中，可以调用其它数据库。附加数据库时使用如下语法：

SQLite3

attach database '<文件名>' as <数据库名>;

其中，<文件名>使用文本形式指定数据库文件的路径。<数据库名>则指定数据库的名称。

释放附加数据库时使用如下语句：

SQLite3

detach database <数据库名>;

首先，在D:盘下创建cdb_test1.db数据库文件，并通过如下语句创建tt1表和数据。

SQLite3

create table tt1 (
f1 text not null unique,
f2 integer default 0,
f3 text);

insert into tt1(f1,f2,f3)
values('user-a',1,datetime('now','localtime')),
('user-b',2,datetime('now','localtime')),
('user-c',0,datetime('now','localtime'));

接下来，保存并关闭d:\cdb_test1.db数据库；然后，打开常用的测试数据库。通过下面的代码查询d:\cdb_test1.db数据库中tt1表的数据。

SQLite3

attach database 'd:\cdb_test1.db' as db1;
select * from db1.tt1;
detach database db1;

查询结果如下图所示。

查询结果

本例，首先使用attact database语句将d:\cdb_test1.db数据库附加到当前数据库，并指定数据库名称为db1；接下来，通过“<数据库名>.<表名>”格式调用附加数据库中的表；最后，使用detach database语句释放db1数据库。

触发器

触发器可以定义表数据发生变动后的自动操作。创建触发器的基本语法如下：

SQLite3

create trigger <触发器名> <before或after> <操作> on <表> for each row 
begin
<语句>
end;

其中：

<触发器名>指定触发器在系统中的名称，可以通过sqlite_master系统表查询。
<before或after>指定触发器是在操作之前还是之后执行。
<操作>包括insert、update、delete，即在添加、修改和删除数据之前（before）或之后（after）触发操作。
<表>指定触发器所在的表。
<语句>定义触发器执行的操作。

下面的代码会创建t1表和t1_bak表，t1表为原始数据表，t1_bak表用于保存t1表数据的变动记录。

SQLite3

create table t1(f1 text not null unique,f2 integer default 0,f3 text,f4 text);
create table t1_bak(recid integer,f1 text,f2 integer,f3 text,f4 text,opt_type text);

其中，t1_bak表中，recid字段保存t1表的rowid值，opt_type字段保存操作类型；实际应用中，还可以保存操作用户、操作时间和操作IP地址等信息。

下面的代码，在t1表中创建t1_ins_bak触发器，其功能是将新添加记录的rowid、f1、f2、f3、f4字段数据同步保存到t1_bak表中。

SQLite3

create trigger t1_ins_bak after insert on t1 for each row
begin
insert into t1_bak(recid,f1,f2,f3,f4,opt_type)
values(new.rowid,new.f1,new.f2,new.f3,new.f4,'ins');
end;

触发器执行语句中，使用new关键字调用新数据字段，使用old关键字调用修改前的数据字段。

下面的代码会在t1表中添加一条记录。

SQLite3

insert into t1(f1,f2,f3,f4)
values('user01',1,datetime('now','localtime'),'111');

下面的代码可以查看t1_bak表中是否对同步添加了新数据记录。

SQLite3

select * from t1_bak;

查询结果如下图所示。

查询结果

下面的代码会在t1表中创建t1_upd_bak触发器，其功能是在t1_bak表中同时保存修改前和修改后的记录。

SQLite3

create trigger t1_upd_bak after update on t1 for each row
begin
insert into t1_bak(recid,f1,f2,f3,f4,opt_type)
values(old.rowid,old.f1,old.f2,old.f3,old.f4,'upd');
insert into t1_bak(recid,f1,f2,f3,f4,opt_type)
values(new.rowid,new.f1,new.f2,new.f3,new.f4,'upd');
end;

下面的代码，将t1表中f1等于user01的记录中的f1修改为'user03'，f2修改为2，f4修改为'333'。

SQLite3

update t1 set f1='user03',f2=2,f4='333' where f1='user01';

执行操作后可以查看t1_bak表的记录，如下图所示。这里，可以根据t1_bak表中的recid字段数据，即t1表rowid字段数据完整跟踪记录数据的变化，而t1_bak表中的rowid字段数据则显示了操作的顺序。

触发器同步数据

下面的代码会创建t1_del_bak触发器，其功能是在删除记录之前，将原数据保存到t1_bak表中。

SQLite3

create trigger t1_del_bak before delete on t1 for each row
begin
insert into t1_bak(recid,f1,f2,f3,f4,opt_type)
values(old.rowid,old.f1,old.f2,old.f3,old.f4,'del');
end;

下面的代码会从t1表中删除f1等于user03的记录。

SQLite3

delete from t1 where f1='user03';

执行操作后，可以查看t1_bak表中的数据如下图所示。

查询记录

此外，删除触发器时可以使用“drop trigger <触发器名>”语句。