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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15 16# If either views or triggers are disabled in this build, omit this file. 17ifcapable {!trigger || !view} { 18 finish_test 19 return 20} 21 22do_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} 38do_test trigger4-1.2 { 39 execsql { 40 select * from test2; 41 } 42} {1 3} 43do_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} 51do_test trigger4-1.4 { 52 execsql { 53 select * from test2; 54 } 55} {1 3 4 6} 56 57do_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} 68do_test trigger4-2.2 { 69 execsql { 70 select * from test2; 71 } 72} {1 3 4 6} 73do_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} 81do_test trigger4-2.4 { 82 execsql { 83 select * from test2; 84 } 85} {1 3 4 66} 86 87do_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}} 93do_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}} 100do_test trigger4-3.3 { 101 catchsql { 102 update test set a=222 where id=1; 103 } 104} {1 {no such table: main.test2}} 105do_test trigger4-3.4 { 106 execsql { 107 select * from test1; 108 } 109} {1 22 4 5} 110do_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} 117do_test trigger4-3.6 { 118 execsql { 119 select * from test2; 120 } 121} {7 9} 122do_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 131do_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} 160do_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 165do_test trigger4-5.1 { 166 catchsql {delete from vw where a>101 and a<2000} 167} {0 {}} 168do_test trigger4-5.2 { 169 execsql {select * from vw} 170} {101 1001} 171 172#test insert into view 173do_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 {}} 184do_test trigger4-6.2 { 185 execsql {select count(*) from vw} 186} {128} 187 188#test update of view 189do_test trigger4-7.1 { 190 catchsql {update vw set b=b+1000 where a>101 and a<2000} 191} {0 {}} 192do_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 196integrity_check trigger4-99.9 197db close 198file delete -force trigtest.db trigtest.db-journal 199 200finish_test 201