# The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # Regression testing of FOR EACH ROW table triggers # # 1. Trigger execution order tests. # These tests ensure that BEFORE and AFTER triggers are fired at the correct # times relative to each other and the triggering statement. # # trigger2-1.1.*: ON UPDATE trigger execution model. # trigger2-1.2.*: DELETE trigger execution model. # trigger2-1.3.*: INSERT trigger execution model. # # 2. Trigger program execution tests. # These tests ensure that trigger programs execute correctly (ie. that a # trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT # statements, and combinations thereof). # # 3. Selective trigger execution # This tests that conditional triggers (ie. UPDATE OF triggers and triggers # with WHEN clauses) are fired only fired when they are supposed to be. # # trigger2-3.1: UPDATE OF triggers # trigger2-3.2: WHEN clause # # 4. Cascaded trigger execution # Tests that trigger-programs may cause other triggers to fire. Also that a # trigger-program is never executed recursively. # # trigger2-4.1: Trivial cascading trigger # trigger2-4.2: Trivial recursive trigger handling # # 5. Count changes behaviour. # Verify that rows altered by triggers are not included in the return value # of the "count changes" interface. # # 6. ON CONFLICT clause handling # trigger2-6.1[a-f]: INSERT statements # trigger2-6.2[a-f]: UPDATE statements # # 7. & 8. Triggers on views fire correctly. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable {!trigger} { finish_test return } # 1. ifcapable subquery { set ii 0 foreach tbl_defn { {CREATE TEMP TABLE tbl (a, b);} {CREATE TABLE tbl (a, b);} {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);} {CREATE TABLE tbl (a, b PRIMARY KEY);} {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} } { incr ii catchsql { DROP INDEX tbl_idx; } catchsql { DROP TABLE rlog; DROP TABLE clog; DROP TABLE tbl; DROP TABLE other_tbl; } execsql $tbl_defn execsql { INSERT INTO tbl VALUES(1, 2); INSERT INTO tbl VALUES(3, 4); CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW BEGIN INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), old.a, old.b, (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), new.a, new.b); END; CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW BEGIN INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), old.a, old.b, (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), new.a, new.b); END; CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW WHEN old.a = 1 BEGIN INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), old.a, old.b, (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), new.a, new.b); END; } do_test trigger2-1.$ii.1 { set r {} foreach v [execsql { UPDATE tbl SET a = a * 10, b = b * 10; SELECT * FROM rlog ORDER BY idx; SELECT * FROM clog ORDER BY idx; }] { lappend r [expr {int($v)}] } set r } [list 1 1 2 4 6 10 20 \ 2 1 2 13 24 10 20 \ 3 3 4 13 24 30 40 \ 4 3 4 40 60 30 40 \ 1 1 2 13 24 10 20 ] execsql { DELETE FROM rlog; DELETE FROM tbl; INSERT INTO tbl VALUES (100, 100); INSERT INTO tbl VALUES (300, 200); CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW BEGIN INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), old.a, old.b, (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 0, 0); END; CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW BEGIN INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), old.a, old.b, (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 0, 0); END; } do_test trigger2-1.$ii.2 { set r {} foreach v [execsql { DELETE FROM tbl; SELECT * FROM rlog; }] { lappend r [expr {int($v)}] } set r } [list 1 100 100 400 300 0 0 \ 2 100 100 300 200 0 0 \ 3 300 200 300 200 0 0 \ 4 300 200 0 0 0 0 ] execsql { DELETE FROM rlog; CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW BEGIN INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 0, 0, (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), new.a, new.b); END; CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW BEGIN INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 0, 0, (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), new.a, new.b); END; } do_test trigger2-1.$ii.3 { execsql { CREATE TABLE other_tbl(a, b); INSERT INTO other_tbl VALUES(1, 2); INSERT INTO other_tbl VALUES(3, 4); -- INSERT INTO tbl SELECT * FROM other_tbl; INSERT INTO tbl VALUES(5, 6); DROP TABLE other_tbl; SELECT * FROM rlog; } } [list 1 0 0 0.0 0.0 5 6 \ 2 0 0 5.0 6.0 5 6 ] integrity_check trigger2-1.$ii.4 } catchsql { DROP TABLE rlog; DROP TABLE clog; DROP TABLE tbl; DROP TABLE other_tbl; } } # 2. set ii 0 foreach tr_program { {UPDATE tbl SET b = old.b;} {INSERT INTO log VALUES(new.c, 2, 3);} {DELETE FROM log WHERE a = 1;} {INSERT INTO tbl VALUES(500, new.b * 10, 700); UPDATE tbl SET c = old.c; DELETE FROM log;} {INSERT INTO log select * from tbl;} } { foreach test_varset [ list \ { set statement {UPDATE tbl SET c = 10 WHERE a = 1;} set prep {INSERT INTO tbl VALUES(1, 2, 3);} set newC 10 set newB 2 set newA 1 set oldA 1 set oldB 2 set oldC 3 } \ { set statement {DELETE FROM tbl WHERE a = 1;} set prep {INSERT INTO tbl VALUES(1, 2, 3);} set oldA 1 set oldB 2 set oldC 3 } \ { set statement {INSERT INTO tbl VALUES(1, 2, 3);} set newA 1 set newB 2 set newC 3 } ] \ { set statement {} set prep {} set newA {''} set newB {''} set newC {''} set oldA {''} set oldB {''} set oldC {''} incr ii eval $test_varset set statement_type [string range $statement 0 5] set tr_program_fixed $tr_program if {$statement_type == "DELETE"} { regsub -all new\.a $tr_program_fixed {''} tr_program_fixed regsub -all new\.b $tr_program_fixed {''} tr_program_fixed regsub -all new\.c $tr_program_fixed {''} tr_program_fixed } if {$statement_type == "INSERT"} { regsub -all old\.a $tr_program_fixed {''} tr_program_fixed regsub -all old\.b $tr_program_fixed {''} tr_program_fixed regsub -all old\.c $tr_program_fixed {''} tr_program_fixed } set tr_program_cooked $tr_program regsub -all new\.a $tr_program_cooked $newA tr_program_cooked regsub -all new\.b $tr_program_cooked $newB tr_program_cooked regsub -all new\.c $tr_program_cooked $newC tr_program_cooked regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked catchsql { DROP TABLE tbl; DROP TABLE log; } execsql { CREATE TABLE tbl(a PRIMARY KEY, b, c); CREATE TABLE log(a, b, c); } set query {SELECT * FROM tbl; SELECT * FROM log;} set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\ INSERT INTO log VALUES(10, 20, 30);" # Check execution of BEFORE programs: set before_data [ execsql "$prep $tr_program_cooked $statement $query" ] execsql "DELETE FROM tbl; DELETE FROM log; $prep"; execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\ ON tbl BEGIN $tr_program_fixed END;" do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data execsql "DROP TRIGGER the_trigger;" execsql "DELETE FROM tbl; DELETE FROM log;" # Check execution of AFTER programs set after_data [ execsql "$prep $statement $tr_program_cooked $query" ] execsql "DELETE FROM tbl; DELETE FROM log; $prep"; execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\ ON tbl BEGIN $tr_program_fixed END;" do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data execsql "DROP TRIGGER the_trigger;" integrity_check trigger2-2.$ii-integrity } } catchsql { DROP TABLE tbl; DROP TABLE log; } # 3. # trigger2-3.1: UPDATE OF triggers execsql { CREATE TABLE tbl (a, b, c, d); CREATE TABLE log (a); INSERT INTO log VALUES (0); INSERT INTO tbl VALUES (0, 0, 0, 0); INSERT INTO tbl VALUES (1, 0, 0, 0); CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl BEGIN UPDATE log SET a = a + 1; END; } do_test trigger2-3.1 { execsql { UPDATE tbl SET b = 1, c = 10; -- 2 UPDATE tbl SET b = 10; -- 0 UPDATE tbl SET d = 4 WHERE a = 0; --1 UPDATE tbl SET a = 4, b = 10; --0 SELECT * FROM log; } } {3} execsql { DROP TABLE tbl; DROP TABLE log; } # trigger2-3.2: WHEN clause set when_triggers [list {t1 BEFORE INSERT ON tbl WHEN new.a > 20}] ifcapable subquery { lappend when_triggers \ {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} } execsql { CREATE TABLE tbl (a, b, c, d); CREATE TABLE log (a); INSERT INTO log VALUES (0); } foreach trig $when_triggers { execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;" } ifcapable subquery { set t232 {1 0 1} } else { set t232 {0 0 1} } do_test trigger2-3.2 { execsql { INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1 (ifcapable subquery) SELECT * FROM log; UPDATE log SET a = 0; INSERT INTO tbl VALUES(0, 0, 0, 0); -- 0 SELECT * FROM log; UPDATE log SET a = 0; INSERT INTO tbl VALUES(200, 0, 0, 0); -- 1 SELECT * FROM log; UPDATE log SET a = 0; } } $t232 execsql { DROP TABLE tbl; DROP TABLE log; } integrity_check trigger2-3.3 # Simple cascaded trigger execsql { CREATE TABLE tblA(a, b); CREATE TABLE tblB(a, b); CREATE TABLE tblC(a, b); CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN INSERT INTO tblB values(new.a, new.b); END; CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN INSERT INTO tblC values(new.a, new.b); END; } do_test trigger2-4.1 { execsql { INSERT INTO tblA values(1, 2); SELECT * FROM tblA; SELECT * FROM tblB; SELECT * FROM tblC; } } {1 2 1 2 1 2} execsql { DROP TABLE tblA; DROP TABLE tblB; DROP TABLE tblC; } # Simple recursive trigger execsql { CREATE TABLE tbl(a, b, c); CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl BEGIN INSERT INTO tbl VALUES (new.a, new.b, new.c); END; } do_test trigger2-4.2 { execsql { INSERT INTO tbl VALUES (1, 2, 3); select * from tbl; } } {1 2 3 1 2 3} execsql { DROP TABLE tbl; } # 5. execsql { CREATE TABLE tbl(a, b, c); CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl BEGIN INSERT INTO tbl VALUES (1, 2, 3); INSERT INTO tbl VALUES (2, 2, 3); UPDATE tbl set b = 10 WHERE a = 1; DELETE FROM tbl WHERE a = 1; DELETE FROM tbl; END; } do_test trigger2-5 { execsql { INSERT INTO tbl VALUES(100, 200, 300); } db changes } {1} execsql { DROP TABLE tbl; } # Handling of ON CONFLICT by INSERT statements inside triggers execsql { CREATE TABLE tbl (a primary key, b, c); CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN INSERT OR IGNORE INTO tbl values (new.a, 0, 0); END; } do_test trigger2-6.1a { execsql { BEGIN; INSERT INTO tbl values (1, 2, 3); SELECT * from tbl; } } {1 2 3} do_test trigger2-6.1b { catchsql { INSERT OR ABORT INTO tbl values (2, 2, 3); } } {1 {column a is not unique}} do_test trigger2-6.1c { execsql { SELECT * from tbl; } } {1 2 3} do_test trigger2-6.1d { catchsql { INSERT OR FAIL INTO tbl values (2, 2, 3); } } {1 {column a is not unique}} do_test trigger2-6.1e { execsql { SELECT * from tbl; } } {1 2 3 2 2 3} do_test trigger2-6.1f { execsql { INSERT OR REPLACE INTO tbl values (2, 2, 3); SELECT * from tbl; } } {1 2 3 2 0 0} do_test trigger2-6.1g { catchsql { INSERT OR ROLLBACK INTO tbl values (3, 2, 3); } } {1 {column a is not unique}} do_test trigger2-6.1h { execsql { SELECT * from tbl; } } {} execsql {DELETE FROM tbl} # Handling of ON CONFLICT by UPDATE statements inside triggers execsql { INSERT INTO tbl values (4, 2, 3); INSERT INTO tbl values (6, 3, 4); CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN UPDATE OR IGNORE tbl SET a = new.a, c = 10; END; } do_test trigger2-6.2a { execsql { BEGIN; UPDATE tbl SET a = 1 WHERE a = 4; SELECT * from tbl; } } {1 2 10 6 3 4} do_test trigger2-6.2b { catchsql { UPDATE OR ABORT tbl SET a = 4 WHERE a = 1; } } {1 {column a is not unique}} do_test trigger2-6.2c { execsql { SELECT * from tbl; } } {1 2 10 6 3 4} do_test trigger2-6.2d { catchsql { UPDATE OR FAIL tbl SET a = 4 WHERE a = 1; } } {1 {column a is not unique}} do_test trigger2-6.2e { execsql { SELECT * from tbl; } } {4 2 10 6 3 4} do_test trigger2-6.2f.1 { execsql { UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4; SELECT * from tbl; } } {1 3 10} do_test trigger2-6.2f.2 { execsql { INSERT INTO tbl VALUES (2, 3, 4); SELECT * FROM tbl; } } {1 3 10 2 3 4} do_test trigger2-6.2g { catchsql { UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1; } } {1 {column a is not unique}} do_test trigger2-6.2h { execsql { SELECT * from tbl; } } {4 2 3 6 3 4} execsql { DROP TABLE tbl; } # 7. Triggers on views ifcapable view { do_test trigger2-7.1 { execsql { CREATE TABLE ab(a, b); CREATE TABLE cd(c, d); INSERT INTO ab VALUES (1, 2); INSERT INTO ab VALUES (0, 0); INSERT INTO cd VALUES (3, 4); CREATE TABLE tlog(ii INTEGER PRIMARY KEY, olda, oldb, oldc, oldd, newa, newb, newc, newd); CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd; CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN INSERT INTO tlog VALUES(NULL, old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); END; CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN INSERT INTO tlog VALUES(NULL, old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); END; CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN INSERT INTO tlog VALUES(NULL, old.a, old.b, old.c, old.d, 0, 0, 0, 0); END; CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN INSERT INTO tlog VALUES(NULL, old.a, old.b, old.c, old.d, 0, 0, 0, 0); END; CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN INSERT INTO tlog VALUES(NULL, 0, 0, 0, 0, new.a, new.b, new.c, new.d); END; CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN INSERT INTO tlog VALUES(NULL, 0, 0, 0, 0, new.a, new.b, new.c, new.d); END; } } {}; do_test trigger2-7.2 { execsql { UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; DELETE FROM abcd WHERE a = 1; INSERT INTO abcd VALUES(10, 20, 30, 40); SELECT * FROM tlog; } } [ list 1 1 2 3 4 100 25 3 4 \ 2 1 2 3 4 100 25 3 4 \ 3 1 2 3 4 0 0 0 0 \ 4 1 2 3 4 0 0 0 0 \ 5 0 0 0 0 10 20 30 40 \ 6 0 0 0 0 10 20 30 40 ] do_test trigger2-7.3 { execsql { DELETE FROM tlog; INSERT INTO abcd VALUES(10, 20, 30, 40); UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; DELETE FROM abcd WHERE a = 1; SELECT * FROM tlog; } } [ list \ 1 0 0 0 0 10 20 30 40 \ 2 0 0 0 0 10 20 30 40 \ 3 1 2 3 4 100 25 3 4 \ 4 1 2 3 4 100 25 3 4 \ 5 1 2 3 4 0 0 0 0 \ 6 1 2 3 4 0 0 0 0 \ ] do_test trigger2-7.4 { execsql { DELETE FROM tlog; DELETE FROM abcd WHERE a = 1; INSERT INTO abcd VALUES(10, 20, 30, 40); UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; SELECT * FROM tlog; } } [ list \ 1 1 2 3 4 0 0 0 0 \ 2 1 2 3 4 0 0 0 0 \ 3 0 0 0 0 10 20 30 40 \ 4 0 0 0 0 10 20 30 40 \ 5 1 2 3 4 100 25 3 4 \ 6 1 2 3 4 100 25 3 4 \ ] do_test trigger2-8.1 { execsql { CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(1,2,3); CREATE VIEW v1 AS SELECT a+b AS x, b+c AS y, a+c AS z FROM t1; SELECT * FROM v1; } } {3 5 4} do_test trigger2-8.2 { execsql { CREATE TABLE v1log(a,b,c,d,e,f); CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL); END; DELETE FROM v1 WHERE x=1; SELECT * FROM v1log; } } {} do_test trigger2-8.3 { execsql { DELETE FROM v1 WHERE x=3; SELECT * FROM v1log; } } {3 {} 5 {} 4 {}} do_test trigger2-8.4 { execsql { INSERT INTO t1 VALUES(4,5,6); DELETE FROM v1log; DELETE FROM v1 WHERE y=11; SELECT * FROM v1log; } } {9 {} 11 {} 10 {}} do_test trigger2-8.5 { execsql { CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z); END; DELETE FROM v1log; INSERT INTO v1 VALUES(1,2,3); SELECT * FROM v1log; } } {{} 1 {} 2 {} 3} do_test trigger2-8.6 { execsql { CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z); END; DELETE FROM v1log; UPDATE v1 SET x=x+100, y=y+200, z=z+300; SELECT * FROM v1log; } } {3 103 5 205 4 304 9 109 11 211 10 310} } ;# ifcapable view integrity_check trigger2-9.9 finish_test