SQLite的触发器的例子2_sqlite 触发器写文件-优快云博客

本例的主要内容：我们要得到的数据需要由两个文件拼接而成。文件1存储在data_part1里面，文件2存储在data_part2里面，目标数据存储在data_all里面。当我们往data_part表里面塞数据时，会触发相应的触发器，这些触发器会将数据塞进data_all表里面，从而自动实现数据的拼接。在拼接的过程中，触发器还会往log_info里面写log，从而让我们知道触发器所做的动作。

下面是SQL语句：

CREATE TABLE IF NOT EXISTS data_part1(
exchange   VARCHAR(32),
code       VARCHAR(32),
name       VARCHAR(32),
group_id   VARCHAR(32),
PRIMARY KEY(exchange, code)
);

CREATE TABLE IF NOT EXISTS data_part2(
exchange   VARCHAR(32),
code       VARCHAR(32),
price_tick FLOAT,
PRIMARY KEY(exchange, code)
);

CREATE TABLE IF NOT EXISTS data_all(
exchange   VARCHAR(32),
code       VARCHAR(32),
name       VARCHAR(32),
group_id   VARCHAR(32),
price_tick FLOAT,
PRIMARY KEY(exchange, code)
);

--创建log_info表。
CREATE TABLE IF NOT EXISTS log_info(
idx     INTEGER   PRIMARY KEY, --A column declared INTEGER PRIMARY KEY will autoincrement.
dttm    TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'now', 'localtime')),
summary TEXT NOT NULL, --摘要
details TEXT     NULL  --详情
);

--我自己定义的触发器名字的通用格式：t_(表名/视图名)_(before/after)_(insert/update/delete)_自定义字符串。
--写这个触发器，主要是想表示：如果不用 LIMIT 的话，得用 NOT IN 关键字才行。
--这个触发器和下面的一个触发器的作用相同。他们俩创建一个就好了。如果都创建了，那么在看log时，反而会有些疑惑。
CREATE TRIGGER t_data_part1_after_insert_notin
AFTER INSERT
ON data_part1
FOR EACH ROW
WHEN new.code NOT IN(SELECT code FROM data_all WHERE exchange=new.exchange AND code=new.code)
BEGIN
  REPLACE INTO log_info(summary, details) VALUES('t_data_part1_after_insert_notin', '['||new.exchange||']['||new.code||']['||new.name||']['||new.group_id||']');
  INSERT  INTO data_all(exchange, code, name, group_id) VALUES(new.exchange, new.code, new.name, new.group_id);
END;

--表data_part1的inset。
CREATE TRIGGER t_data_part1_after_insert_isnull
AFTER INSERT
ON data_part1
FOR EACH ROW
--"new.code=NULL"是假，"new.code!=NULL"也是假。
--WHEN new.code!=(SELECT code FROM data_all WHERE exchange=new.exchange AND code=new.code LIMIT 1)的写法是错误的。
WHEN (SELECT code FROM data_all WHERE exchange=new.exchange AND code=new.code LIMIT 1) IS NULL
BEGIN
  REPLACE INTO log_info(summary, details) VALUES('t_data_part1_after_insert_isnull', '['||new.exchange||']['||new.code||']['||new.name||']['||new.group_id||']');
  --为NULL，就是没有数据，没有数据就要插入。
  INSERT  INTO data_all(exchange, code, name, group_id) VALUES(new.exchange, new.code, new.name, new.group_id);
END;

--表data_part1的inset。
CREATE TRIGGER t_data_part1_after_insert_isnotnull
AFTER INSERT
ON data_part1
FOR EACH ROW
WHEN (SELECT code FROM data_all WHERE exchange=new.exchange AND code=new.code LIMIT 1) IS NOT NULL
BEGIN
  REPLACE INTO log_info(summary, details) VALUES('t_data_part1_after_insert_isnotnull', '['||new.exchange||']['||new.code||']['||new.name||']['||new.group_id||']');
  --不为NULL，就是已经有了数据，就要update，如果insert的话，原来的数据就丢了。
  UPDATE data_all SET name=new.name, group_id=new.group_id WHERE exchange=new.exchange AND code=new.code;
END;

--表data_part1的update。
CREATE TRIGGER t_data_part1_after_update_isnull
AFTER UPDATE
ON data_part1
FOR EACH ROW
WHEN (SELECT code FROM data_all WHERE exchange=new.exchange AND code=new.code LIMIT 1) IS NULL
BEGIN
  REPLACE INTO log_info(summary, details) VALUES('t_data_part1_after_update_isnull', '['||new.exchange||']['||new.code||']['||new.name||']['||new.group_id||']');
  INSERT  INTO data_all(exchange, code, name, group_id) VALUES(new.exchange, new.code, new.name, new.group_id);
END;

--表data_part1的update。
CREATE TRIGGER t_data_part1_after_update_isnotnull
AFTER UPDATE
ON data_part1
FOR EACH ROW
WHEN (SELECT code FROM data_all WHERE exchange=new.exchange AND code=new.code LIMIT 1) IS NOT NULL
BEGIN
  REPLACE INTO log_info(summary, details) VALUES('t_data_part1_after_update_isnotnull', '['||new.exchange||']['||new.code||']['||new.name||']['||new.group_id||']');
  UPDATE data_all SET name=new.name, group_id=new.group_id WHERE exchange=new.exchange AND code=new.code;
END;

--表data_part2的insert。
CREATE TRIGGER t_data_part2_after_insert_isnull
AFTER INSERT
ON data_part2
FOR EACH ROW
WHEN (SELECT code FROM data_all WHERE exchange=new.exchange AND code=new.code LIMIT 1) IS NULL
BEGIN
  REPLACE INTO log_info(summary, details) VALUES('t_data_part2_after_insert_isnull', '['||new.exchange||']['||new.code||']['||new.price_tick||']');
  INSERT  INTO data_all(exchange, code, price_tick) VALUES(new.exchange, new.code, new.price_tick);
END;

--表data_part2的insert。
CREATE TRIGGER t_data_part2_after_insert_isnotnull
AFTER INSERT
ON data_part2
FOR EACH ROW
WHEN (SELECT code FROM data_all WHERE exchange=new.exchange AND code=new.code LIMIT 1) IS NOT NULL
BEGIN
  REPLACE INTO log_info(summary, details) VALUES('t_data_part2_after_insert_isnotnull', '['||new.exchange||']['||new.code||']['||new.price_tick||']');
  UPDATE data_all SET price_tick=new.price_tick WHERE exchange=new.exchange AND code=new.code;
END;

--清空各个表的语句。
delete from data_part1;
delete from data_part2;
delete from data_all;
delete from log_info;
--查询各个表的语句。
select * from data_part1;
select * from data_part2;
select * from data_all;
select * from log_info;
--往表里面插入数据，让触发器执行操作，然后在log_info里面查看和理解触发器的行为。
replace into data_part1(exchange, code, name, group_id) VALUES('SSE', '600000', 'PFYH', 'ASHARE');
replace into data_part1(exchange, code, name, group_id) VALUES('SSE', '600000', 'PUFA', 'ASHARE');
replace into data_part2(exchange, code, price_tick) VALUES('SSE', '600000', 0.01);

我们还可以创建好表和触发器，然后用python的sqlalchemy往SQLite里面插数据，然后触发器就自动加工出来目标数据了。

sqlalchemy往SQLite里面插数据的代码在这个链接里：http://blog.youkuaiyun.com/u013600225/article/details/53914180

完。