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