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 201
