SQLite-2.5.0-for-code-reading/test/trigger2.test

# 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. 
#
# trig-1.1.*: ON UPDATE trigger execution model.
# trig-1.2.*: DELETE trigger execution model.
# trig-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.
#
# trig-3.1: UPDATE OF triggers
# trig-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.
# 
# trig-4.1: Trivial cascading trigger
# trig-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
# trig-6.1[a-f]: INSERT statements
# trig-6.2[a-f]: UPDATE statements
#
# 7. Triggers on views fire correctly.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# 1.
set ii 0
foreach tbl_defn [ 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);} ] {
  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 trig-1.1.$ii {
    execsql {
      UPDATE tbl SET a = a * 10, b = b * 10;
      SELECT * FROM rlog ORDER BY idx;
      SELECT * FROM clog ORDER BY idx;
    }
  } [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 trig-1.2.$ii {
    execsql {
      DELETE FROM tbl;
      SELECT * FROM rlog;
    }
  } [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 trig-1.3.$ii {
    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 ]
}
catchsql {
  DROP TABLE rlog;
  DROP TABLE clog;
  DROP TABLE tbl;
  DROP TABLE other_tbl;
}

# 2.
set ii 0
foreach tr_program [ list \
   {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 trig-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 trig-2-$ii-after "execsql {$statement $query}" $after_data
    execsql "DROP TRIGGER the_trigger;"
  }
}
catchsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# 3.

# trig-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 trig-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;
}

# trig-3.2: WHEN clause
set when_triggers [ list \
             {t1 BEFORE INSERT ON tbl WHEN new.a > 20} \
             {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;"
}

do_test trig-3.2 {
  execsql { 

    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 1
    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;
  }
} {1 0 1}
execsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# 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 trig-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 trig-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 trig-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 trig-6.1a {
  execsql {
    BEGIN;
    INSERT INTO tbl values (1, 2, 3);
    SELECT * from tbl;
  }
} {1 2 3}
do_test trig-6.1b {
  catchsql {
    INSERT OR ABORT INTO tbl values (2, 2, 3);
  }
} {1 {constraint failed}}
do_test trig-6.1c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3}
do_test trig-6.1d {
  catchsql {
    INSERT OR FAIL INTO tbl values (2, 2, 3);
  }
} {1 {constraint failed}}
do_test trig-6.1e {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3 2 2 3}
do_test trig-6.1f {
  execsql {
    INSERT OR REPLACE INTO tbl values (2, 2, 3);
    SELECT * from tbl;
  }
} {1 2 3 2 0 0}
do_test trig-6.1g {
  catchsql {
    INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
  }
} {1 {constraint failed}}
do_test trig-6.1h {
  execsql {
    SELECT * 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 trig-6.2a {
  execsql {
    BEGIN;
    UPDATE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trig-6.2b {
  catchsql {
    UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trig-6.2c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trig-6.2d {
  catchsql {
    UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trig-6.2e {
  execsql {
    SELECT * from tbl;
  }
} {4 2 10 6 3 4}
do_test trig-6.2f {
  execsql {
    UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 3 10}
execsql {
  INSERT INTO tbl VALUES (2, 3, 4);
}
do_test trig-6.2g {
  catchsql {
    UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trig-6.2h {
  execsql {
    SELECT * from tbl;
  }
} {4 2 3 6 3 4}
execsql {
  DROP TABLE tbl;
}

# 7. Triggers on views
do_test trig-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 trig-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 ]

finish_test