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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
# 2020 July 14
#
# 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.
#
#***********************************************************************
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix triggerupfrom
do_execsql_test 1.0 {
CREATE TABLE map(k, v);
INSERT INTO map VALUES(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four');
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN
UPDATE t1 SET c = v FROM map WHERE k=new.a AND a=new.a;
END;
}
do_execsql_test 1.1 {
INSERT INTO t1(a) VALUES(1);
}
do_execsql_test 1.2 {
SELECT a, c FROM t1 ORDER BY a;
} {1 one}
do_execsql_test 1.3 {
INSERT INTO t1(a) VALUES(2), (3), (4), (5);
SELECT a, c FROM t1 ORDER BY a;
} {1 one 2 two 3 three 4 four 5 {}}
forcedelete test.db2
do_execsql_test 2.0 {
ATTACH 'test.db2' AS aux;
CREATE TABLE aux.t3(x, y);
INSERT INTO aux.t3 VALUES('x', 'y');
}
do_catchsql_test 2.1 {
CREATE TRIGGER tr2 AFTER INSERT ON t1 BEGIN
UPDATE t1 SET b = y FROM aux.t3 WHERE k=new.a;
END;
} {1 {trigger tr2 cannot reference objects in database aux}}
do_execsql_test 2.2 {
CREATE TEMP TRIGGER tr2 AFTER INSERT ON t1 BEGIN
UPDATE t1 SET b = y FROM aux.t3 WHERE a=new.a;
END;
INSERT INTO t1(a) VALUES(10), (20);
SELECT * FROM t1;
} {
1 {} one
2 {} two
3 {} three
4 {} four
5 {} {}
10 y {}
20 y {}
}
do_execsql_test 2.3 {
CREATE TABLE link(f, t);
INSERT INTO link VALUES(5, 2), (20, 10), (2, 1);
CREATE TRIGGER tr3 BEFORE DELETE ON t1 BEGIN
UPDATE t1 SET b=coalesce(old.b,old.c) FROM main.link WHERE a=t AND old.a=f;
END;
DELETE FROM t1 WHERE a=2;
SELECT * FROM t1;
} {
1 two one
3 {} three
4 {} four
5 {} {}
10 y {}
20 y {}
}
db close
sqlite3 db ""
do_catchsql_test 2.4 {
ATTACH 'test.db' AS yyy;
SELECT * FROM t1;
} {1 {malformed database schema (tr3) - trigger tr3 cannot reference objects in database main}}
#-------------------------------------------------------------------------
reset_db
forcedelete test.db2
do_execsql_test 3.0 {
CREATE TABLE mmm(x, y);
INSERT INTO mmm VALUES(1, 'one');
INSERT INTO mmm VALUES(2, 'two');
INSERT INTO mmm VALUES(3, 'three');
ATTACH 'test.db2' AS aux;
CREATE TABLE aux.t1(a, b);
CREATE TABLE aux.mmm(x, y);
INSERT INTO aux.mmm VALUES(1, 'ONE');
INSERT INTO aux.mmm VALUES(2, 'TWO');
INSERT INTO aux.mmm VALUES(3, 'THREE');
CREATE TRIGGER aux.ttt AFTER INSERT ON t1 BEGIN
UPDATE t1 SET b=y FROM mmm WHERE x=new.a AND a=new.a;
END;
INSERT INTO t1(a) VALUES (2);
SELECT * FROM t1;
} {2 TWO}
#-------------------------------------------------------------------------
# Test that INSTEAD OF UPDATE triggers on views work with UPDATE...FROM
# statements. Including, if the library is built with ENABLE_HIDDEN_COLUMNS,
# that they work correctly on views with hidden columns.
#
reset_db
do_execsql_test 4.0 {
CREATE TABLE t1(k, a, b);
INSERT INTO t1 VALUES('a', 1, 'one');
INSERT INTO t1 VALUES('b', 2, 'two');
INSERT INTO t1 VALUES('c', 3, 'three');
INSERT INTO t1 VALUES('d', 4, 'four');
CREATE TABLE log(x);
CREATE VIEW v1 AS SELECT k, a, b AS __hidden__b FROM t1;
CREATE TRIGGER tr1 INSTEAD OF UPDATE ON v1 BEGIN
INSERT INTO log VALUES(
'('||old.a||','||old.__hidden__b||')->('||new.a||','||new.__hidden__b||')'
);
END;
}
ifcapable hiddencolumns {
do_execsql_test 4.1-hc-enabled {
SELECT * FROM v1
} {a 1 b 2 c 3 d 4}
} else {
do_execsql_test 4.1-hc-disabled {
SELECT * FROM v1
} {a 1 one b 2 two c 3 three d 4 four}
}
do_execsql_test 4.2 {
UPDATE v1 SET a='xyz' WHERE k IN ('a', 'c');
SELECT * FROM log;
DELETE FROM log;
} {
(1,one)->(xyz,one)
(3,three)->(xyz,three)
}
do_execsql_test 4.3 {
CREATE TABLE map(k, v);
INSERT INTO map VALUES('b', 'twelve');
INSERT INTO map VALUES('d', 'fourteen');
UPDATE v1 SET a=map.v FROM map WHERE v1.k=map.k;
SELECT * FROM log;
DELETE FROM log;
} {
(2,two)->(twelve,two)
(4,four)->(fourteen,four)
}
finish_test
|
