1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 37c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 47c478bd9Sstevel@tonic-gate# 57c478bd9Sstevel@tonic-gate# May you do good and not evil. 67c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 77c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 87c478bd9Sstevel@tonic-gate# 97c478bd9Sstevel@tonic-gate#*********************************************************************** 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate# This file tests the triggers of views. 127c478bd9Sstevel@tonic-gate# 137c478bd9Sstevel@tonic-gate 147c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 157c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 167c478bd9Sstevel@tonic-gate 177c478bd9Sstevel@tonic-gatedo_test trigger4-1.1 { 187c478bd9Sstevel@tonic-gate execsql { 197c478bd9Sstevel@tonic-gate create table test1(id integer primary key,a); 207c478bd9Sstevel@tonic-gate create table test2(id integer,b); 217c478bd9Sstevel@tonic-gate create view test as 227c478bd9Sstevel@tonic-gate select test1.id as id,a as a,b as b 237c478bd9Sstevel@tonic-gate from test1 join test2 on test2.id = test1.id; 247c478bd9Sstevel@tonic-gate create trigger I_test instead of insert on test 257c478bd9Sstevel@tonic-gate begin 267c478bd9Sstevel@tonic-gate insert into test1 (id,a) values (NEW.id,NEW.a); 277c478bd9Sstevel@tonic-gate insert into test2 (id,b) values (NEW.id,NEW.b); 287c478bd9Sstevel@tonic-gate end; 297c478bd9Sstevel@tonic-gate insert into test values(1,2,3); 307c478bd9Sstevel@tonic-gate select * from test1; 317c478bd9Sstevel@tonic-gate } 327c478bd9Sstevel@tonic-gate} {1 2} 337c478bd9Sstevel@tonic-gatedo_test trigger4-1.2 { 347c478bd9Sstevel@tonic-gate execsql { 357c478bd9Sstevel@tonic-gate select * from test2; 367c478bd9Sstevel@tonic-gate } 377c478bd9Sstevel@tonic-gate} {1 3} 387c478bd9Sstevel@tonic-gatedo_test trigger4-1.3 { 397c478bd9Sstevel@tonic-gate db close 407c478bd9Sstevel@tonic-gate sqlite db test.db 417c478bd9Sstevel@tonic-gate execsql { 427c478bd9Sstevel@tonic-gate insert into test values(4,5,6); 437c478bd9Sstevel@tonic-gate select * from test1; 447c478bd9Sstevel@tonic-gate } 457c478bd9Sstevel@tonic-gate} {1 2 4 5} 467c478bd9Sstevel@tonic-gatedo_test trigger4-1.4 { 477c478bd9Sstevel@tonic-gate execsql { 487c478bd9Sstevel@tonic-gate select * from test2; 497c478bd9Sstevel@tonic-gate } 507c478bd9Sstevel@tonic-gate} {1 3 4 6} 517c478bd9Sstevel@tonic-gate 527c478bd9Sstevel@tonic-gatedo_test trigger4-2.1 { 537c478bd9Sstevel@tonic-gate execsql { 547c478bd9Sstevel@tonic-gate create trigger U_test instead of update on test 557c478bd9Sstevel@tonic-gate begin 567c478bd9Sstevel@tonic-gate update test1 set a=NEW.a where id=NEW.id; 577c478bd9Sstevel@tonic-gate update test2 set b=NEW.b where id=NEW.id; 587c478bd9Sstevel@tonic-gate end; 597c478bd9Sstevel@tonic-gate update test set a=22 where id=1; 607c478bd9Sstevel@tonic-gate select * from test1; 617c478bd9Sstevel@tonic-gate } 627c478bd9Sstevel@tonic-gate} {1 22 4 5} 637c478bd9Sstevel@tonic-gatedo_test trigger4-2.2 { 647c478bd9Sstevel@tonic-gate execsql { 657c478bd9Sstevel@tonic-gate select * from test2; 667c478bd9Sstevel@tonic-gate } 677c478bd9Sstevel@tonic-gate} {1 3 4 6} 687c478bd9Sstevel@tonic-gatedo_test trigger4-2.3 { 697c478bd9Sstevel@tonic-gate db close 707c478bd9Sstevel@tonic-gate sqlite db test.db 717c478bd9Sstevel@tonic-gate execsql { 727c478bd9Sstevel@tonic-gate update test set b=66 where id=4; 737c478bd9Sstevel@tonic-gate select * from test1; 747c478bd9Sstevel@tonic-gate } 757c478bd9Sstevel@tonic-gate} {1 22 4 5} 767c478bd9Sstevel@tonic-gatedo_test trigger4-2.4 { 777c478bd9Sstevel@tonic-gate execsql { 787c478bd9Sstevel@tonic-gate select * from test2; 797c478bd9Sstevel@tonic-gate } 807c478bd9Sstevel@tonic-gate} {1 3 4 66} 817c478bd9Sstevel@tonic-gate 827c478bd9Sstevel@tonic-gatedo_test trigger4-3.1 { 837c478bd9Sstevel@tonic-gate catchsql { 847c478bd9Sstevel@tonic-gate drop table test2; 857c478bd9Sstevel@tonic-gate insert into test values(7,8,9); 867c478bd9Sstevel@tonic-gate } 877c478bd9Sstevel@tonic-gate} {1 {no such table: main.test2}} 887c478bd9Sstevel@tonic-gatedo_test trigger4-3.2 { 897c478bd9Sstevel@tonic-gate db close 907c478bd9Sstevel@tonic-gate sqlite db test.db 917c478bd9Sstevel@tonic-gate catchsql { 927c478bd9Sstevel@tonic-gate insert into test values(7,8,9); 937c478bd9Sstevel@tonic-gate } 947c478bd9Sstevel@tonic-gate} {1 {no such table: main.test2}} 957c478bd9Sstevel@tonic-gatedo_test trigger4-3.3 { 967c478bd9Sstevel@tonic-gate catchsql { 977c478bd9Sstevel@tonic-gate update test set a=222 where id=1; 987c478bd9Sstevel@tonic-gate } 997c478bd9Sstevel@tonic-gate} {1 {no such table: main.test2}} 1007c478bd9Sstevel@tonic-gatedo_test trigger4-3.4 { 1017c478bd9Sstevel@tonic-gate execsql { 1027c478bd9Sstevel@tonic-gate select * from test1; 1037c478bd9Sstevel@tonic-gate } 1047c478bd9Sstevel@tonic-gate} {1 22 4 5} 1057c478bd9Sstevel@tonic-gatedo_test trigger4-3.5 { 1067c478bd9Sstevel@tonic-gate execsql { 1077c478bd9Sstevel@tonic-gate create table test2(id,b); 1087c478bd9Sstevel@tonic-gate insert into test values(7,8,9); 1097c478bd9Sstevel@tonic-gate select * from test1; 1107c478bd9Sstevel@tonic-gate } 1117c478bd9Sstevel@tonic-gate} {1 22 4 5 7 8} 1127c478bd9Sstevel@tonic-gatedo_test trigger4-3.6 { 1137c478bd9Sstevel@tonic-gate execsql { 1147c478bd9Sstevel@tonic-gate select * from test2; 1157c478bd9Sstevel@tonic-gate } 1167c478bd9Sstevel@tonic-gate} {7 9} 1177c478bd9Sstevel@tonic-gatedo_test trigger4-3.7 { 1187c478bd9Sstevel@tonic-gate db close 1197c478bd9Sstevel@tonic-gate sqlite db test.db 1207c478bd9Sstevel@tonic-gate execsql { 1217c478bd9Sstevel@tonic-gate update test set b=99 where id=7; 1227c478bd9Sstevel@tonic-gate select * from test2; 1237c478bd9Sstevel@tonic-gate } 1247c478bd9Sstevel@tonic-gate} {7 99} 1257c478bd9Sstevel@tonic-gate 1267c478bd9Sstevel@tonic-gateintegrity_check trigger4-4.1 1277c478bd9Sstevel@tonic-gate 1287c478bd9Sstevel@tonic-gatefinish_test 129