SQLite学习五、 触发器（Trigger）
SQLite 触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。
触发条件
SQLite 的触发器（Trigger）可以指定在特定的数据库表发生 DELETE、INSERT 或 UPDATE 时触发，或在一个或多个指定表的列发生更新时触发。
特性
SQLite 只支持 FOR EACH ROW 触发器（Trigger），没有 FOR EACH STATEMENT 触发器（Trigger）。
＞for each row 是操作语句每影响到一行的时候就触发一次，也就是删了 10 行就触发 10 次，
而for each statement一条操作语句就触发一次，有时没有被影响的行也执行。
sqlite 只实现了 for each row 的触发。for each row 用法是这样的（when 后接条件）：
另外，我们可以选择在触发前或触发后做记录。
使用触发器
1、新增数据时触发
比如，我们对供应商表中的数据进行监控，当供应商数据增加的时候记录一下
- 创健变化数据的记录表
CREATE TABLE "supplier_data_change" (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`info` TEXT,
`updateTime` TEXT NOT NULL )
- 创建触发器
CREATE TRIGGER supplier_change_trigger
AFTER INSERT
ON supplier_new
BEGIN
insert into supplier_data_change (info,updateTime)
values (-- 记录供应商变化的时间和id
'新增供应商ID='||(select max(id) from supplier_new),
datetime());
END;
触发器创建成果
- 插入一条供应商信息
insert into supplier_new (supplier,supplierAddress,supplierTel,supplierEmail,supplierContact)
values ('杭州有机农副产品经销','文二西路113号','15865432549','organicVegetable@126.com','周琪')
插入成功
-
记录表中的数据
新增供应商的记录
2、修改数据时触发
上面是供应商数据增加时才记录，我们可以再加一个当供应商数据变化的时候也记录，我们在供应商表中增加一个信息更新时间的字段（updateTime），记录插入或更新信息的时间
（如果没有时间做筛选的，我们只能记录对应的表在什么时间做过修改，无法判断是那条记录被修改过，所以为了演示我这里增加了一个updateTime的字段）
-
创建更新的触发器
利用select id from supplier_new order by updateTime desc limit 1来获取最新修改的记录
CREATE TRIGGER supplier_update_trigger
AFTER UPDATE
ON supplier_new
FOR EACH ROW
BEGIN
insert into supplier_data_change (info,updateTime)
values (-- 记录供应商变化的时间和id
'供应商信息变更，ID='||(select id from supplier_new order by updateTime desc limit 1),
datetime());
END;
-
更新数据 不要忘记了更新
updateTime值
update supplier_new
set supplierContact = supplierContact||'_'||id ,updateTime = datetime()
where id = 2
数据更新成功
记录成功
3、删除数据时触发
删除数据的时候，我们记录被删除的供应商名称以及删除的时间
- 创建触发器
CREATE TRIGGER supplier_delete_before_trigger
BEFORE delete
on supplier_new
BEGIN
insert into supplier_data_change (info,updateTime)
values (-- 记录供应商变化
'供应商删除前,供应商ID:'||(select group_concat(id) from supplier_new order by updateTime desc),
datetime());
END;
CREATE TRIGGER supplier_delete_trigger
AFTER delete
ON supplier_new
BEGIN
insert into supplier_data_change (info,updateTime)
values (-- 记录供应商变化
'供应商删除后,供应商ID:'||(select group_concat(id) from supplier_new order by updateTime desc),
datetime());
END;
触发器创建成功
- 删除数据
delete from supplier_new
where id = 3
执行成功
删除成功
- 删除记录成功
记录成功
通过记录信息我们可以看到id=3的供应商是在2018-10-25 12:32:57被删除的。
试一下将上面的 select id from supplier_new order by updateTime desc进行列转行
获取触发器
- 获取数据库中的触发器
SELECT name FROM sqlite_master
WHERE type = 'trigger';
- 获取特定表中的触发器使用 AND 子句连接表名
SELECT name FROM sqlite_master
WHERE type = 'trigger' and tbl_name = 表名;
实际使用
SELECT name FROM sqlite_master
WHERE type = 'trigger' and tbl_name = 'supplier_new';
删除触发器
DROP TRIGGER trigger_name;
实际使用
DROP TRIGGER supplier_change_trigger;
说明
如果你创建触发器的SQL写的不够严谨，可能会操作你正常的插入/更新SQL无法执行
例如，我创建触发器的SQL为：
CREATE TRIGGER supplier_update_trigger
AFTER UPDATE
ON supplier_new
FOR EACH ROW
BEGIN
insert into supplier_data_change (info,updateTime)
values (-- 记录供应商变化的时间和id
'供应商信息变更，ID='||id,
datetime());
END;
我在 supplier_data_change 的插入语句使用了id，这样写是错误（我实际是想取变化的供应商的id）。
执行出错
这里的no such column: id就是你在创建触发器的时候使用的id。
这个id正确的获取方法是select max(id) from supplier_new
