wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
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