1 # The author disclaims copyright to this source code. In place of
2 # a legal notice, here is a blessing:
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.
8 #***********************************************************************
10 # Regression testing of FOR EACH ROW table triggers
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.
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.
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).
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.
29 # trigger2-3.1: UPDATE OF triggers
30 # trigger2-3.2: WHEN clause
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.
36 # trigger2-4.1: Trivial cascading trigger
37 # trigger2-4.2: Trivial recursive trigger handling
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.
43 # 6. ON CONFLICT clause handling
44 # trigger2-6.1[a-f]: INSERT statements
45 # trigger2-6.2[a-f]: UPDATE statements
47 # 7. & 8. Triggers on views fire correctly.
50 set testdir [file dirname $argv0]
51 source $testdir/tester.tcl
52 ifcapable {!trigger} {
60 set tbl_definitions [list \
61 {CREATE TABLE tbl (a, b);} \
62 {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} \
63 {CREATE TABLE tbl (a, b PRIMARY KEY);} \
64 {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} \
67 lappend tbl_definitions \
68 {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);}
69 lappend tbl_definitions {CREATE TEMP TABLE tbl (a, b);}
70 lappend tbl_definitions \
71 {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);}
73 foreach tbl_defn $tbl_definitions {
75 catchsql { DROP INDEX tbl_idx; }
86 INSERT INTO tbl VALUES(1, 2);
87 INSERT INTO tbl VALUES(3, 4);
89 CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
90 CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
92 CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW
94 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
96 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
100 CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW
102 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
104 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
108 CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
111 INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog),
113 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
118 do_test trigger2-1.$ii.1 {
121 UPDATE tbl SET a = a * 10, b = b * 10;
122 SELECT * FROM rlog ORDER BY idx;
123 SELECT * FROM clog ORDER BY idx;
125 lappend r [expr {int($v)}]
128 } [list 1 1 2 4 6 10 20 \
137 INSERT INTO tbl VALUES (100, 100);
138 INSERT INTO tbl VALUES (300, 200);
139 CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
141 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
143 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
147 CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
149 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
151 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
155 do_test trigger2-1.$ii.2 {
161 lappend r [expr {int($v)}]
164 } [list 1 100 100 400 300 0 0 \
165 2 100 100 300 200 0 0 \
166 3 300 200 300 200 0 0 \
171 CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
173 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
175 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
179 CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
181 INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
183 (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
187 do_test trigger2-1.$ii.3 {
190 CREATE TABLE other_tbl(a, b);
191 INSERT INTO other_tbl VALUES(1, 2);
192 INSERT INTO other_tbl VALUES(3, 4);
193 -- INSERT INTO tbl SELECT * FROM other_tbl;
194 INSERT INTO tbl VALUES(5, 6);
195 DROP TABLE other_tbl;
199 } [list 1 0 0 0.0 0.0 5 6 \
202 integrity_check trigger2-1.$ii.4
208 DROP TABLE other_tbl;
215 {UPDATE tbl SET b = old.b;}
216 {INSERT INTO log VALUES(new.c, 2, 3);}
217 {DELETE FROM log WHERE a = 1;}
218 {INSERT INTO tbl VALUES(500, new.b * 10, 700);
219 UPDATE tbl SET c = old.c;
221 {INSERT INTO log select * from tbl;}
223 foreach test_varset [ list \
225 set statement {UPDATE tbl SET c = 10 WHERE a = 1;}
226 set prep {INSERT INTO tbl VALUES(1, 2, 3);}
235 set statement {DELETE FROM tbl WHERE a = 1;}
236 set prep {INSERT INTO tbl VALUES(1, 2, 3);}
242 set statement {INSERT INTO tbl VALUES(1, 2, 3);}
262 set statement_type [string range $statement 0 5]
263 set tr_program_fixed $tr_program
264 if {$statement_type == "DELETE"} {
265 regsub -all new\.a $tr_program_fixed {''} tr_program_fixed
266 regsub -all new\.b $tr_program_fixed {''} tr_program_fixed
267 regsub -all new\.c $tr_program_fixed {''} tr_program_fixed
269 if {$statement_type == "INSERT"} {
270 regsub -all old\.a $tr_program_fixed {''} tr_program_fixed
271 regsub -all old\.b $tr_program_fixed {''} tr_program_fixed
272 regsub -all old\.c $tr_program_fixed {''} tr_program_fixed
276 set tr_program_cooked $tr_program
277 regsub -all new\.a $tr_program_cooked $newA tr_program_cooked
278 regsub -all new\.b $tr_program_cooked $newB tr_program_cooked
279 regsub -all new\.c $tr_program_cooked $newC tr_program_cooked
280 regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked
281 regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked
282 regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked
290 CREATE TABLE tbl(a PRIMARY KEY, b, c);
291 CREATE TABLE log(a, b, c);
294 set query {SELECT * FROM tbl; SELECT * FROM log;}
295 set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\
296 INSERT INTO log VALUES(10, 20, 30);"
298 # Check execution of BEFORE programs:
300 set before_data [ execsql "$prep $tr_program_cooked $statement $query" ]
302 execsql "DELETE FROM tbl; DELETE FROM log; $prep";
303 execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\
304 ON tbl BEGIN $tr_program_fixed END;"
306 do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data
308 execsql "DROP TRIGGER the_trigger;"
309 execsql "DELETE FROM tbl; DELETE FROM log;"
311 # Check execution of AFTER programs
312 set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]
314 execsql "DELETE FROM tbl; DELETE FROM log; $prep";
315 execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\
316 ON tbl BEGIN $tr_program_fixed END;"
318 do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data
319 execsql "DROP TRIGGER the_trigger;"
321 integrity_check trigger2-2.$ii-integrity
331 # trigger2-3.1: UPDATE OF triggers
333 CREATE TABLE tbl (a, b, c, d);
334 CREATE TABLE log (a);
335 INSERT INTO log VALUES (0);
336 INSERT INTO tbl VALUES (0, 0, 0, 0);
337 INSERT INTO tbl VALUES (1, 0, 0, 0);
338 CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl
340 UPDATE log SET a = a + 1;
343 do_test trigger2-3.1 {
345 UPDATE tbl SET b = 1, c = 10; -- 2
346 UPDATE tbl SET b = 10; -- 0
347 UPDATE tbl SET d = 4 WHERE a = 0; --1
348 UPDATE tbl SET a = 4, b = 10; --0
357 # trigger2-3.2: WHEN clause
358 set when_triggers [list {t1 BEFORE INSERT ON tbl WHEN new.a > 20}]
360 lappend when_triggers \
361 {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0}
365 CREATE TABLE tbl (a, b, c, d);
366 CREATE TABLE log (a);
367 INSERT INTO log VALUES (0);
370 foreach trig $when_triggers {
371 execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"
379 do_test trigger2-3.2 {
382 INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1 (ifcapable subquery)
384 UPDATE log SET a = 0;
386 INSERT INTO tbl VALUES(0, 0, 0, 0); -- 0
388 UPDATE log SET a = 0;
390 INSERT INTO tbl VALUES(200, 0, 0, 0); -- 1
392 UPDATE log SET a = 0;
399 integrity_check trigger2-3.3
401 # Simple cascaded trigger
403 CREATE TABLE tblA(a, b);
404 CREATE TABLE tblB(a, b);
405 CREATE TABLE tblC(a, b);
407 CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN
408 INSERT INTO tblB values(new.a, new.b);
411 CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN
412 INSERT INTO tblC values(new.a, new.b);
415 do_test trigger2-4.1 {
417 INSERT INTO tblA values(1, 2);
429 # Simple recursive trigger
431 CREATE TABLE tbl(a, b, c);
432 CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl
434 INSERT INTO tbl VALUES (new.a, new.b, new.c);
437 do_test trigger2-4.2 {
439 INSERT INTO tbl VALUES (1, 2, 3);
449 CREATE TABLE tbl(a, b, c);
450 CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl
452 INSERT INTO tbl VALUES (1, 2, 3);
453 INSERT INTO tbl VALUES (2, 2, 3);
454 UPDATE tbl set b = 10 WHERE a = 1;
455 DELETE FROM tbl WHERE a = 1;
461 INSERT INTO tbl VALUES(100, 200, 300);
469 # Handling of ON CONFLICT by INSERT statements inside triggers
471 CREATE TABLE tbl (a primary key, b, c);
472 CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN
473 INSERT OR IGNORE INTO tbl values (new.a, 0, 0);
476 do_test trigger2-6.1a {
479 INSERT INTO tbl values (1, 2, 3);
483 do_test trigger2-6.1b {
485 INSERT OR ABORT INTO tbl values (2, 2, 3);
487 } {1 {column a is not unique}}
488 do_test trigger2-6.1c {
493 do_test trigger2-6.1d {
495 INSERT OR FAIL INTO tbl values (2, 2, 3);
497 } {1 {column a is not unique}}
498 do_test trigger2-6.1e {
503 do_test trigger2-6.1f {
505 INSERT OR REPLACE INTO tbl values (2, 2, 3);
509 do_test trigger2-6.1g {
511 INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
513 } {1 {column a is not unique}}
514 do_test trigger2-6.1h {
519 execsql {DELETE FROM tbl}
522 # Handling of ON CONFLICT by UPDATE statements inside triggers
524 INSERT INTO tbl values (4, 2, 3);
525 INSERT INTO tbl values (6, 3, 4);
526 CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN
527 UPDATE OR IGNORE tbl SET a = new.a, c = 10;
530 do_test trigger2-6.2a {
533 UPDATE tbl SET a = 1 WHERE a = 4;
537 do_test trigger2-6.2b {
539 UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
541 } {1 {column a is not unique}}
542 do_test trigger2-6.2c {
547 do_test trigger2-6.2d {
549 UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
551 } {1 {column a is not unique}}
552 do_test trigger2-6.2e {
557 do_test trigger2-6.2f.1 {
559 UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
563 do_test trigger2-6.2f.2 {
565 INSERT INTO tbl VALUES (2, 3, 4);
569 do_test trigger2-6.2g {
571 UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
573 } {1 {column a is not unique}}
574 do_test trigger2-6.2h {
583 # 7. Triggers on views
586 do_test trigger2-7.1 {
588 CREATE TABLE ab(a, b);
589 CREATE TABLE cd(c, d);
590 INSERT INTO ab VALUES (1, 2);
591 INSERT INTO ab VALUES (0, 0);
592 INSERT INTO cd VALUES (3, 4);
594 CREATE TABLE tlog(ii INTEGER PRIMARY KEY,
595 olda, oldb, oldc, oldd, newa, newb, newc, newd);
597 CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;
599 CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN
600 INSERT INTO tlog VALUES(NULL,
601 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
603 CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN
604 INSERT INTO tlog VALUES(NULL,
605 old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
608 CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN
609 INSERT INTO tlog VALUES(NULL,
610 old.a, old.b, old.c, old.d, 0, 0, 0, 0);
612 CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN
613 INSERT INTO tlog VALUES(NULL,
614 old.a, old.b, old.c, old.d, 0, 0, 0, 0);
617 CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN
618 INSERT INTO tlog VALUES(NULL,
619 0, 0, 0, 0, new.a, new.b, new.c, new.d);
621 CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN
622 INSERT INTO tlog VALUES(NULL,
623 0, 0, 0, 0, new.a, new.b, new.c, new.d);
628 do_test trigger2-7.2 {
630 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
631 DELETE FROM abcd WHERE a = 1;
632 INSERT INTO abcd VALUES(10, 20, 30, 40);
635 } [ list 1 1 2 3 4 100 25 3 4 \
636 2 1 2 3 4 100 25 3 4 \
639 5 0 0 0 0 10 20 30 40 \
640 6 0 0 0 0 10 20 30 40 ]
642 do_test trigger2-7.3 {
645 INSERT INTO abcd VALUES(10, 20, 30, 40);
646 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
647 DELETE FROM abcd WHERE a = 1;
651 1 0 0 0 0 10 20 30 40 \
652 2 0 0 0 0 10 20 30 40 \
653 3 1 2 3 4 100 25 3 4 \
654 4 1 2 3 4 100 25 3 4 \
658 do_test trigger2-7.4 {
661 DELETE FROM abcd WHERE a = 1;
662 INSERT INTO abcd VALUES(10, 20, 30, 40);
663 UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
669 3 0 0 0 0 10 20 30 40 \
670 4 0 0 0 0 10 20 30 40 \
671 5 1 2 3 4 100 25 3 4 \
672 6 1 2 3 4 100 25 3 4 \
675 do_test trigger2-8.1 {
677 CREATE TABLE t1(a,b,c);
678 INSERT INTO t1 VALUES(1,2,3);
680 SELECT a+b AS x, b+c AS y, a+c AS z FROM t1;
684 do_test trigger2-8.2 {
686 CREATE TABLE v1log(a,b,c,d,e,f);
687 CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN
688 INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL);
690 DELETE FROM v1 WHERE x=1;
694 do_test trigger2-8.3 {
696 DELETE FROM v1 WHERE x=3;
700 do_test trigger2-8.4 {
702 INSERT INTO t1 VALUES(4,5,6);
704 DELETE FROM v1 WHERE y=11;
708 do_test trigger2-8.5 {
710 CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN
711 INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z);
714 INSERT INTO v1 VALUES(1,2,3);
718 do_test trigger2-8.6 {
720 CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN
721 INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z);
724 UPDATE v1 SET x=x+100, y=y+200, z=z+300;
727 } {3 103 5 205 4 304 9 109 11 211 10 310}
731 integrity_check trigger2-9.9