SQLite 触发器

2017-11-21

当具体的表发生特定的数据库事件时，触发器执行对应的 SQL 命令。创建触发器的一般命令如下：

1
2
3

create [temp | temporary] trigger name
[before | after] [insert | delete | update of columns] on table
action

触发器是通过名称、行为和表定义的。行为（或者称为触发体）由一系列 SQL 命令组成，当某些事件发生时，触发器负责启动这些命令。此外，可以通过关键字 before 或 after 来指定这些操作是在事件发生前还是发生后执行。事件包括在具体的表中执行的 delete、insert 和 update 命令。触发器可以用来创建自定义完整性约束、日志改变、更新表和其他很多事情。触发器的作用只限于所写的 SQL 命令。

一：更新触发器

与 insert 和 delete 触发器不同，update 触发器可以在表的执行字段上定义，这种触发器的一般格式如下：

1
2
3

create trigger name
[before | after] update of column on table
action

下面是一个 update 触发器的 SQL 脚本。

CREATE TABLE ACCOUNT (ID BIGINT, NAME text, PASS text, DATE datetime, PRIMARY KEY(ID, NAME));
INSERT INTO ACCOUNT VALUES (10,'Adin','123456','2000-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (11,'Jada','123456','2001-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (12,'Cain','123456','2002-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (13,'Dora','123456','2003-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (14,'Eden','123456','2004-01-01 01:01:01');

CREATE TEMP TABLE LOG(X);

CREATE TEMP TRIGGER ACCOUNT_UPDATE_LOG UPDATE OF PASS ON ACCOUNT
BEGIN
  INSERT INTO LOG VALUES('UPDATED ACCOUNT ' || old.NAME || '’s  PASS : NEW = ' || new.PASS || ' | OLD = ' || old.PASS );
END;

该脚本创建了 ACCOUNT 表、LOG 临时表，以及在表 ACCOUNT 的 PASS 字段上的一个临时的 update 触发器，该触发器执行时会向 LOG 表插入一条消息。

此处输入图片的描述

1
2
3

BEGIN;
UPDATE ACCOUNT SET PASS='666666' WHERE PASS = '123456';
SELECT * FROM LOG;

该行为发生在事务内部，事务的第一步更新 PASS 字段值为 ‘123456’ 的所有行，这将促使 update 触发器执行。当触发器执行时，它向 LOG 表插入对应变更量的记录。事务的下一步读取 LOG 表，显示触发器确实执行了。

此处输入图片的描述

ROLLBACK;

然后事务回滚刚才的改变，当连接结束时，销毁 LOG 表和 update 触发器。

此处输入图片的描述

SQLite 提供在 update 触发器中对未更新行（最初的行）和已更新行的访问。未更新行可以引用为 old，已更新行可引用为 new。注意脚本中的触发器是如何引用 new.PASS 和 old.PASS 的。old 和 new 行的所有属性都可以通过点号来引用，例如 new.DATE 或 old.NAME。

二：错误处理

定义为事件发生前执行的触发器有机会阻止事件的发生，同样，定义为事件发生后执行的触发器可以检查事件，具备重新思考的机会。before 和 after 触发器可以使您具备实施新的完整性约束。SQLite 提供一个特殊的 SQL 函数 raise() 供触发器调用，该函数允许在触发器内产生错误。raise() 定义如下：

1	raise(resolution, error_message);

第一个参数是冲突解决策略（abort、fail、ignore、rollback 等）。第二个参数是错误消息。如果使用 ignore，当前触发器的剩余语句、促使触发器执行的 SQL 命令，以及将被触发执行的触发器，都将中止。如果促使触发器执行的 SQL 语句本身是另一个触发器的组成者，那么该触发器在触发行为的下一个 SQL 命令处继续执行。

三：可更新的视图

触发器使得创建像可更新的视图一类事物成为可能。这里创建一个视图，然后创建负责处理视图上更新事件的触发器。SQLite 支持视图触发器，可以使用 instead of 替代触发器的定义。下面创建表 TYPES、FOODS，然后创建一个连接 TYPES 和 FOODS 的视图：

-- 创建 TYPES 表，插入数据
CREATE TABLE TYPES(ID INTEGER PRIMARY KEY, NAME TEXT);
INSERT INTO TYPES (ID, NAME) VALUES (1, '水果');
INSERT INTO TYPES (ID, NAME) VALUES (2, '蔬菜');
INSERT INTO TYPES (ID, NAME) VALUES (3, '肉类');

此处输入图片的描述

-- 创建 FOODS 表，插入数据
CREATE TABLE FOODS(ID INTEGER PRIMARY KEY, NAME TEXT, TYPE_ID INTEGER);
INSERT INTO FOODS (NAME, TYPE_ID) VALUES ('苹果', 1);
INSERT INTO FOODS (NAME, TYPE_ID) VALUES ('桔子', 1);
INSERT INTO FOODS (NAME, TYPE_ID) VALUES ('西瓜', 1);
INSERT INTO FOODS (NAME, TYPE_ID) VALUES ('芹菜', 2);
INSERT INTO FOODS (NAME, TYPE_ID) VALUES ('黄瓜', 2);
INSERT INTO FOODS (NAME, TYPE_ID) VALUES ('土豆', 2);
INSERT INTO FOODS (NAME, TYPE_ID) VALUES ('牛肉', 3);
INSERT INTO FOODS (NAME, TYPE_ID) VALUES ('猪肉', 3);
INSERT INTO FOODS (NAME, TYPE_ID) VALUES ('鸡肉', 3);

此处输入图片的描述

1 2	-- 创建连接 TYPES 和 FOODS 的视图 CREATE VIEW FOODS_VIEW AS SELECT F.ID FID, F.NAME FNAME, T.ID TID, T.NAME TNAME FROM FOODS F, TYPES T;

此处输入图片的描述

该视图通过外键关系连接两个表。注意，这里为视图中的所有字段创建了别名。这样就可以在触发器内部应用这些字段时区分各自的 ID 和 NAME。现在，通过创建 update 触发器，实现可更新的视图：

-- 创建视图的 update 触发器
CREATE TRIGGER FOODS_VIEW_TRIGGER
INSTEAD OF UPDATE ON FOODS_VIEW
FOR EACH ROW
BEGIN
  UPDATE FOODS SET NAME=NEW.FNAME WHERE ID=NEW.FID;
  UPDATE TYPES SET NAME=NEW.TNAME WHERE ID=NEW.TID;
END;

现在，如果你试图更新 FOODS_VIEW，该触发器将被调用。触发器只是简单地获取 update 语句更新视图时提供的值，使用这些值更新底层的基本表 FOODS 和 TYPES。测试如下：

BEGIN;
UPDATE FOODS_VIEW SET FNAME='芒果', TNAME='鲜果' WHERE FID=1 AND TID=1;
SELECT * FROM FOODS_VIEW WHERE FID=1 AND TID=1;
SELECT * FROM FOODS F, TYPES T WHERE F.TYPE_ID=T.ID AND F.ID=1;
ROLLBACK;

测试过程在事务内进行，逐条执行语句，查看运行结果，全部语句执行完毕后一定要进行回滚，否则无法开启下一个事务进行测试。首先更新视图，将 FOODS_VIEW 视图中字段 FID=1 和 TID=1 的条目的 FNAME 设置为 ‘芒果’，TNAME 设置为 ‘鲜果’，这时你会发现视图和基本表的数据都发生了变化，如下图所示：

此处输入图片的描述