# 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.tclifcapable {!trigger} {  finish_test  return}# 1.ifcapable subquery {  set ii 0  set tbl_definitions [list \  	{CREATE TABLE tbl (a, b);}                                      \  	{CREATE 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);}      \  ]  ifcapable tempdb {    lappend tbl_definitions \        {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);}     lappend tbl_definitions {CREATE TEMP TABLE tbl (a, b);}    lappend tbl_definitions \        {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);}  }  foreach tbl_defn $tbl_definitions {    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 coalesce(sum(a),0) FROM tbl),          (SELECT coalesce(sum(b),0) 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 coalesce(sum(a),0) FROM tbl),          (SELECT coalesce(sum(b),0) 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 coalesce(sum(a),0) FROM tbl),          (SELECT coalesce(sum(b),0) 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 coalesce(sum(a),0) FROM tbl),          (SELECT coalesce(sum(b),0) 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 coalesce(sum(a),0) FROM tbl),          (SELECT coalesce(sum(b),0) 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 coalesce(sum(a),0) FROM tbl),          (SELECT coalesce(sum(b),0) 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 coalesce(sum(a),0) FROM tbl),          (SELECT coalesce(sum(b),0) 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 5 6 \            2 0 0 5 6 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 0foreach 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 triggersexecsql {  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 clauseset 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);