1 2#pragma ident "%Z%%M% %I% %E% SMI" 3 4# The author disclaims copyright to this source code. In place of 5# a legal notice, here is a blessing: 6# 7# May you do good and not evil. 8# May you find forgiveness for yourself and forgive others. 9# May you share freely, never taking more than you give. 10# 11#*********************************************************************** 12# 13# This file tests the triggers of views. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19do_test trigger4-1.1 { 20 execsql { 21 create table test1(id integer primary key,a); 22 create table test2(id integer,b); 23 create view test as 24 select test1.id as id,a as a,b as b 25 from test1 join test2 on test2.id = test1.id; 26 create trigger I_test instead of insert on test 27 begin 28 insert into test1 (id,a) values (NEW.id,NEW.a); 29 insert into test2 (id,b) values (NEW.id,NEW.b); 30 end; 31 insert into test values(1,2,3); 32 select * from test1; 33 } 34} {1 2} 35do_test trigger4-1.2 { 36 execsql { 37 select * from test2; 38 } 39} {1 3} 40do_test trigger4-1.3 { 41 db close 42 sqlite db test.db 43 execsql { 44 insert into test values(4,5,6); 45 select * from test1; 46 } 47} {1 2 4 5} 48do_test trigger4-1.4 { 49 execsql { 50 select * from test2; 51 } 52} {1 3 4 6} 53 54do_test trigger4-2.1 { 55 execsql { 56 create trigger U_test instead of update on test 57 begin 58 update test1 set a=NEW.a where id=NEW.id; 59 update test2 set b=NEW.b where id=NEW.id; 60 end; 61 update test set a=22 where id=1; 62 select * from test1; 63 } 64} {1 22 4 5} 65do_test trigger4-2.2 { 66 execsql { 67 select * from test2; 68 } 69} {1 3 4 6} 70do_test trigger4-2.3 { 71 db close 72 sqlite db test.db 73 execsql { 74 update test set b=66 where id=4; 75 select * from test1; 76 } 77} {1 22 4 5} 78do_test trigger4-2.4 { 79 execsql { 80 select * from test2; 81 } 82} {1 3 4 66} 83 84do_test trigger4-3.1 { 85 catchsql { 86 drop table test2; 87 insert into test values(7,8,9); 88 } 89} {1 {no such table: main.test2}} 90do_test trigger4-3.2 { 91 db close 92 sqlite db test.db 93 catchsql { 94 insert into test values(7,8,9); 95 } 96} {1 {no such table: main.test2}} 97do_test trigger4-3.3 { 98 catchsql { 99 update test set a=222 where id=1; 100 } 101} {1 {no such table: main.test2}} 102do_test trigger4-3.4 { 103 execsql { 104 select * from test1; 105 } 106} {1 22 4 5} 107do_test trigger4-3.5 { 108 execsql { 109 create table test2(id,b); 110 insert into test values(7,8,9); 111 select * from test1; 112 } 113} {1 22 4 5 7 8} 114do_test trigger4-3.6 { 115 execsql { 116 select * from test2; 117 } 118} {7 9} 119do_test trigger4-3.7 { 120 db close 121 sqlite db test.db 122 execsql { 123 update test set b=99 where id=7; 124 select * from test2; 125 } 126} {7 99} 127 128integrity_check trigger4-4.1 129 130finish_test 131