Debian Codegraph @

test/trigger2.test

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