yjkXb9XA

1# sqlite3触发器的使用实例
2对于SQLite和SQLite的触发器，我先说一些结果吧：
3
4#### 01. SQLite不支持赋值操作，自然也没有变量之说。
5建表时，如果不指定 NOT NULL 的话，主键和联合主键的字段是可以插入NULL值的。
6建表时，强烈建议主键/联合主键是NOT NULL的，在SQLite中，主键为NULL的数据行可以重复插入：
7```sql
8CREATE TABLE kv(
9k TEXT NULL PRIMARY KEY,
10v TEXT NULL
11);
12SELECT * FROM kv;
13REPLACE INTO kv(k,v)VALUES(NULL,'value_of_NULL'),('key','value');
14```
1502. NULL与任何数据进行运算，结果都是NULL。ISNULL和NOTNULL应该不是运算符。请执行下面的语句进行测试：
16```sql
17SELECT 1=NULL, 1!=NULL, NULL=NULL, NULL!=NULL, NULL ISNULL, NULL IS NULL, NULL NOTNULL;
18```
1903. 在CASE-WHEN-THEN-ELSE-END语句中，THEN后面可以接SELECT语句，但是没法接INSERT/REPLACE/UPDATE语句，貌似因为它们没有返回值。
2004. SQLite没有FLOOR函数、CEIL函数、POWER函数、等函数。
2105. 当你对SQLite有什么猜想需要验证时，不要用NavicatForSQLite进行验证，建议使用SQLiteStudio，至少我还没有发现SQLiteStudio异常的地方。（注：Navicat里面可以对SQLite执行POWER和FLOOR等函数，但是原生的SQLite不支持它们。触发器的某些写法在Navicat里面创建失败，但是用SQLite官网提供的程序就可以创建成功）。
2206. 触发器里面无法使用IF-ELSE语句。
2307. 触发器里面无法创建临时表。
2408. 触发器在进行递归调用时，会报错（Error while executing SQL query on database 'test.sqlite3': too many levels of trigger recursion），并没有陷入死循环中，这个挺好。（验证方式：给一个数据表创建两个UPDATE OF触发器，一个是字段A的触发器，它会更新字段B，一个是字段B的触发器，它会更新字段A，然后外部用UPDATE语句驱动程序进入这个死循环，查看结果）。
2509. 行级触发器里面的NEW和OLD是某行数据的引用。引用的值在触发器的本次调用过程中不会改变（在本次调用过程中，即使我UPDATE了某个字段的值，NEW中的这个字段的值也不会改变/实时更新）。（验证方式：自己创建一个数据表和一个日志表，创建一个数据表的触发器，将NEW的所有字段的值写进日志表，修改某个字段的值，再将NEW的所有字段的值写进日志表）。
2610. 对于UPDATE语句（UPDATE 表名 SET field_1=value_1,field_2=value_2,... WHERE 条件;），不论这个UPDATE语句同时SET了多少字段，如果它仅仅修改了一行，那么UPDATE的FOR EACH ROW触发器仅调用一次。（验证方式：自己创建一个数据表和一个日志表，创建一个数据表的触发器，触发器中往日志表中写进NEW的所有值，往数据表里INSERT几行数据，然后UPDATE某一行字段的几个值）。
2711. 触发器的调用顺序，貌似是对触发器按照名字排序，然后逐个调用。（验证方式：一个数据表创建两个AFTER INSERT触发器，触发器里面往日志表中写消息，往数据表中INSERT一行数据，查看日志表，对触发器改名，再尝试一次）。
28下面是一个实战的例子：
29说明：不建议按照这个例子的写法进行作业，这样做的话，可读性和可维护性不好，各个表之间是强耦合的，后期有改动的话，可能会牵一发而动全身。
30本例则主要内容：股票数据存储在两个文件中，我需要将这俩文件存储到raw_data_1和raw_data_2中，然后利用raw_data中的数据在中间表it_data中加工出来数据，并将其更新到目标数据表target_data中，加工方式是利用price_tick和pre_close_price计算出来第二天的涨跌停价。
31代码如下（如果代码显示不完全，可以点击“view plain”查看完整代码）：
32```sql
33--我为自己规定的触发器名字的通用格式：t_(表名/视图名)_(before/after)_(insert/update/delete)_自定义字符串.
34--本例中的建表规则：除了主键(联合主键)字段不可为NULL，其他字段都可以为NULL，大不了计算出来的数据为NULL.
35 
36 
37--原始数据表(数据部分1).
38CREATE TABLE IF NOT EXISTS raw_data_1(
39exchange   TEXT  NOT NULL,
40code       TEXT  NOT NULL,
41name       TEXT      NULL,
42price_tick REAL      NULL,
43PRIMARY KEY(exchange, code)
44);
45 
46--原始数据表(数据部分2).
47CREATE TABLE IF NOT EXISTS raw_data_2(
48exchange   TEXT NOT NULL,
49code       TEXT NOT NULL,
50l_close_px REAL     NULL,
51PRIMARY KEY(exchange, code)
52);
53 
54--中间数据表(由原始数据加工出来).
55CREATE TABLE IF NOT EXISTS it_data( --[intermediate table][it][中间表]
56exchange         TEXT    NOT NULL,
57code             TEXT    NOT NULL,
58name             TEXT        NULL,
59price_tick       REAL        NULL,
60l_close_px       REAL        NULL,
61upper_px         REAL        NULL,
62lower_px         REAL        NULL,
63i_decimal_places INTEGER     NULL, --intermediate,中间数据,小数位数.
64i_gain           REAL        NULL, --intermediate,中间数据,放大倍数.
65t_calc           TINYINT     NULL, --trigger,为了触发"触发器"而设置的字段.
66t_update         TINYINT     NULL, --trigger,为了触发"触发器"而设置的字段.
67PRIMARY KEY(exchange, code)
68);
69 
70--目标数据表(由中间数据生成).
71CREATE TABLE IF NOT EXISTS target_data(
72exchange       TEXT    NOT NULL,
73code           TEXT    NOT NULL,
74name           TEXT        NULL,
75price_tick     REAL        NULL,
76l_close_px     REAL        NULL,
77upper_px       REAL        NULL,
78lower_px       REAL        NULL,
79PRIMARY KEY(exchange, code)
80);
81 
82--日志表.
83CREATE TABLE IF NOT EXISTS log_info(
84idx     INTEGER PRIMARY KEY, --A column declared INTEGER PRIMARY KEY will autoincrement.
85dttm    TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'now', 'localtime')),
86summary TEXT      NOT NULL, --摘要
87details TEXT          NULL  --详情
88);
89 
90 
91--此触发器的作用：规定触发器的书写格式，验证触发器里面无法创建临时表.
92CREATE TRIGGER IF NOT EXISTS t_log_info_after_delete_buildfail
93AFTER DELETE
94ON log_info
95FOR EACH ROW
96WHEN (1=1) AND (3 NOTNULL) AND (NULL ISNULL)
97BEGIN
98  --(报错)触发器里面无法创建临时表.
99  CREATE TEMP TABLE kv(k TEXT NOT NULL PRIMARY KEY, v TEXT);
100  --(报错)SQLite没有FLOOR、CEIL、POWER函数，同时有ROUND函数.
101  SELECT FLOOR(5.175), CEIL(5.175), POWER(10, 2);
102  --(正确)输出格式:YYYY-MM-DD HH:MM:SS.
103  SELECT DATETIME('now','localtime');
104END;
105 
106--表raw_data_1的([insert/update]*[ISNULL/NOTNULL])共4个触发器的作用:
107--不管insert还是update，若it表没有此数据，则insert它+触发计算.
108--不管insert还是update，若it表存在此数据，则update它+触发计算.
109--1. insert，无此数据，insert它，触发计算.
110CREATE TRIGGER t_raw_data_1_after_insert_notexist
111AFTER INSERT
112ON raw_data_1
113FOR EACH ROW
114WHEN (SELECT code FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1) ISNULL
115BEGIN
116  REPLACE INTO it_data(exchange,code,name,price_tick)VALUES(NEW.exchange,NEW.code,NEW.name,NEW.price_tick);
117  UPDATE it_data SET t_calc=1 WHERE exchange=NEW.exchange AND code=NEW.code;
118END;
119--2. update，无此数据，insert它，触发计算.
120CREATE TRIGGER t_raw_data_1_after_update_notexist
121AFTER UPDATE
122ON raw_data_1
123FOR EACH ROW
124--如果不用 LIMIT 的话，得用 NOT IN 关键字才行，貌似因为它返回的是一个列表.
125--WHEN NEW.code NOT IN (SELECT code FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code)
126--表达式(NEW.code!=NULL)的值为NULL.
127--WHEN NEW.code!=(SELECT code FROM it_data WHERE exchange=new.exchange AND code=new.code LIMIT 1)的写法是错误的.
128WHEN (SELECT code FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1) ISNULL
129BEGIN
130  REPLACE INTO it_data(exchange,code,name,price_tick)VALUES(NEW.exchange,NEW.code,NEW.name,NEW.price_tick);
131  UPDATE it_data SET t_calc=1 WHERE exchange=NEW.exchange AND code=NEW.code;
132END;
133--3. insert，有数据，update它，触发计算.
134CREATE TRIGGER t_raw_data_1_after_insert_exist
135AFTER INSERT
136ON raw_data_1
137FOR EACH ROW
138WHEN (SELECT code FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1) NOTNULL
139BEGIN
140  --除了主键之外，要将NEW中剩余的所有字段都update进it_data表中.
141  --如果不用UPDATE，使用了INSERT的话，其他表(raw_data_2)插入的值就丢了.
142  UPDATE it_data SET name=NEW.name,price_tick=NEW.price_tick WHERE exchange=NEW.exchange AND code=NEW.code;
143  UPDATE it_data SET t_calc=1 WHERE exchange=NEW.exchange AND code=NEW.code;
144END;
145--4. update，有数据，update它，触发计算.
146CREATE TRIGGER t_raw_data_1_after_update_exist
147AFTER UPDATE
148ON raw_data_1
149FOR EACH ROW
150WHEN (SELECT code FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1) NOTNULL
151BEGIN
152  --除了主键之外，要将NEW中剩余的所有字段都update进it_data表中.
153  UPDATE it_data SET name=NEW.name,price_tick=NEW.price_tick WHERE exchange=NEW.exchange AND code=NEW.code;
154  UPDATE it_data SET t_calc=1 WHERE exchange=NEW.exchange AND code=NEW.code;
155END;
156 
157--表raw_data_2的(insert/update*ISNULL/NOTNULL)共4个触发器的作用：同上.
158--1. insert，无此数据，insert它，触发计算.
159CREATE TRIGGER t_raw_data_2_after_insert_notexist
160AFTER INSERT
161ON raw_data_2
162FOR EACH ROW
163WHEN (SELECT code FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1) ISNULL
164BEGIN
165  REPLACE INTO it_data(exchange,code,l_close_px)VALUES(NEW.exchange,NEW.code,NEW.l_close_px);
166  UPDATE it_data SET t_calc=1 WHERE exchange=NEW.exchange AND code=NEW.code;
167END;
168--2. update，无此数据，insert它，触发计算.
169CREATE TRIGGER t_raw_data_2_after_update_notexist
170AFTER UPDATE
171ON raw_data_2
172FOR EACH ROW
173WHEN (SELECT code FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1) ISNULL
174BEGIN
175  REPLACE INTO it_data(exchange,code,l_close_px)VALUES(NEW.exchange,NEW.code,NEW.l_close_px);
176  UPDATE it_data SET t_calc=1 WHERE exchange=NEW.exchange AND code=NEW.code;
177END;
178--3. insert，有数据，update它，触发计算.
179CREATE TRIGGER t_raw_data_2_after_insert_exist
180AFTER INSERT
181ON raw_data_2
182FOR EACH ROW
183WHEN (SELECT code FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1) NOTNULL
184BEGIN
185  --除了主键之外，要将NEW中剩余的所有字段都update进it表中.
186  UPDATE it_data SET l_close_px=NEW.l_close_px WHERE exchange=NEW.exchange AND code=NEW.code;
187  UPDATE it_data SET t_calc=1 WHERE exchange=NEW.exchange AND code=NEW.code;
188END;
189--4. update，有数据，update它，触发计算.
190CREATE TRIGGER t_raw_data_2_after_update_exist
191AFTER UPDATE
192ON raw_data_2
193FOR EACH ROW
194WHEN (SELECT code FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1) NOTNULL
195BEGIN
196  --除了主键之外，要将NEW中剩余的所有字段都update进it表中.
197  UPDATE it_data SET l_close_px=NEW.l_close_px WHERE exchange=NEW.exchange AND code=NEW.code;
198  UPDATE it_data SET t_calc=1 WHERE exchange=NEW.exchange AND code=NEW.code;
199END;
200 
201--此触发器的作用：当t_calc被修改时，触发计算的触发器，计算完毕后，更新t_update字段.
202--如果此触发器仅仅是AFTER UPDATE，并没有指定列，那么此触发器在运行过程中，会修改自己的那一行的数据，可能还会触发自己，导致错误，所以要有一个专门的列来触发本触发器.
203CREATE TRIGGER t_it_data_after_update_t_calc
204AFTER UPDATE OF t_calc --t_calc字段用于触发计算.
205ON it_data
206FOR EACH ROW
207WHEN (NEW.price_tick NOTNULL) AND (NEW.l_close_px NOTNULL)
208BEGIN
209  --计算有几位小数.
210  UPDATE it_data SET i_decimal_places=LENGTH(RTRIM(NEW.price_tick,' 0'))-LENGTH(REPLACE(NEW.price_tick,'.','_')/1)-1 WHERE exchange=NEW.exchange AND code=NEW.code;
211  --计算放大倍数.
212  UPDATE it_data SET i_gain=(CASE (SELECT i_decimal_places FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1)
213                               WHEN 0 THEN 1
214                               WHEN 1 THEN 10
215                               WHEN 2 THEN 100
216                               WHEN 3 THEN 1000
217                               WHEN 4 THEN 10000
218                               WHEN 5 THEN 100000
219                               WHEN 6 THEN 1000000
220                               ELSE 1 END) WHERE exchange=NEW.exchange AND code=NEW.code;
221  --计算涨停价.
222  UPDATE it_data SET upper_px=REPLACE(NEW.l_close_px*1.1*(SELECT i_gain FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1)+0.5, '.', '_'
223                                      )*1.0/(SELECT i_gain FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1)
224                                WHERE exchange=NEW.exchange AND code=NEW.code;
225  --计算跌停价.
226  UPDATE it_data SET lower_px=REPLACE(NEW.l_close_px*0.9*(SELECT i_gain FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1)+0.5, '.', '_'
227                                      )*1.0/(SELECT i_gain FROM it_data WHERE exchange=NEW.exchange AND code=NEW.code LIMIT 1)
228                                WHERE exchange=NEW.exchange AND code=NEW.code;
229  --更新t_update字段.
230  UPDATE it_data SET t_update=1 WHERE exchange=NEW.exchange AND code=NEW.code;
231END;
232 
233--此触发器的作用：当t_update被修改时，触发更新的触发器，将数据更新到目标表.
234CREATE TRIGGER t_it_data_after_update_t_update
235AFTER UPDATE OF t_update --t_update字段用于触发更新数据.
236ON it_data
237FOR EACH ROW
238BEGIN
239  REPLACE INTO target_data(exchange,code,name,price_tick,l_close_px,upper_px,lower_px)VALUES(NEW.exchange,NEW.code,NEW.name,NEW.price_tick,NEW.l_close_px,NEW.upper_px,NEW.lower_px);
240END;
241 
242 
243--清空各个表:
244DELETE FROM raw_data_1;
245DELETE FROM raw_data_2;
246DELETE FROM it_data;
247DELETE FROM target_data;
248DELETE FROM log_info;
249--查询各个表:
250SELECT * FROM raw_data_1;
251SELECT * FROM raw_data_2;
252SELECT * FROM it_data;
253SELECT * FROM target_data;
254SELECT * FROM log_info;
255--往log_info里面写日志的语句:
256REPLACE INTO log_info(summary,details)VALUES("some_summary",'some_details');
257--测试语句1:
258REPLACE INTO raw_data_1(exchange,code,name,price_tick)VALUES('SSE','600000','PFYH',0.01);
259REPLACE INTO raw_data_2(exchange,code,l_close_px)     VALUES('SSE','600000',5.75);
260--测试语句2:
261REPLACE INTO raw_data_1(exchange,code,name,price_tick)VALUES('SZSE','000001','PAYH',0.01);
262REPLACE INTO raw_data_2(exchange,code,l_close_px)     VALUES('SZSE','000001',10.25);
263
264```