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