|
1 # The author disclaims copyright to this source code. In place of |
|
2 # a legal notice, here is a blessing: |
|
3 # |
|
4 # May you do good and not evil. |
|
5 # May you find forgiveness for yourself and forgive others. |
|
6 # May you share freely, never taking more than you give. |
|
7 # |
|
8 #*********************************************************************** |
|
9 # |
|
10 # This file tests the triggers of views. |
|
11 # |
|
12 |
|
13 set testdir [file dirname $argv0] |
|
14 source $testdir/tester.tcl |
|
15 |
|
16 # If either views or triggers are disabled in this build, omit this file. |
|
17 ifcapable {!trigger || !view} { |
|
18 finish_test |
|
19 return |
|
20 } |
|
21 |
|
22 do_test trigger4-1.1 { |
|
23 execsql { |
|
24 create table test1(id integer primary key,a); |
|
25 create table test2(id integer,b); |
|
26 create view test as |
|
27 select test1.id as id,a as a,b as b |
|
28 from test1 join test2 on test2.id = test1.id; |
|
29 create trigger I_test instead of insert on test |
|
30 begin |
|
31 insert into test1 (id,a) values (NEW.id,NEW.a); |
|
32 insert into test2 (id,b) values (NEW.id,NEW.b); |
|
33 end; |
|
34 insert into test values(1,2,3); |
|
35 select * from test1; |
|
36 } |
|
37 } {1 2} |
|
38 do_test trigger4-1.2 { |
|
39 execsql { |
|
40 select * from test2; |
|
41 } |
|
42 } {1 3} |
|
43 do_test trigger4-1.3 { |
|
44 db close |
|
45 sqlite3 db test.db |
|
46 execsql { |
|
47 insert into test values(4,5,6); |
|
48 select * from test1; |
|
49 } |
|
50 } {1 2 4 5} |
|
51 do_test trigger4-1.4 { |
|
52 execsql { |
|
53 select * from test2; |
|
54 } |
|
55 } {1 3 4 6} |
|
56 |
|
57 do_test trigger4-2.1 { |
|
58 execsql { |
|
59 create trigger U_test instead of update on test |
|
60 begin |
|
61 update test1 set a=NEW.a where id=NEW.id; |
|
62 update test2 set b=NEW.b where id=NEW.id; |
|
63 end; |
|
64 update test set a=22 where id=1; |
|
65 select * from test1; |
|
66 } |
|
67 } {1 22 4 5} |
|
68 do_test trigger4-2.2 { |
|
69 execsql { |
|
70 select * from test2; |
|
71 } |
|
72 } {1 3 4 6} |
|
73 do_test trigger4-2.3 { |
|
74 db close |
|
75 sqlite3 db test.db |
|
76 execsql { |
|
77 update test set b=66 where id=4; |
|
78 select * from test1; |
|
79 } |
|
80 } {1 22 4 5} |
|
81 do_test trigger4-2.4 { |
|
82 execsql { |
|
83 select * from test2; |
|
84 } |
|
85 } {1 3 4 66} |
|
86 |
|
87 do_test trigger4-3.1 { |
|
88 catchsql { |
|
89 drop table test2; |
|
90 insert into test values(7,8,9); |
|
91 } |
|
92 } {1 {no such table: main.test2}} |
|
93 do_test trigger4-3.2 { |
|
94 db close |
|
95 sqlite3 db test.db |
|
96 catchsql { |
|
97 insert into test values(7,8,9); |
|
98 } |
|
99 } {1 {no such table: main.test2}} |
|
100 do_test trigger4-3.3 { |
|
101 catchsql { |
|
102 update test set a=222 where id=1; |
|
103 } |
|
104 } {1 {no such table: main.test2}} |
|
105 do_test trigger4-3.4 { |
|
106 execsql { |
|
107 select * from test1; |
|
108 } |
|
109 } {1 22 4 5} |
|
110 do_test trigger4-3.5 { |
|
111 execsql { |
|
112 create table test2(id,b); |
|
113 insert into test values(7,8,9); |
|
114 select * from test1; |
|
115 } |
|
116 } {1 22 4 5 7 8} |
|
117 do_test trigger4-3.6 { |
|
118 execsql { |
|
119 select * from test2; |
|
120 } |
|
121 } {7 9} |
|
122 do_test trigger4-3.7 { |
|
123 db close |
|
124 sqlite3 db test.db |
|
125 execsql { |
|
126 update test set b=99 where id=7; |
|
127 select * from test2; |
|
128 } |
|
129 } {7 99} |
|
130 |
|
131 do_test trigger4-4.1 { |
|
132 db close |
|
133 file delete -force trigtest.db |
|
134 file delete -force trigtest.db-journal |
|
135 sqlite3 db trigtest.db |
|
136 catchsql {drop table tbl; drop view vw} |
|
137 execsql { |
|
138 create table tbl(a integer primary key, b integer); |
|
139 create view vw as select * from tbl; |
|
140 create trigger t_del_tbl instead of delete on vw for each row begin |
|
141 delete from tbl where a = old.a; |
|
142 end; |
|
143 create trigger t_upd_tbl instead of update on vw for each row begin |
|
144 update tbl set a=new.a, b=new.b where a = old.a; |
|
145 end; |
|
146 create trigger t_ins_tbl instead of insert on vw for each row begin |
|
147 insert into tbl values (new.a,new.b); |
|
148 end; |
|
149 insert into tbl values(101,1001); |
|
150 insert into tbl values(102,1002); |
|
151 insert into tbl select a+2, b+2 from tbl; |
|
152 insert into tbl select a+4, b+4 from tbl; |
|
153 insert into tbl select a+8, b+8 from tbl; |
|
154 insert into tbl select a+16, b+16 from tbl; |
|
155 insert into tbl select a+32, b+32 from tbl; |
|
156 insert into tbl select a+64, b+64 from tbl; |
|
157 select count(*) from vw; |
|
158 } |
|
159 } {128} |
|
160 do_test trigger4-4.2 { |
|
161 execsql {select a, b from vw where a<103 or a>226 order by a} |
|
162 } {101 1001 102 1002 227 1127 228 1128} |
|
163 |
|
164 #test delete from view |
|
165 do_test trigger4-5.1 { |
|
166 catchsql {delete from vw where a>101 and a<2000} |
|
167 } {0 {}} |
|
168 do_test trigger4-5.2 { |
|
169 execsql {select * from vw} |
|
170 } {101 1001} |
|
171 |
|
172 #test insert into view |
|
173 do_test trigger4-6.1 { |
|
174 catchsql { |
|
175 insert into vw values(102,1002); |
|
176 insert into vw select a+2, b+2 from vw; |
|
177 insert into vw select a+4, b+4 from vw; |
|
178 insert into vw select a+8, b+8 from vw; |
|
179 insert into vw select a+16, b+16 from vw; |
|
180 insert into vw select a+32, b+32 from vw; |
|
181 insert into vw select a+64, b+64 from vw; |
|
182 } |
|
183 } {0 {}} |
|
184 do_test trigger4-6.2 { |
|
185 execsql {select count(*) from vw} |
|
186 } {128} |
|
187 |
|
188 #test update of view |
|
189 do_test trigger4-7.1 { |
|
190 catchsql {update vw set b=b+1000 where a>101 and a<2000} |
|
191 } {0 {}} |
|
192 do_test trigger4-7.2 { |
|
193 execsql {select a, b from vw where a<=102 or a>=227 order by a} |
|
194 } {101 1001 102 2002 227 2127 228 2128} |
|
195 |
|
196 integrity_check trigger4-99.9 |
|
197 db close |
|
198 file delete -force trigtest.db trigtest.db-journal |
|
199 |
|
200 finish_test |