wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2009 August 24 |
2 | # |
||
3 | # The author disclaims copyright to this source code. In place of |
||
4 | # a legal notice', here is a blessing: |
||
5 | # |
||
6 | # May you do good and not evil. |
||
7 | # May you find forgiveness for yourself and forgive others. |
||
8 | # May you share freely, never taking more than you give. |
||
9 | # |
||
10 | #*********************************************************************** |
||
11 | # |
||
12 | |||
13 | set testdir [file dirname $argv0] |
||
14 | source $testdir/tester.tcl |
||
15 | ifcapable {!trigger} { |
||
16 | finish_test |
||
17 | return |
||
18 | } |
||
19 | |||
20 | #------------------------------------------------------------------------- |
||
21 | # Test organization: |
||
22 | # |
||
23 | # triggerC-1.*: Haphazardly designed trigger related tests that were useful |
||
24 | # during an upgrade of the triggers sub-system. |
||
25 | # |
||
26 | # triggerC-2.*: |
||
27 | # |
||
28 | # triggerC-3.*: |
||
29 | # |
||
30 | # triggerC-4.*: |
||
31 | # |
||
32 | # triggerC-5.*: Test that when recursive triggers are enabled DELETE |
||
33 | # triggers are fired when rows are deleted as part of OR |
||
34 | # REPLACE conflict resolution. And that they are not fired |
||
35 | # if recursive triggers are not enabled. |
||
36 | # |
||
37 | # triggerC-6.*: Test that the recursive_triggers pragma returns correct |
||
38 | # results when invoked without an argument. |
||
39 | # |
||
40 | |||
41 | # Enable recursive triggers for this file. |
||
42 | # |
||
43 | execsql { PRAGMA recursive_triggers = on } |
||
44 | |||
45 | #sqlite3_db_config_lookaside db 0 0 0 |
||
46 | |||
47 | #------------------------------------------------------------------------- |
||
48 | # This block of tests, triggerC-1.*, are not aimed at any specific |
||
49 | # property of the triggers sub-system. They were created to debug |
||
50 | # specific problems while modifying SQLite to support recursive |
||
51 | # triggers. They are left here in case they can help debug the |
||
52 | # same problems again. |
||
53 | # |
||
54 | do_test triggerC-1.1 { |
||
55 | execsql { |
||
56 | CREATE TABLE t1(a, b, c); |
||
57 | CREATE TABLE log(t, a1, b1, c1, a2, b2, c2); |
||
58 | CREATE TRIGGER trig1 BEFORE INSERT ON t1 BEGIN |
||
59 | INSERT INTO log VALUES('before', NULL, NULL, NULL, new.a, new.b, new.c); |
||
60 | END; |
||
61 | CREATE TRIGGER trig2 AFTER INSERT ON t1 BEGIN |
||
62 | INSERT INTO log VALUES('after', NULL, NULL, NULL, new.a, new.b, new.c); |
||
63 | END; |
||
64 | CREATE TRIGGER trig3 BEFORE UPDATE ON t1 BEGIN |
||
65 | INSERT INTO log VALUES('before', old.a,old.b,old.c, new.a,new.b,new.c); |
||
66 | END; |
||
67 | CREATE TRIGGER trig4 AFTER UPDATE ON t1 BEGIN |
||
68 | INSERT INTO log VALUES('after', old.a,old.b,old.c, new.a,new.b,new.c); |
||
69 | END; |
||
70 | |||
71 | CREATE TRIGGER trig5 BEFORE DELETE ON t1 BEGIN |
||
72 | INSERT INTO log VALUES('before', old.a,old.b,old.c, NULL,NULL,NULL); |
||
73 | END; |
||
74 | CREATE TRIGGER trig6 AFTER DELETE ON t1 BEGIN |
||
75 | INSERT INTO log VALUES('after', old.a,old.b,old.c, NULL,NULL,NULL); |
||
76 | END; |
||
77 | } |
||
78 | } {} |
||
79 | do_test triggerC-1.2 { |
||
80 | execsql { |
||
81 | INSERT INTO t1 VALUES('A', 'B', 'C'); |
||
82 | SELECT * FROM log; |
||
83 | } |
||
84 | } {before {} {} {} A B C after {} {} {} A B C} |
||
85 | do_test triggerC-1.3 { |
||
86 | execsql { SELECT * FROM t1 } |
||
87 | } {A B C} |
||
88 | do_test triggerC-1.4 { |
||
89 | execsql { |
||
90 | DELETE FROM log; |
||
91 | UPDATE t1 SET a = 'a'; |
||
92 | SELECT * FROM log; |
||
93 | } |
||
94 | } {before A B C a B C after A B C a B C} |
||
95 | do_test triggerC-1.5 { |
||
96 | execsql { SELECT * FROM t1 } |
||
97 | } {a B C} |
||
98 | do_test triggerC-1.6 { |
||
99 | execsql { |
||
100 | DELETE FROM log; |
||
101 | DELETE FROM t1; |
||
102 | SELECT * FROM log; |
||
103 | } |
||
104 | } {before a B C {} {} {} after a B C {} {} {}} |
||
105 | do_test triggerC-1.7 { |
||
106 | execsql { SELECT * FROM t1 } |
||
107 | } {} |
||
108 | do_test triggerC-1.8 { |
||
109 | execsql { |
||
110 | CREATE TABLE t4(a, b); |
||
111 | CREATE TRIGGER t4t AFTER DELETE ON t4 BEGIN |
||
112 | SELECT RAISE(ABORT, 'delete is not supported'); |
||
113 | END; |
||
114 | } |
||
115 | } {} |
||
116 | do_test triggerC-1.9 { |
||
117 | execsql { INSERT INTO t4 VALUES(1, 2) } |
||
118 | catchsql { DELETE FROM t4 } |
||
119 | } {1 {delete is not supported}} |
||
120 | do_test triggerC-1.10 { |
||
121 | execsql { SELECT * FROM t4 } |
||
122 | } {1 2} |
||
123 | do_test triggerC-1.11 { |
||
124 | execsql { |
||
125 | CREATE TABLE t5 (a primary key, b, c); |
||
126 | INSERT INTO t5 values (1, 2, 3); |
||
127 | CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN |
||
128 | UPDATE OR IGNORE t5 SET a = new.a, c = 10; |
||
129 | END; |
||
130 | } |
||
131 | } {} |
||
132 | do_test triggerC-1.12 { |
||
133 | catchsql { UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 } |
||
134 | } {1 {too many levels of trigger recursion}} |
||
135 | do_test triggerC-1.13 { |
||
136 | execsql { |
||
137 | CREATE TABLE t6(a INTEGER PRIMARY KEY, b); |
||
138 | INSERT INTO t6 VALUES(1, 2); |
||
139 | create trigger r1 after update on t6 for each row begin |
||
140 | SELECT 1; |
||
141 | end; |
||
142 | UPDATE t6 SET a=a; |
||
143 | } |
||
144 | } {} |
||
145 | do_test triggerC-1.14 { |
||
146 | execsql { |
||
147 | DROP TABLE t1; |
||
148 | CREATE TABLE cnt(n); |
||
149 | INSERT INTO cnt VALUES(0); |
||
150 | CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c, d, e); |
||
151 | CREATE INDEX t1cd ON t1(c,d); |
||
152 | CREATE TRIGGER t1r1 AFTER UPDATE ON t1 BEGIN UPDATE cnt SET n=n+1; END; |
||
153 | INSERT INTO t1 VALUES(1,2,3,4,5); |
||
154 | INSERT INTO t1 VALUES(6,7,8,9,10); |
||
155 | INSERT INTO t1 VALUES(11,12,13,14,15); |
||
156 | } |
||
157 | } {} |
||
158 | do_test triggerC-1.15 { |
||
159 | catchsql { UPDATE OR ROLLBACK t1 SET a=100 } |
||
160 | } {1 {PRIMARY KEY must be unique}} |
||
161 | |||
162 | |||
163 | #------------------------------------------------------------------------- |
||
164 | # This block of tests, triggerC-2.*, tests that recursive trigger |
||
165 | # programs (triggers that fire themselves) work. More specifically, |
||
166 | # this block focuses on recursive INSERT triggers. |
||
167 | # |
||
168 | do_test triggerC-2.1.0 { |
||
169 | execsql { |
||
170 | CREATE TABLE t2(a PRIMARY KEY); |
||
171 | } |
||
172 | } {} |
||
173 | |||
174 | foreach {n tdefn rc} { |
||
175 | 1 { |
||
176 | CREATE TRIGGER t2_trig AFTER INSERT ON t2 WHEN (new.a>0) BEGIN |
||
177 | INSERT INTO t2 VALUES(new.a - 1); |
||
178 | END; |
||
179 | } {0 {10 9 8 7 6 5 4 3 2 1 0}} |
||
180 | |||
181 | 2 { |
||
182 | CREATE TRIGGER t2_trig AFTER INSERT ON t2 BEGIN |
||
183 | SELECT CASE WHEN new.a==2 THEN RAISE(IGNORE) ELSE NULL END; |
||
184 | INSERT INTO t2 VALUES(new.a - 1); |
||
185 | END; |
||
186 | } {0 {10 9 8 7 6 5 4 3 2}} |
||
187 | |||
188 | 3 { |
||
189 | CREATE TRIGGER t2_trig BEFORE INSERT ON t2 WHEN (new.a>0) BEGIN |
||
190 | INSERT INTO t2 VALUES(new.a - 1); |
||
191 | END; |
||
192 | } {0 {0 1 2 3 4 5 6 7 8 9 10}} |
||
193 | |||
194 | 4 { |
||
195 | CREATE TRIGGER t2_trig BEFORE INSERT ON t2 BEGIN |
||
196 | SELECT CASE WHEN new.a==2 THEN RAISE(IGNORE) ELSE NULL END; |
||
197 | INSERT INTO t2 VALUES(new.a - 1); |
||
198 | END; |
||
199 | } {0 {3 4 5 6 7 8 9 10}} |
||
200 | |||
201 | 5 { |
||
202 | CREATE TRIGGER t2_trig BEFORE INSERT ON t2 BEGIN |
||
203 | INSERT INTO t2 VALUES(new.a - 1); |
||
204 | END; |
||
205 | } {1 {too many levels of trigger recursion}} |
||
206 | |||
207 | 6 { |
||
208 | CREATE TRIGGER t2_trig AFTER INSERT ON t2 WHEN (new.a>0) BEGIN |
||
209 | INSERT OR IGNORE INTO t2 VALUES(new.a); |
||
210 | END; |
||
211 | } {0 10} |
||
212 | |||
213 | 7 { |
||
214 | CREATE TRIGGER t2_trig BEFORE INSERT ON t2 WHEN (new.a>0) BEGIN |
||
215 | INSERT OR IGNORE INTO t2 VALUES(new.a); |
||
216 | END; |
||
217 | } {1 {too many levels of trigger recursion}} |
||
218 | } { |
||
219 | do_test triggerC-2.1.$n { |
||
220 | catchsql { DROP TRIGGER t2_trig } |
||
221 | execsql { DELETE FROM t2 } |
||
222 | execsql $tdefn |
||
223 | catchsql { |
||
224 | INSERT INTO t2 VALUES(10); |
||
225 | SELECT * FROM t2; |
||
226 | } |
||
227 | } $rc |
||
228 | } |
||
229 | |||
230 | do_test triggerC-2.2 { |
||
231 | execsql " |
||
232 | CREATE TABLE t22(x); |
||
233 | |||
234 | CREATE TRIGGER t22a AFTER INSERT ON t22 BEGIN |
||
235 | INSERT INTO t22 SELECT x + (SELECT max(x) FROM t22) FROM t22; |
||
236 | END; |
||
237 | CREATE TRIGGER t22b BEFORE INSERT ON t22 BEGIN |
||
238 | SELECT CASE WHEN (SELECT count(*) FROM t22) >= [expr $SQLITE_MAX_TRIGGER_DEPTH / 2] |
||
239 | THEN RAISE(IGNORE) |
||
240 | ELSE NULL END; |
||
241 | END; |
||
242 | |||
243 | INSERT INTO t22 VALUES(1); |
||
244 | SELECT count(*) FROM t22; |
||
245 | " |
||
246 | } [list [expr $SQLITE_MAX_TRIGGER_DEPTH / 2]] |
||
247 | |||
248 | do_test triggerC-2.3 { |
||
249 | execsql " |
||
250 | CREATE TABLE t23(x PRIMARY KEY); |
||
251 | |||
252 | CREATE TRIGGER t23a AFTER INSERT ON t23 BEGIN |
||
253 | INSERT INTO t23 VALUES(new.x + 1); |
||
254 | END; |
||
255 | |||
256 | CREATE TRIGGER t23b BEFORE INSERT ON t23 BEGIN |
||
257 | SELECT CASE WHEN new.x>[expr $SQLITE_MAX_TRIGGER_DEPTH / 2] |
||
258 | THEN RAISE(IGNORE) |
||
259 | ELSE NULL END; |
||
260 | END; |
||
261 | |||
262 | INSERT INTO t23 VALUES(1); |
||
263 | SELECT count(*) FROM t23; |
||
264 | " |
||
265 | } [list [expr $SQLITE_MAX_TRIGGER_DEPTH / 2]] |
||
266 | |||
267 | |||
268 | #----------------------------------------------------------------------- |
||
269 | # This block of tests, triggerC-3.*, test that SQLite throws an exception |
||
270 | # when it detects excessive recursion. |
||
271 | # |
||
272 | do_test triggerC-3.1.1 { |
||
273 | execsql { |
||
274 | CREATE TABLE t3(a, b); |
||
275 | CREATE TRIGGER t3i AFTER INSERT ON t3 BEGIN |
||
276 | DELETE FROM t3 WHERE rowid = new.rowid; |
||
277 | END; |
||
278 | CREATE TRIGGER t3d AFTER DELETE ON t3 BEGIN |
||
279 | INSERT INTO t3 VALUES(old.a, old.b); |
||
280 | END; |
||
281 | } |
||
282 | } {} |
||
283 | do_test triggerC-3.1.2 { |
||
284 | catchsql { INSERT INTO t3 VALUES(0,0) } |
||
285 | } {1 {too many levels of trigger recursion}} |
||
286 | do_test triggerC-3.1.3 { |
||
287 | execsql { SELECT * FROM t3 } |
||
288 | } {} |
||
289 | |||
290 | do_test triggerC-3.2.1 { |
||
291 | execsql " |
||
292 | CREATE TABLE t3b(x); |
||
293 | CREATE TRIGGER t3bi AFTER INSERT ON t3b WHEN new.x<[expr $SQLITE_MAX_TRIGGER_DEPTH * 2] BEGIN |
||
294 | INSERT INTO t3b VALUES(new.x+1); |
||
295 | END; |
||
296 | " |
||
297 | catchsql { |
||
298 | INSERT INTO t3b VALUES(1); |
||
299 | } |
||
300 | } {1 {too many levels of trigger recursion}} |
||
301 | do_test triggerC-3.2.2 { |
||
302 | db eval {SELECT * FROM t3b} |
||
303 | } {} |
||
304 | |||
305 | do_test triggerC-3.3.1 { |
||
306 | catchsql " |
||
307 | INSERT INTO t3b VALUES([expr $SQLITE_MAX_TRIGGER_DEPTH + 1]); |
||
308 | " |
||
309 | } {0 {}} |
||
310 | do_test triggerC-3.3.2 { |
||
311 | db eval {SELECT count(*), max(x), min(x) FROM t3b} |
||
312 | } [list $SQLITE_MAX_TRIGGER_DEPTH [expr $SQLITE_MAX_TRIGGER_DEPTH * 2] [expr $SQLITE_MAX_TRIGGER_DEPTH + 1]] |
||
313 | |||
314 | do_test triggerC-3.4.1 { |
||
315 | catchsql " |
||
316 | DELETE FROM t3b; |
||
317 | INSERT INTO t3b VALUES([expr $SQLITE_MAX_TRIGGER_DEPTH - 1]); |
||
318 | " |
||
319 | } {1 {too many levels of trigger recursion}} |
||
320 | do_test triggerC-3.4.2 { |
||
321 | db eval {SELECT count(*), max(x), min(x) FROM t3b} |
||
322 | } {0 {} {}} |
||
323 | |||
324 | do_test triggerC-3.5.1 { |
||
325 | sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH [expr $SQLITE_MAX_TRIGGER_DEPTH / 10] |
||
326 | catchsql " |
||
327 | INSERT INTO t3b VALUES([expr ($SQLITE_MAX_TRIGGER_DEPTH * 2) - ($SQLITE_MAX_TRIGGER_DEPTH / 10) + 1]); |
||
328 | " |
||
329 | } {0 {}} |
||
330 | do_test triggerC-3.5.2 { |
||
331 | db eval {SELECT count(*), max(x), min(x) FROM t3b} |
||
332 | } [list [expr $SQLITE_MAX_TRIGGER_DEPTH / 10] [expr $SQLITE_MAX_TRIGGER_DEPTH * 2] [expr ($SQLITE_MAX_TRIGGER_DEPTH * 2) - ($SQLITE_MAX_TRIGGER_DEPTH / 10) + 1]] |
||
333 | |||
334 | do_test triggerC-3.5.3 { |
||
335 | catchsql " |
||
336 | DELETE FROM t3b; |
||
337 | INSERT INTO t3b VALUES([expr ($SQLITE_MAX_TRIGGER_DEPTH * 2) - ($SQLITE_MAX_TRIGGER_DEPTH / 10)]); |
||
338 | " |
||
339 | } {1 {too many levels of trigger recursion}} |
||
340 | do_test triggerC-3.5.4 { |
||
341 | db eval {SELECT count(*), max(x), min(x) FROM t3b} |
||
342 | } {0 {} {}} |
||
343 | |||
344 | do_test triggerC-3.6.1 { |
||
345 | sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1 |
||
346 | catchsql " |
||
347 | INSERT INTO t3b VALUES([expr $SQLITE_MAX_TRIGGER_DEPTH * 2]); |
||
348 | " |
||
349 | } {0 {}} |
||
350 | do_test triggerC-3.6.2 { |
||
351 | db eval {SELECT count(*), max(x), min(x) FROM t3b} |
||
352 | } [list 1 [expr $SQLITE_MAX_TRIGGER_DEPTH * 2] [expr $SQLITE_MAX_TRIGGER_DEPTH * 2]] |
||
353 | |||
354 | do_test triggerC-3.6.3 { |
||
355 | catchsql " |
||
356 | DELETE FROM t3b; |
||
357 | INSERT INTO t3b VALUES([expr ($SQLITE_MAX_TRIGGER_DEPTH * 2) - 1]); |
||
358 | " |
||
359 | } {1 {too many levels of trigger recursion}} |
||
360 | do_test triggerC-3.6.4 { |
||
361 | db eval {SELECT count(*), max(x), min(x) FROM t3b} |
||
362 | } {0 {} {}} |
||
363 | sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH $SQLITE_MAX_TRIGGER_DEPTH |
||
364 | |||
365 | |||
366 | #----------------------------------------------------------------------- |
||
367 | # This next block of tests, triggerC-4.*, checks that affinity |
||
368 | # transformations and constraint processing is performed at the correct |
||
369 | # times relative to BEFORE and AFTER triggers. |
||
370 | # |
||
371 | # For an INSERT statement, for each row to be inserted: |
||
372 | # |
||
373 | # 1. Apply affinities to non-rowid values to be inserted. |
||
374 | # 2. Fire BEFORE triggers. |
||
375 | # 3. Process constraints. |
||
376 | # 4. Insert new record. |
||
377 | # 5. Fire AFTER triggers. |
||
378 | # |
||
379 | # If the value of the rowid field is to be automatically assigned, it is |
||
380 | # set to -1 in the new.* record. Even if it is explicitly set to NULL |
||
381 | # by the INSERT statement. |
||
382 | # |
||
383 | # For an UPDATE statement, for each row to be deleted: |
||
384 | # |
||
385 | # 1. Apply affinities to non-rowid values to be inserted. |
||
386 | # 2. Fire BEFORE triggers. |
||
387 | # 3. Process constraints. |
||
388 | # 4. Insert new record. |
||
389 | # 5. Fire AFTER triggers. |
||
390 | # |
||
391 | # For a DELETE statement, for each row to be deleted: |
||
392 | # |
||
393 | # 1. Fire BEFORE triggers. |
||
394 | # 2. Remove database record. |
||
395 | # 3. Fire AFTER triggers. |
||
396 | # |
||
397 | # When a numeric value that as an exact integer representation is stored |
||
398 | # in a column with REAL affinity, it is actually stored as an integer. |
||
399 | # These tests check that the typeof() such values is always 'real', |
||
400 | # not 'integer'. |
||
401 | # |
||
402 | # triggerC-4.1.*: Check that affinity transformations are made before |
||
403 | # triggers are invoked. |
||
404 | # |
||
405 | do_test triggerC-4.1.1 { |
||
406 | catchsql { DROP TABLE log } |
||
407 | catchsql { DROP TABLE t4 } |
||
408 | execsql { |
||
409 | CREATE TABLE log(t); |
||
410 | CREATE TABLE t4(a TEXT,b INTEGER,c REAL); |
||
411 | CREATE TRIGGER t4bi BEFORE INSERT ON t4 BEGIN |
||
412 | INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || |
||
413 | new.a || ' ' || typeof(new.a) || ' ' || |
||
414 | new.b || ' ' || typeof(new.b) || ' ' || |
||
415 | new.c || ' ' || typeof(new.c) |
||
416 | ); |
||
417 | END; |
||
418 | CREATE TRIGGER t4ai AFTER INSERT ON t4 BEGIN |
||
419 | INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || |
||
420 | new.a || ' ' || typeof(new.a) || ' ' || |
||
421 | new.b || ' ' || typeof(new.b) || ' ' || |
||
422 | new.c || ' ' || typeof(new.c) |
||
423 | ); |
||
424 | END; |
||
425 | CREATE TRIGGER t4bd BEFORE DELETE ON t4 BEGIN |
||
426 | INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || |
||
427 | old.a || ' ' || typeof(old.a) || ' ' || |
||
428 | old.b || ' ' || typeof(old.b) || ' ' || |
||
429 | old.c || ' ' || typeof(old.c) |
||
430 | ); |
||
431 | END; |
||
432 | CREATE TRIGGER t4ad AFTER DELETE ON t4 BEGIN |
||
433 | INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || |
||
434 | old.a || ' ' || typeof(old.a) || ' ' || |
||
435 | old.b || ' ' || typeof(old.b) || ' ' || |
||
436 | old.c || ' ' || typeof(old.c) |
||
437 | ); |
||
438 | END; |
||
439 | CREATE TRIGGER t4bu BEFORE UPDATE ON t4 BEGIN |
||
440 | INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || |
||
441 | old.a || ' ' || typeof(old.a) || ' ' || |
||
442 | old.b || ' ' || typeof(old.b) || ' ' || |
||
443 | old.c || ' ' || typeof(old.c) |
||
444 | ); |
||
445 | INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || |
||
446 | new.a || ' ' || typeof(new.a) || ' ' || |
||
447 | new.b || ' ' || typeof(new.b) || ' ' || |
||
448 | new.c || ' ' || typeof(new.c) |
||
449 | ); |
||
450 | END; |
||
451 | CREATE TRIGGER t4au AFTER UPDATE ON t4 BEGIN |
||
452 | INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || |
||
453 | old.a || ' ' || typeof(old.a) || ' ' || |
||
454 | old.b || ' ' || typeof(old.b) || ' ' || |
||
455 | old.c || ' ' || typeof(old.c) |
||
456 | ); |
||
457 | INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || |
||
458 | new.a || ' ' || typeof(new.a) || ' ' || |
||
459 | new.b || ' ' || typeof(new.b) || ' ' || |
||
460 | new.c || ' ' || typeof(new.c) |
||
461 | ); |
||
462 | END; |
||
463 | } |
||
464 | } {} |
||
465 | foreach {n insert log} { |
||
466 | |||
467 | 2 { |
||
468 | INSERT INTO t4 VALUES('1', '1', '1'); |
||
469 | DELETE FROM t4; |
||
470 | } { |
||
471 | -1 integer 1 text 1 integer 1.0 real |
||
472 | 1 integer 1 text 1 integer 1.0 real |
||
473 | 1 integer 1 text 1 integer 1.0 real |
||
474 | 1 integer 1 text 1 integer 1.0 real |
||
475 | } |
||
476 | |||
477 | 3 { |
||
478 | INSERT INTO t4(rowid,a,b,c) VALUES(45, 45, 45, 45); |
||
479 | DELETE FROM t4; |
||
480 | } { |
||
481 | 45 integer 45 text 45 integer 45.0 real |
||
482 | 45 integer 45 text 45 integer 45.0 real |
||
483 | 45 integer 45 text 45 integer 45.0 real |
||
484 | 45 integer 45 text 45 integer 45.0 real |
||
485 | } |
||
486 | |||
487 | 4 { |
||
488 | INSERT INTO t4(rowid,a,b,c) VALUES(-42.0, -42.0, -42.0, -42.0); |
||
489 | DELETE FROM t4; |
||
490 | } { |
||
491 | -42 integer -42.0 text -42 integer -42.0 real |
||
492 | -42 integer -42.0 text -42 integer -42.0 real |
||
493 | -42 integer -42.0 text -42 integer -42.0 real |
||
494 | -42 integer -42.0 text -42 integer -42.0 real |
||
495 | } |
||
496 | |||
497 | 5 { |
||
498 | INSERT INTO t4(rowid,a,b,c) VALUES(NULL, -42.4, -42.4, -42.4); |
||
499 | DELETE FROM t4; |
||
500 | } { |
||
501 | -1 integer -42.4 text -42.4 real -42.4 real |
||
502 | 1 integer -42.4 text -42.4 real -42.4 real |
||
503 | 1 integer -42.4 text -42.4 real -42.4 real |
||
504 | 1 integer -42.4 text -42.4 real -42.4 real |
||
505 | } |
||
506 | |||
507 | 6 { |
||
508 | INSERT INTO t4 VALUES(7, 7, 7); |
||
509 | UPDATE t4 SET a=8, b=8, c=8; |
||
510 | } { |
||
511 | -1 integer 7 text 7 integer 7.0 real |
||
512 | 1 integer 7 text 7 integer 7.0 real |
||
513 | 1 integer 7 text 7 integer 7.0 real |
||
514 | 1 integer 8 text 8 integer 8.0 real |
||
515 | 1 integer 7 text 7 integer 7.0 real |
||
516 | 1 integer 8 text 8 integer 8.0 real |
||
517 | } |
||
518 | |||
519 | 7 { |
||
520 | UPDATE t4 SET rowid=2; |
||
521 | } { |
||
522 | 1 integer 8 text 8 integer 8.0 real |
||
523 | 2 integer 8 text 8 integer 8.0 real |
||
524 | 1 integer 8 text 8 integer 8.0 real |
||
525 | 2 integer 8 text 8 integer 8.0 real |
||
526 | } |
||
527 | |||
528 | 8 { |
||
529 | UPDATE t4 SET a='9', b='9', c='9'; |
||
530 | } { |
||
531 | 2 integer 8 text 8 integer 8.0 real |
||
532 | 2 integer 9 text 9 integer 9.0 real |
||
533 | 2 integer 8 text 8 integer 8.0 real |
||
534 | 2 integer 9 text 9 integer 9.0 real |
||
535 | } |
||
536 | |||
537 | 9 { |
||
538 | UPDATE t4 SET a='9.1', b='9.1', c='9.1'; |
||
539 | } { |
||
540 | 2 integer 9 text 9 integer 9.0 real |
||
541 | 2 integer 9.1 text 9.1 real 9.1 real |
||
542 | 2 integer 9 text 9 integer 9.0 real |
||
543 | 2 integer 9.1 text 9.1 real 9.1 real |
||
544 | } |
||
545 | } { |
||
546 | do_test triggerC-4.1.$n { |
||
547 | eval concat [execsql " |
||
548 | DELETE FROM log; |
||
549 | $insert ; |
||
550 | SELECT * FROM log; |
||
551 | "] |
||
552 | } [join $log " "] |
||
553 | } |
||
554 | |||
555 | #------------------------------------------------------------------------- |
||
556 | # This block of tests, triggerC-5.*, test that DELETE triggers are fired |
||
557 | # if a row is deleted as a result of OR REPLACE conflict resolution. |
||
558 | # |
||
559 | do_test triggerC-5.1.0 { |
||
560 | execsql { |
||
561 | DROP TABLE IF EXISTS t5; |
||
562 | CREATE TABLE t5(a INTEGER PRIMARY KEY, b); |
||
563 | CREATE UNIQUE INDEX t5i ON t5(b); |
||
564 | INSERT INTO t5 VALUES(1, 'a'); |
||
565 | INSERT INTO t5 VALUES(2, 'b'); |
||
566 | INSERT INTO t5 VALUES(3, 'c'); |
||
567 | |||
568 | CREATE TABLE t5g(a, b, c); |
||
569 | CREATE TRIGGER t5t BEFORE DELETE ON t5 BEGIN |
||
570 | INSERT INTO t5g VALUES(old.a, old.b, (SELECT count(*) FROM t5)); |
||
571 | END; |
||
572 | } |
||
573 | } {} |
||
574 | foreach {n dml t5g t5} { |
||
575 | 1 "DELETE FROM t5 WHERE a=2" {2 b 3} {1 a 3 c} |
||
576 | 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {2 b 3} {1 a 2 d 3 c} |
||
577 | 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {2 b 3} {1 a 2 c} |
||
578 | 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {2 b 3} {1 a 3 c 4 b} |
||
579 | 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {2 b 3} {1 a 3 b} |
||
580 | 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {2 b 3 3 c 2} {1 a 2 c} |
||
581 | 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 3 2 b 2} {1 b} |
||
582 | } { |
||
583 | do_test triggerC-5.1.$n { |
||
584 | execsql " |
||
585 | BEGIN; |
||
586 | $dml ; |
||
587 | SELECT * FROM t5g; |
||
588 | SELECT * FROM t5; |
||
589 | ROLLBACK; |
||
590 | " |
||
591 | } [concat $t5g $t5] |
||
592 | } |
||
593 | do_test triggerC-5.2.0 { |
||
594 | execsql { |
||
595 | DROP TRIGGER t5t; |
||
596 | CREATE TRIGGER t5t AFTER DELETE ON t5 BEGIN |
||
597 | INSERT INTO t5g VALUES(old.a, old.b, (SELECT count(*) FROM t5)); |
||
598 | END; |
||
599 | } |
||
600 | } {} |
||
601 | foreach {n dml t5g t5} { |
||
602 | 1 "DELETE FROM t5 WHERE a=2" {2 b 2} {1 a 3 c} |
||
603 | 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {2 b 2} {1 a 2 d 3 c} |
||
604 | 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {2 b 2} {1 a 2 c} |
||
605 | 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {2 b 2} {1 a 3 c 4 b} |
||
606 | 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {2 b 2} {1 a 3 b} |
||
607 | 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {2 b 2 3 c 1} {1 a 2 c} |
||
608 | 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 2 2 b 1} {1 b} |
||
609 | } { |
||
610 | do_test triggerC-5.2.$n { |
||
611 | execsql " |
||
612 | BEGIN; |
||
613 | $dml ; |
||
614 | SELECT * FROM t5g; |
||
615 | SELECT * FROM t5; |
||
616 | ROLLBACK; |
||
617 | " |
||
618 | } [concat $t5g $t5] |
||
619 | } |
||
620 | do_test triggerC-5.3.0 { |
||
621 | execsql { PRAGMA recursive_triggers = off } |
||
622 | } {} |
||
623 | foreach {n dml t5g t5} { |
||
624 | 1 "DELETE FROM t5 WHERE a=2" {2 b 2} {1 a 3 c} |
||
625 | 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {} {1 a 2 d 3 c} |
||
626 | 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {} {1 a 2 c} |
||
627 | 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {} {1 a 3 c 4 b} |
||
628 | 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {} {1 a 3 b} |
||
629 | 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {} {1 a 2 c} |
||
630 | 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {} {1 b} |
||
631 | } { |
||
632 | do_test triggerC-5.3.$n { |
||
633 | execsql " |
||
634 | BEGIN; |
||
635 | $dml ; |
||
636 | SELECT * FROM t5g; |
||
637 | SELECT * FROM t5; |
||
638 | ROLLBACK; |
||
639 | " |
||
640 | } [concat $t5g $t5] |
||
641 | } |
||
642 | do_test triggerC-5.3.8 { |
||
643 | execsql { PRAGMA recursive_triggers = on } |
||
644 | } {} |
||
645 | |||
646 | #------------------------------------------------------------------------- |
||
647 | # This block of tests, triggerC-6.*, tests that "PRAGMA recursive_triggers" |
||
648 | # statements return the current value of the recursive triggers flag. |
||
649 | # |
||
650 | do_test triggerC-6.1 { |
||
651 | execsql { PRAGMA recursive_triggers } |
||
652 | } {1} |
||
653 | do_test triggerC-6.2 { |
||
654 | execsql { |
||
655 | PRAGMA recursive_triggers = off; |
||
656 | PRAGMA recursive_triggers; |
||
657 | } |
||
658 | } {0} |
||
659 | do_test triggerC-6.3 { |
||
660 | execsql { |
||
661 | PRAGMA recursive_triggers = on; |
||
662 | PRAGMA recursive_triggers; |
||
663 | } |
||
664 | } {1} |
||
665 | |||
666 | #------------------------------------------------------------------------- |
||
667 | # Test some of the "undefined behaviour" associated with triggers. The |
||
668 | # undefined behaviour occurs when a row being updated or deleted is |
||
669 | # manipulated by a BEFORE trigger. |
||
670 | # |
||
671 | do_test triggerC-7.1 { |
||
672 | execsql { |
||
673 | CREATE TABLE t8(x); |
||
674 | CREATE TABLE t7(a, b); |
||
675 | INSERT INTO t7 VALUES(1, 2); |
||
676 | INSERT INTO t7 VALUES(3, 4); |
||
677 | INSERT INTO t7 VALUES(5, 6); |
||
678 | CREATE TRIGGER t7t BEFORE UPDATE ON t7 BEGIN |
||
679 | DELETE FROM t7 WHERE a = 1; |
||
680 | END; |
||
681 | CREATE TRIGGER t7ta AFTER UPDATE ON t7 BEGIN |
||
682 | INSERT INTO t8 VALUES('after fired ' || old.rowid || '->' || new.rowid); |
||
683 | END; |
||
684 | } |
||
685 | } {} |
||
686 | do_test triggerC-7.2 { |
||
687 | execsql { |
||
688 | BEGIN; |
||
689 | UPDATE t7 SET b=7 WHERE a = 5; |
||
690 | SELECT * FROM t7; |
||
691 | SELECT * FROM t8; |
||
692 | ROLLBACK; |
||
693 | } |
||
694 | } {3 4 5 7 {after fired 3->3}} |
||
695 | do_test triggerC-7.3 { |
||
696 | execsql { |
||
697 | BEGIN; |
||
698 | UPDATE t7 SET b=7 WHERE a = 1; |
||
699 | SELECT * FROM t7; |
||
700 | SELECT * FROM t8; |
||
701 | ROLLBACK; |
||
702 | } |
||
703 | } {3 4 5 6} |
||
704 | |||
705 | do_test triggerC-7.4 { |
||
706 | execsql { |
||
707 | DROP TRIGGER t7t; |
||
708 | CREATE TRIGGER t7t BEFORE UPDATE ON t7 WHEN (old.rowid!=1 OR new.rowid!=8) |
||
709 | BEGIN |
||
710 | UPDATE t7 set rowid = 8 WHERE rowid=1; |
||
711 | END; |
||
712 | } |
||
713 | } {} |
||
714 | do_test triggerC-7.5 { |
||
715 | execsql { |
||
716 | BEGIN; |
||
717 | UPDATE t7 SET b=7 WHERE a = 5; |
||
718 | SELECT rowid, * FROM t7; |
||
719 | SELECT * FROM t8; |
||
720 | ROLLBACK; |
||
721 | } |
||
722 | } {2 3 4 3 5 7 8 1 2 {after fired 1->8} {after fired 3->3}} |
||
723 | do_test triggerC-7.6 { |
||
724 | execsql { |
||
725 | BEGIN; |
||
726 | UPDATE t7 SET b=7 WHERE a = 1; |
||
727 | SELECT rowid, * FROM t7; |
||
728 | SELECT * FROM t8; |
||
729 | ROLLBACK; |
||
730 | } |
||
731 | } {2 3 4 3 5 6 8 1 2 {after fired 1->8}} |
||
732 | |||
733 | do_test triggerC-7.7 { |
||
734 | execsql { |
||
735 | DROP TRIGGER t7t; |
||
736 | DROP TRIGGER t7ta; |
||
737 | CREATE TRIGGER t7t BEFORE DELETE ON t7 BEGIN |
||
738 | UPDATE t7 set rowid = 8 WHERE rowid=1; |
||
739 | END; |
||
740 | CREATE TRIGGER t7ta AFTER DELETE ON t7 BEGIN |
||
741 | INSERT INTO t8 VALUES('after fired ' || old.rowid); |
||
742 | END; |
||
743 | } |
||
744 | } {} |
||
745 | do_test triggerC-7.8 { |
||
746 | execsql { |
||
747 | BEGIN; |
||
748 | DELETE FROM t7 WHERE a = 3; |
||
749 | SELECT rowid, * FROM t7; |
||
750 | SELECT * FROM t8; |
||
751 | ROLLBACK; |
||
752 | } |
||
753 | } {3 5 6 8 1 2 {after fired 2}} |
||
754 | do_test triggerC-7.9 { |
||
755 | execsql { |
||
756 | BEGIN; |
||
757 | DELETE FROM t7 WHERE a = 1; |
||
758 | SELECT rowid, * FROM t7; |
||
759 | SELECT * FROM t8; |
||
760 | ROLLBACK; |
||
761 | } |
||
762 | } {2 3 4 3 5 6 8 1 2} |
||
763 | |||
764 | # Ticket [e25d9ea771febc9c311928c1c01c3163dcb26643] |
||
765 | # |
||
766 | do_test triggerC-9.1 { |
||
767 | execsql { |
||
768 | CREATE TABLE t9(a,b); |
||
769 | CREATE INDEX t9b ON t9(b); |
||
770 | INSERT INTO t9 VALUES(1,0); |
||
771 | INSERT INTO t9 VALUES(2,1); |
||
772 | INSERT INTO t9 VALUES(3,2); |
||
773 | INSERT INTO t9 SELECT a+3, a+2 FROM t9; |
||
774 | INSERT INTO t9 SELECT a+6, a+5 FROM t9; |
||
775 | SELECT a FROM t9 ORDER BY a; |
||
776 | } |
||
777 | } {1 2 3 4 5 6 7 8 9 10 11 12} |
||
778 | do_test triggerC-9.2 { |
||
779 | execsql { |
||
780 | CREATE TRIGGER t9r1 AFTER DELETE ON t9 BEGIN |
||
781 | DELETE FROM t9 WHERE b=old.a; |
||
782 | END; |
||
783 | DELETE FROM t9 WHERE b=4; |
||
784 | SELECT a FROM t9 ORDER BY a; |
||
785 | } |
||
786 | } {1 2 3 4} |
||
787 | |||
788 | # At one point (between versions 3.6.18 and 3.6.20 inclusive), an UPDATE |
||
789 | # that fired a BEFORE trigger that itself updated the same row as the |
||
790 | # statement causing it to fire was causing a strange side-effect: The |
||
791 | # values updated by the statement within the trigger were being overwritten |
||
792 | # by the values in the new.* array, even if those values were not |
||
793 | # themselves written by the parent UPDATE statement. |
||
794 | # |
||
795 | # Technically speaking this was not a bug. The SQLite documentation says |
||
796 | # that if a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes the |
||
797 | # row that the parent statement is operating on the results are undefined. |
||
798 | # But as of 3.6.21 behaviour is restored to the way it was in versions |
||
799 | # 3.6.17 and earlier to avoid causing unnecessary difficulties. |
||
800 | # |
||
801 | do_test triggerC-10.1 { |
||
802 | execsql { |
||
803 | CREATE TABLE t10(a, updatecnt DEFAULT 0); |
||
804 | CREATE TRIGGER t10_bu BEFORE UPDATE OF a ON t10 BEGIN |
||
805 | UPDATE t10 SET updatecnt = updatecnt+1 WHERE rowid = old.rowid; |
||
806 | END; |
||
807 | INSERT INTO t10(a) VALUES('hello'); |
||
808 | } |
||
809 | |||
810 | # Before the problem was fixed, table t10 would contain the tuple |
||
811 | # (world, 0) after running the following script (because the value |
||
812 | # 1 written to column "updatecnt" was clobbered by the old value 0). |
||
813 | # |
||
814 | execsql { |
||
815 | UPDATE t10 SET a = 'world'; |
||
816 | SELECT * FROM t10; |
||
817 | } |
||
818 | } {world 1} |
||
819 | |||
820 | do_test triggerC-10.2 { |
||
821 | execsql { |
||
822 | UPDATE t10 SET a = 'tcl', updatecnt = 5; |
||
823 | SELECT * FROM t10; |
||
824 | } |
||
825 | } {tcl 5} |
||
826 | |||
827 | do_test triggerC-10.3 { |
||
828 | execsql { |
||
829 | CREATE TABLE t11( |
||
830 | c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, |
||
831 | c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, |
||
832 | c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, |
||
833 | c31, c32, c33, c34, c35, c36, c37, c38, c39, c40 |
||
834 | ); |
||
835 | |||
836 | CREATE TRIGGER t11_bu BEFORE UPDATE OF c1 ON t11 BEGIN |
||
837 | UPDATE t11 SET c31 = c31+1, c32=c32+1 WHERE rowid = old.rowid; |
||
838 | END; |
||
839 | |||
840 | INSERT INTO t11 VALUES( |
||
841 | 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, |
||
842 | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, |
||
843 | 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, |
||
844 | 31, 32, 33, 34, 35, 36, 37, 38, 39, 40 |
||
845 | ); |
||
846 | } |
||
847 | |||
848 | # Before the problem was fixed, table t10 would contain the tuple |
||
849 | # (world, 0) after running the following script (because the value |
||
850 | # 1 written to column "updatecnt" was clobbered by the old value 0). |
||
851 | # |
||
852 | execsql { |
||
853 | UPDATE t11 SET c4=35, c33=22, c1=5; |
||
854 | SELECT * FROM t11; |
||
855 | } |
||
856 | } {5 2 3 35 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 32 33 22 34 35 36 37 38 39 40} |
||
857 | |||
858 | #------------------------------------------------------------------------- |
||
859 | # Test that bug [371bab5d65] has been fixed. BEFORE INSERT and INSTEAD OF |
||
860 | # INSERT triggers with the DEFAULT VALUES INSERT syntax. |
||
861 | # |
||
862 | do_test triggerC-11.0 { |
||
863 | catchsql { DROP TABLE log } |
||
864 | execsql { CREATE TABLE log(a, b) } |
||
865 | } {} |
||
866 | |||
867 | foreach {testno tbl defaults} { |
||
868 | 1 "CREATE TABLE t1(a, b)" {{} {}} |
||
869 | 2 "CREATE TABLE t1(a DEFAULT 1, b DEFAULT 'abc')" {1 abc} |
||
870 | 3 "CREATE TABLE t1(a, b DEFAULT 4.5)" {{} 4.5} |
||
871 | } { |
||
872 | do_test triggerC-11.$testno.1 { |
||
873 | catchsql { DROP TABLE t1 } |
||
874 | execsql { DELETE FROM log } |
||
875 | execsql $tbl |
||
876 | execsql { |
||
877 | CREATE TRIGGER tt1 BEFORE INSERT ON t1 BEGIN |
||
878 | INSERT INTO log VALUES(new.a, new.b); |
||
879 | END; |
||
880 | INSERT INTO t1 DEFAULT VALUES; |
||
881 | SELECT * FROM log; |
||
882 | } |
||
883 | } $defaults |
||
884 | |||
885 | do_test triggerC-11.$testno.2 { |
||
886 | execsql { DELETE FROM log } |
||
887 | execsql { |
||
888 | CREATE TRIGGER tt2 AFTER INSERT ON t1 BEGIN |
||
889 | INSERT INTO log VALUES(new.a, new.b); |
||
890 | END; |
||
891 | INSERT INTO t1 DEFAULT VALUES; |
||
892 | SELECT * FROM log; |
||
893 | } |
||
894 | } [concat $defaults $defaults] |
||
895 | |||
896 | do_test triggerC-11.$testno.3 { |
||
897 | execsql { DROP TRIGGER tt1 } |
||
898 | execsql { DELETE FROM log } |
||
899 | execsql { |
||
900 | INSERT INTO t1 DEFAULT VALUES; |
||
901 | SELECT * FROM log; |
||
902 | } |
||
903 | } $defaults |
||
904 | } |
||
905 | do_test triggerC-11.4 { |
||
906 | catchsql { DROP TABLE t2 } |
||
907 | execsql { |
||
908 | DELETE FROM log; |
||
909 | CREATE TABLE t2(a, b); |
||
910 | CREATE VIEW v2 AS SELECT * FROM t2; |
||
911 | CREATE TRIGGER tv2 INSTEAD OF INSERT ON v2 BEGIN |
||
912 | INSERT INTO log VALUES(new.a, new.b); |
||
913 | END; |
||
914 | INSERT INTO v2 DEFAULT VALUES; |
||
915 | SELECT a, b, a IS NULL, b IS NULL FROM log; |
||
916 | } |
||
917 | } {{} {} 1 1} |
||
918 | |||
919 | do_test triggerC-12.1 { |
||
920 | db close |
||
921 | file delete -force test.db |
||
922 | sqlite3 db test.db |
||
923 | |||
924 | execsql { |
||
925 | CREATE TABLE t1(a, b); |
||
926 | INSERT INTO t1 VALUES(1, 2); |
||
927 | INSERT INTO t1 VALUES(3, 4); |
||
928 | INSERT INTO t1 VALUES(5, 6); |
||
929 | CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1 ; END ; |
||
930 | SELECT count(*) FROM sqlite_master; |
||
931 | } |
||
932 | } {2} |
||
933 | do_test triggerC-12.2 { |
||
934 | db eval { SELECT * FROM t1 } { |
||
935 | if {$a == 3} { execsql { DROP TRIGGER tr1 } } |
||
936 | } |
||
937 | execsql { SELECT count(*) FROM sqlite_master } |
||
938 | } {1} |
||
939 | |||
940 | do_execsql_test triggerC-13.1 { |
||
941 | PRAGMA recursive_triggers = ON; |
||
942 | CREATE TABLE t12(a, b); |
||
943 | INSERT INTO t12 VALUES(1, 2); |
||
944 | CREATE TRIGGER tr12 AFTER UPDATE ON t12 BEGIN |
||
945 | UPDATE t12 SET a=new.a+1, b=new.b+1; |
||
946 | END; |
||
947 | } {} |
||
948 | do_catchsql_test triggerC-13.2 { |
||
949 | UPDATE t12 SET a=a+1, b=b+1; |
||
950 | } {1 {too many levels of trigger recursion}} |
||
951 | |||
952 | |||
953 | |||
954 | finish_test |