wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # The author disclaims copyright to this source code. In place of
2 # a legal notice, here is a blessing:
3 #
4 # May you do good and not evil.
5 # May you find forgiveness for yourself and forgive others.
6 # May you share freely, never taking more than you give.
7 #
8 #***********************************************************************
9 #
10 # Regression testing of FOR EACH ROW table triggers
11 #
12 # 1. Trigger execution order tests.
13 # These tests ensure that BEFORE and AFTER triggers are fired at the correct
14 # times relative to each other and the triggering statement.
15 #
16 # trigger2-1.1.*: ON UPDATE trigger execution model.
17 # trigger2-1.2.*: DELETE trigger execution model.
18 # trigger2-1.3.*: INSERT trigger execution model.
19 #
20 # 2. Trigger program execution tests.
21 # These tests ensure that trigger programs execute correctly (ie. that a
22 # trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT
23 # statements, and combinations thereof).
24 #
25 # 3. Selective trigger execution
26 # This tests that conditional triggers (ie. UPDATE OF triggers and triggers
27 # with WHEN clauses) are fired only fired when they are supposed to be.
28 #
29 # trigger2-3.1: UPDATE OF triggers
30 # trigger2-3.2: WHEN clause
31 #
32 # 4. Cascaded trigger execution
33 # Tests that trigger-programs may cause other triggers to fire. Also that a
34 # trigger-program is never executed recursively.
35 #
36 # trigger2-4.1: Trivial cascading trigger
37 # trigger2-4.2: Trivial recursive trigger handling
38 #
39 # 5. Count changes behaviour.
40 # Verify that rows altered by triggers are not included in the return value
41 # of the "count changes" interface.
42 #
43 # 6. ON CONFLICT clause handling
44 # trigger2-6.1[a-f]: INSERT statements
45 # trigger2-6.2[a-f]: UPDATE statements
46 #
47 # 7. & 8. Triggers on views fire correctly.
48 #
49  
50 set testdir [file dirname $argv0]
51 source $testdir/tester.tcl
52 ifcapable {!trigger} {
53 finish_test
54 return
55 }
56  
57 # The tests in this file were written before SQLite supported recursive
58 # trigger invocation, and some tests depend on that to pass. So disable
59 # recursive triggers for this file.
60 catchsql { pragma recursive_triggers = off }
61  
62 # 1.
63 ifcapable subquery {
64 set ii 0
65 set tbl_definitions [list \
66 {CREATE TABLE tbl (a, b);} \
67 {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} \
68 {CREATE TABLE tbl (a, b PRIMARY KEY);} \
69 {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} \
70 ]
71 ifcapable tempdb {
72 lappend tbl_definitions \
73 {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);}
74 lappend tbl_definitions {CREATE TEMP TABLE tbl (a, b);}
75 lappend tbl_definitions \
76 {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);}
77 }
78 foreach tbl_defn $tbl_definitions {
79 incr ii
80 catchsql { DROP INDEX tbl_idx; }
81 catchsql {
82 DROP TABLE rlog;
83 DROP TABLE clog;
84 DROP TABLE tbl;
85 DROP TABLE other_tbl;
86 }
87  
88 execsql $tbl_defn
89  
90 execsql {
91 INSERT INTO tbl VALUES(1, 2);
92 INSERT INTO tbl VALUES(3, 4);
93  
94 CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
95 CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
96  
97 CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW
98 BEGIN
99 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
100 old.a, old.b,
101 (SELECT coalesce(sum(a),0) FROM tbl),
102 (SELECT coalesce(sum(b),0) FROM tbl),
103 new.a, new.b);
104 END;
105  
106 CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW
107 BEGIN
108 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
109 old.a, old.b,
110 (SELECT coalesce(sum(a),0) FROM tbl),
111 (SELECT coalesce(sum(b),0) FROM tbl),
112 new.a, new.b);
113 END;
114  
115 CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
116 WHEN old.a = 1
117 BEGIN
118 INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog),
119 old.a, old.b,
120 (SELECT coalesce(sum(a),0) FROM tbl),
121 (SELECT coalesce(sum(b),0) FROM tbl),
122 new.a, new.b);
123 END;
124 }
125  
126 do_test trigger2-1.$ii.1 {
127 set r {}
128 foreach v [execsql {
129 UPDATE tbl SET a = a * 10, b = b * 10;
130 SELECT * FROM rlog ORDER BY idx;
131 SELECT * FROM clog ORDER BY idx;
132 }] {
133 lappend r [expr {int($v)}]
134 }
135 set r
136 } [list 1 1 2 4 6 10 20 \
137 2 1 2 13 24 10 20 \
138 3 3 4 13 24 30 40 \
139 4 3 4 40 60 30 40 \
140 1 1 2 13 24 10 20 ]
141  
142 execsql {
143 DELETE FROM rlog;
144 DELETE FROM tbl;
145 INSERT INTO tbl VALUES (100, 100);
146 INSERT INTO tbl VALUES (300, 200);
147 CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
148 BEGIN
149 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
150 old.a, old.b,
151 (SELECT coalesce(sum(a),0) FROM tbl),
152 (SELECT coalesce(sum(b),0) FROM tbl),
153 0, 0);
154 END;
155  
156 CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
157 BEGIN
158 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
159 old.a, old.b,
160 (SELECT coalesce(sum(a),0) FROM tbl),
161 (SELECT coalesce(sum(b),0) FROM tbl),
162 0, 0);
163 END;
164 }
165 do_test trigger2-1.$ii.2 {
166 set r {}
167 foreach v [execsql {
168 DELETE FROM tbl;
169 SELECT * FROM rlog;
170 }] {
171 lappend r [expr {int($v)}]
172 }
173 set r
174 } [list 1 100 100 400 300 0 0 \
175 2 100 100 300 200 0 0 \
176 3 300 200 300 200 0 0 \
177 4 300 200 0 0 0 0 ]
178  
179 execsql {
180 DELETE FROM rlog;
181 CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
182 BEGIN
183 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
184 0, 0,
185 (SELECT coalesce(sum(a),0) FROM tbl),
186 (SELECT coalesce(sum(b),0) FROM tbl),
187 new.a, new.b);
188 END;
189  
190 CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
191 BEGIN
192 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
193 0, 0,
194 (SELECT coalesce(sum(a),0) FROM tbl),
195 (SELECT coalesce(sum(b),0) FROM tbl),
196 new.a, new.b);
197 END;
198 }
199 do_test trigger2-1.$ii.3 {
200 execsql {
201  
202 CREATE TABLE other_tbl(a, b);
203 INSERT INTO other_tbl VALUES(1, 2);
204 INSERT INTO other_tbl VALUES(3, 4);
205 -- INSERT INTO tbl SELECT * FROM other_tbl;
206 INSERT INTO tbl VALUES(5, 6);
207 DROP TABLE other_tbl;
208  
209 SELECT * FROM rlog;
210 }
211 } [list 1 0 0 0 0 5 6 \
212 2 0 0 5 6 5 6 ]
213  
214 integrity_check trigger2-1.$ii.4
215 }
216 catchsql {
217 DROP TABLE rlog;
218 DROP TABLE clog;
219 DROP TABLE tbl;
220 DROP TABLE other_tbl;
221 }
222 }
223  
224 # 2.
225 set ii 0
226 foreach tr_program {
227 {UPDATE tbl SET b = old.b;}
228 {INSERT INTO log VALUES(new.c, 2, 3);}
229 {DELETE FROM log WHERE a = 1;}
230 {INSERT INTO tbl VALUES(500, new.b * 10, 700);
231 UPDATE tbl SET c = old.c;
232 DELETE FROM log;}
233 {INSERT INTO log select * from tbl;}
234 } {
235 foreach test_varset [ list \
236 {
237 set statement {UPDATE tbl SET c = 10 WHERE a = 1;}
238 set prep {INSERT INTO tbl VALUES(1, 2, 3);}
239 set newC 10
240 set newB 2
241 set newA 1
242 set oldA 1
243 set oldB 2
244 set oldC 3
245 } \
246 {
247 set statement {DELETE FROM tbl WHERE a = 1;}
248 set prep {INSERT INTO tbl VALUES(1, 2, 3);}
249 set oldA 1
250 set oldB 2
251 set oldC 3
252 } \
253 {
254 set statement {INSERT INTO tbl VALUES(1, 2, 3);}
255 set newA 1
256 set newB 2
257 set newC 3
258 }
259 ] \
260 {
261 set statement {}
262 set prep {}
263 set newA {''}
264 set newB {''}
265 set newC {''}
266 set oldA {''}
267 set oldB {''}
268 set oldC {''}
269  
270 incr ii
271  
272 eval $test_varset
273  
274 set statement_type [string range $statement 0 5]
275 set tr_program_fixed $tr_program
276 if {$statement_type == "DELETE"} {
277 regsub -all new\.a $tr_program_fixed {''} tr_program_fixed
278 regsub -all new\.b $tr_program_fixed {''} tr_program_fixed
279 regsub -all new\.c $tr_program_fixed {''} tr_program_fixed
280 }
281 if {$statement_type == "INSERT"} {
282 regsub -all old\.a $tr_program_fixed {''} tr_program_fixed
283 regsub -all old\.b $tr_program_fixed {''} tr_program_fixed
284 regsub -all old\.c $tr_program_fixed {''} tr_program_fixed
285 }
286  
287  
288 set tr_program_cooked $tr_program
289 regsub -all new\.a $tr_program_cooked $newA tr_program_cooked
290 regsub -all new\.b $tr_program_cooked $newB tr_program_cooked
291 regsub -all new\.c $tr_program_cooked $newC tr_program_cooked
292 regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked
293 regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked
294 regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked
295  
296 catchsql {
297 DROP TABLE tbl;
298 DROP TABLE log;
299 }
300  
301 execsql {
302 CREATE TABLE tbl(a PRIMARY KEY, b, c);
303 CREATE TABLE log(a, b, c);
304 }
305  
306 set query {SELECT * FROM tbl; SELECT * FROM log;}
307 set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\
308 INSERT INTO log VALUES(10, 20, 30);"
309  
310 # Check execution of BEFORE programs:
311  
312 set before_data [ execsql "$prep $tr_program_cooked $statement $query" ]
313  
314 execsql "DELETE FROM tbl; DELETE FROM log; $prep";
315 execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\
316 ON tbl BEGIN $tr_program_fixed END;"
317  
318 do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data
319  
320 execsql "DROP TRIGGER the_trigger;"
321 execsql "DELETE FROM tbl; DELETE FROM log;"
322  
323 # Check execution of AFTER programs
324 set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]
325  
326 execsql "DELETE FROM tbl; DELETE FROM log; $prep";
327 execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\
328 ON tbl BEGIN $tr_program_fixed END;"
329  
330 do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data
331 execsql "DROP TRIGGER the_trigger;"
332  
333 integrity_check trigger2-2.$ii-integrity
334 }
335 }
336 catchsql {
337 DROP TABLE tbl;
338 DROP TABLE log;
339 }
340  
341 # 3.
342  
343 # trigger2-3.1: UPDATE OF triggers
344 execsql {
345 CREATE TABLE tbl (a, b, c, d);
346 CREATE TABLE log (a);
347 INSERT INTO log VALUES (0);
348 INSERT INTO tbl VALUES (0, 0, 0, 0);
349 INSERT INTO tbl VALUES (1, 0, 0, 0);
350 CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl
351 BEGIN
352 UPDATE log SET a = a + 1;
353 END;
354 }
355 do_test trigger2-3.1 {
356 execsql {
357 UPDATE tbl SET b = 1, c = 10; -- 2
358 UPDATE tbl SET b = 10; -- 0
359 UPDATE tbl SET d = 4 WHERE a = 0; --1
360 UPDATE tbl SET a = 4, b = 10; --0
361 SELECT * FROM log;
362 }
363 } {3}
364 execsql {
365 DROP TABLE tbl;
366 DROP TABLE log;
367 }
368  
369 # trigger2-3.2: WHEN clause
370 set when_triggers [list {t1 BEFORE INSERT ON tbl WHEN new.a > 20}]
371 ifcapable subquery {
372 lappend when_triggers \
373 {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0}
374 }
375  
376 execsql {
377 CREATE TABLE tbl (a, b, c, d);
378 CREATE TABLE log (a);
379 INSERT INTO log VALUES (0);
380 }
381  
382 foreach trig $when_triggers {
383 execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"
384 }
385  
386 ifcapable subquery {
387 set t232 {1 0 1}
388 } else {
389 set t232 {0 0 1}
390 }
391 do_test trigger2-3.2 {
392 execsql {
393  
394 INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1 (ifcapable subquery)
395 SELECT * FROM log;
396 UPDATE log SET a = 0;
397  
398 INSERT INTO tbl VALUES(0, 0, 0, 0); -- 0
399 SELECT * FROM log;
400 UPDATE log SET a = 0;
401  
402 INSERT INTO tbl VALUES(200, 0, 0, 0); -- 1
403 SELECT * FROM log;
404 UPDATE log SET a = 0;
405 }
406 } $t232
407 execsql {
408 DROP TABLE tbl;
409 DROP TABLE log;
410 }
411 integrity_check trigger2-3.3
412  
413 # Simple cascaded trigger
414 execsql {
415 CREATE TABLE tblA(a, b);
416 CREATE TABLE tblB(a, b);
417 CREATE TABLE tblC(a, b);
418  
419 CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN
420 INSERT INTO tblB values(new.a, new.b);
421 END;
422  
423 CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN
424 INSERT INTO tblC values(new.a, new.b);
425 END;
426 }
427 do_test trigger2-4.1 {
428 execsql {
429 INSERT INTO tblA values(1, 2);
430 SELECT * FROM tblA;
431 SELECT * FROM tblB;
432 SELECT * FROM tblC;
433 }
434 } {1 2 1 2 1 2}
435 execsql {
436 DROP TABLE tblA;
437 DROP TABLE tblB;
438 DROP TABLE tblC;
439 }
440  
441 # Simple recursive trigger
442 execsql {
443 CREATE TABLE tbl(a, b, c);
444 CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl
445 BEGIN
446 INSERT INTO tbl VALUES (new.a, new.b, new.c);
447 END;
448 }
449 do_test trigger2-4.2 {
450 execsql {
451 INSERT INTO tbl VALUES (1, 2, 3);
452 select * from tbl;
453 }
454 } {1 2 3 1 2 3}
455 execsql {
456 DROP TABLE tbl;
457 }
458  
459 # 5.
460 execsql {
461 CREATE TABLE tbl(a, b, c);
462 CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl
463 BEGIN
464 INSERT INTO tbl VALUES (1, 2, 3);
465 INSERT INTO tbl VALUES (2, 2, 3);
466 UPDATE tbl set b = 10 WHERE a = 1;
467 DELETE FROM tbl WHERE a = 1;
468 DELETE FROM tbl;
469 END;
470 }
471 do_test trigger2-5 {
472 execsql {
473 INSERT INTO tbl VALUES(100, 200, 300);
474 }
475 db changes
476 } {1}
477 execsql {
478 DROP TABLE tbl;
479 }
480  
481 ifcapable conflict {
482 # Handling of ON CONFLICT by INSERT statements inside triggers
483 execsql {
484 CREATE TABLE tbl (a primary key, b, c);
485 CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN
486 INSERT OR IGNORE INTO tbl values (new.a, 0, 0);
487 END;
488 }
489 do_test trigger2-6.1a {
490 execsql {
491 BEGIN;
492 INSERT INTO tbl values (1, 2, 3);
493 SELECT * from tbl;
494 }
495 } {1 2 3}
496 do_test trigger2-6.1b {
497 catchsql {
498 INSERT OR ABORT INTO tbl values (2, 2, 3);
499 }
500 } {1 {column a is not unique}}
501 do_test trigger2-6.1c {
502 execsql {
503 SELECT * from tbl;
504 }
505 } {1 2 3}
506 do_test trigger2-6.1d {
507 catchsql {
508 INSERT OR FAIL INTO tbl values (2, 2, 3);
509 }
510 } {1 {column a is not unique}}
511 do_test trigger2-6.1e {
512 execsql {
513 SELECT * from tbl;
514 }
515 } {1 2 3 2 2 3}
516 do_test trigger2-6.1f {
517 execsql {
518 INSERT OR REPLACE INTO tbl values (2, 2, 3);
519 SELECT * from tbl;
520 }
521 } {1 2 3 2 0 0}
522 do_test trigger2-6.1g {
523 catchsql {
524 INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
525 }
526 } {1 {column a is not unique}}
527 do_test trigger2-6.1h {
528 execsql {
529 SELECT * from tbl;
530 }
531 } {}
532 execsql {DELETE FROM tbl}
533  
534  
535 # Handling of ON CONFLICT by UPDATE statements inside triggers
536 execsql {
537 INSERT INTO tbl values (4, 2, 3);
538 INSERT INTO tbl values (6, 3, 4);
539 CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN
540 UPDATE OR IGNORE tbl SET a = new.a, c = 10;
541 END;
542 }
543 do_test trigger2-6.2a {
544 execsql {
545 BEGIN;
546 UPDATE tbl SET a = 1 WHERE a = 4;
547 SELECT * from tbl;
548 }
549 } {1 2 10 6 3 4}
550 do_test trigger2-6.2b {
551 catchsql {
552 UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
553 }
554 } {1 {column a is not unique}}
555 do_test trigger2-6.2c {
556 execsql {
557 SELECT * from tbl;
558 }
559 } {1 2 10 6 3 4}
560 do_test trigger2-6.2d {
561 catchsql {
562 UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
563 }
564 } {1 {column a is not unique}}
565 do_test trigger2-6.2e {
566 execsql {
567 SELECT * from tbl;
568 }
569 } {4 2 10 6 3 4}
570 do_test trigger2-6.2f.1 {
571 execsql {
572 UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
573 SELECT * from tbl;
574 }
575 } {1 3 10}
576 do_test trigger2-6.2f.2 {
577 execsql {
578 INSERT INTO tbl VALUES (2, 3, 4);
579 SELECT * FROM tbl;
580 }
581 } {1 3 10 2 3 4}
582 do_test trigger2-6.2g {
583 catchsql {
584 UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
585 }
586 } {1 {column a is not unique}}
587 do_test trigger2-6.2h {
588 execsql {
589 SELECT * from tbl;
590 }
591 } {4 2 3 6 3 4}
592 execsql {
593 DROP TABLE tbl;
594 }
595 } ; # ifcapable conflict
596  
597 # 7. Triggers on views
598 ifcapable view {
599  
600 do_test trigger2-7.1 {
601 execsql {
602 CREATE TABLE ab(a, b);
603 CREATE TABLE cd(c, d);
604 INSERT INTO ab VALUES (1, 2);
605 INSERT INTO ab VALUES (0, 0);
606 INSERT INTO cd VALUES (3, 4);
607  
608 CREATE TABLE tlog(ii INTEGER PRIMARY KEY,
609 olda, oldb, oldc, oldd, newa, newb, newc, newd);
610  
611 CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;
612  
613 CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN
614 INSERT INTO tlog VALUES(NULL,
615 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
616 END;
617 CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN
618 INSERT INTO tlog VALUES(NULL,
619 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
620 END;
621  
622 CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN
623 INSERT INTO tlog VALUES(NULL,
624 old.a, old.b, old.c, old.d, 0, 0, 0, 0);
625 END;
626 CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN
627 INSERT INTO tlog VALUES(NULL,
628 old.a, old.b, old.c, old.d, 0, 0, 0, 0);
629 END;
630  
631 CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN
632 INSERT INTO tlog VALUES(NULL,
633 0, 0, 0, 0, new.a, new.b, new.c, new.d);
634 END;
635 CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN
636 INSERT INTO tlog VALUES(NULL,
637 0, 0, 0, 0, new.a, new.b, new.c, new.d);
638 END;
639 }
640 } {};
641  
642 do_test trigger2-7.2 {
643 execsql {
644 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
645 DELETE FROM abcd WHERE a = 1;
646 INSERT INTO abcd VALUES(10, 20, 30, 40);
647 SELECT * FROM tlog;
648 }
649 } [ list 1 1 2 3 4 100 25 3 4 \
650 2 1 2 3 4 100 25 3 4 \
651 3 1 2 3 4 0 0 0 0 \
652 4 1 2 3 4 0 0 0 0 \
653 5 0 0 0 0 10 20 30 40 \
654 6 0 0 0 0 10 20 30 40 ]
655  
656 do_test trigger2-7.3 {
657 execsql {
658 DELETE FROM tlog;
659 INSERT INTO abcd VALUES(10, 20, 30, 40);
660 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
661 DELETE FROM abcd WHERE a = 1;
662 SELECT * FROM tlog;
663 }
664 } [ list \
665 1 0 0 0 0 10 20 30 40 \
666 2 0 0 0 0 10 20 30 40 \
667 3 1 2 3 4 100 25 3 4 \
668 4 1 2 3 4 100 25 3 4 \
669 5 1 2 3 4 0 0 0 0 \
670 6 1 2 3 4 0 0 0 0 \
671 ]
672 do_test trigger2-7.4 {
673 execsql {
674 DELETE FROM tlog;
675 DELETE FROM abcd WHERE a = 1;
676 INSERT INTO abcd VALUES(10, 20, 30, 40);
677 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
678 SELECT * FROM tlog;
679 }
680 } [ list \
681 1 1 2 3 4 0 0 0 0 \
682 2 1 2 3 4 0 0 0 0 \
683 3 0 0 0 0 10 20 30 40 \
684 4 0 0 0 0 10 20 30 40 \
685 5 1 2 3 4 100 25 3 4 \
686 6 1 2 3 4 100 25 3 4 \
687 ]
688  
689 do_test trigger2-8.1 {
690 execsql {
691 CREATE TABLE t1(a,b,c);
692 INSERT INTO t1 VALUES(1,2,3);
693 CREATE VIEW v1 AS
694 SELECT a+b AS x, b+c AS y, a+c AS z FROM t1;
695 SELECT * FROM v1;
696 }
697 } {3 5 4}
698 do_test trigger2-8.2 {
699 execsql {
700 CREATE TABLE v1log(a,b,c,d,e,f);
701 CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN
702 INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL);
703 END;
704 DELETE FROM v1 WHERE x=1;
705 SELECT * FROM v1log;
706 }
707 } {}
708 do_test trigger2-8.3 {
709 execsql {
710 DELETE FROM v1 WHERE x=3;
711 SELECT * FROM v1log;
712 }
713 } {3 {} 5 {} 4 {}}
714 do_test trigger2-8.4 {
715 execsql {
716 INSERT INTO t1 VALUES(4,5,6);
717 DELETE FROM v1log;
718 DELETE FROM v1 WHERE y=11;
719 SELECT * FROM v1log;
720 }
721 } {9 {} 11 {} 10 {}}
722 do_test trigger2-8.5 {
723 execsql {
724 CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN
725 INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z);
726 END;
727 DELETE FROM v1log;
728 INSERT INTO v1 VALUES(1,2,3);
729 SELECT * FROM v1log;
730 }
731 } {{} 1 {} 2 {} 3}
732 do_test trigger2-8.6 {
733 execsql {
734 CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN
735 INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z);
736 END;
737 DELETE FROM v1log;
738 UPDATE v1 SET x=x+100, y=y+200, z=z+300;
739 SELECT * FROM v1log;
740 }
741 } {3 103 5 205 4 304 9 109 11 211 10 310}
742  
743 # At one point the following was causing a segfault.
744 do_test trigger2-9.1 {
745 execsql {
746 CREATE TABLE t3(a TEXT, b TEXT);
747 CREATE VIEW v3 AS SELECT t3.a FROM t3;
748 CREATE TRIGGER trig1 INSTEAD OF DELETE ON v3 BEGIN
749 SELECT 1;
750 END;
751 DELETE FROM v3 WHERE a = 1;
752 }
753 } {}
754  
755 } ;# ifcapable view
756  
757 integrity_check trigger2-9.9
758  
759 finish_test