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