third_party/sqlite/src/test/e_droptrigger.test

# 2010 November 29
#
# 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.
#
#***********************************************************************
#
# This file implements tests to verify that the "testable statements" in 
# the lang_droptrigger.html document are correct.
#

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

ifcapable !trigger { finish_test ; return }

proc do_droptrigger_tests {nm args} {
  uplevel do_select_tests [list e_createtable-$nm] $args
}

proc list_all_triggers {{db db}} {
  set res [list]
  $db eval { PRAGMA database_list } {
    if {$name == "temp"} {
      set tbl sqlite_temp_master
    } else {
      set tbl "$name.sqlite_master"
    }
    lappend res {*}[
      db eval "SELECT '$name.' || name FROM $tbl WHERE type = 'trigger'"
    ]
  }
  set res
}


proc droptrigger_reopen_db {{event INSERT}} {
  db close
  forcedelete test.db test.db2
  sqlite3 db test.db

  set ::triggers_fired [list]
  proc r {x} { lappend ::triggers_fired $x }
  db func r r

  db eval "
    ATTACH 'test.db2' AS aux;

    CREATE TEMP TABLE t1(a, b);
    INSERT INTO t1 VALUES('a', 'b');
    CREATE TRIGGER tr1 AFTER $event ON t1 BEGIN SELECT r('temp.tr1') ; END;

    CREATE TABLE t2(a, b);
    INSERT INTO t2 VALUES('a', 'b');
    CREATE TRIGGER tr1 BEFORE $event ON t2 BEGIN SELECT r('main.tr1') ; END;
    CREATE TRIGGER tr2 AFTER  $event ON t2 BEGIN SELECT r('main.tr2') ; END;

    CREATE TABLE aux.t3(a, b);
    INSERT INTO t3 VALUES('a', 'b');
    CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END;
    CREATE TRIGGER aux.tr2 AFTER  $event ON t3 BEGIN SELECT r('aux.tr2') ; END;
    CREATE TRIGGER aux.tr3 AFTER  $event ON t3 BEGIN SELECT r('aux.tr3') ; END;
  "
}


# EVIDENCE-OF: R-52650-16855 -- syntax diagram drop-trigger-stmt
#
do_droptrigger_tests 1.1 -repair {
  droptrigger_reopen_db
} -tclquery {
  list_all_triggers 
} {
  1   "DROP TRIGGER main.tr1"            
      {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
  2   "DROP TRIGGER IF EXISTS main.tr1"  
      {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
  3   "DROP TRIGGER tr1"                 
      {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
  4   "DROP TRIGGER IF EXISTS tr1"       
      {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}

  5   "DROP TRIGGER aux.tr1"             
      {main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3}
  6   "DROP TRIGGER IF EXISTS aux.tr1"   
      {main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3}

  7   "DROP TRIGGER IF EXISTS aux.xxx"   
      {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
  8   "DROP TRIGGER IF EXISTS aux.xxx"   
      {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
}

# EVIDENCE-OF: R-61172-15671 The DROP TRIGGER statement removes a
# trigger created by the CREATE TRIGGER statement.
#
foreach {tn tbl droptrigger before after} {
  1   t1  "DROP TRIGGER tr1" {temp.tr1}                {}
  2   t2  "DROP TRIGGER tr1" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  3   t3  "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  4   t1  "DROP TRIGGER tr2" {temp.tr1}                {temp.tr1}
  5   t2  "DROP TRIGGER tr2" {main.tr1 main.tr2}       {main.tr1}
  6   t3  "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  7   t1  "DROP TRIGGER tr3" {temp.tr1}                {temp.tr1}
  8   t2  "DROP TRIGGER tr3" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  9   t3  "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2}
} {

  do_test 2.$tn.1 {
    droptrigger_reopen_db
    execsql " INSERT INTO $tbl VALUES('1', '2') "
    set ::triggers_fired
  } $before

  do_test 2.$tn.2 {
    droptrigger_reopen_db
    execsql $droptrigger
    execsql " INSERT INTO $tbl VALUES('1', '2') "
    set ::triggers_fired
  } $after
}

# EVIDENCE-OF: R-50239-29811 Once removed, the trigger definition is no
# longer present in the sqlite_master (or sqlite_temp_master) table and
# is not fired by any subsequent INSERT, UPDATE or DELETE statements.
#
#   Test cases e_droptrigger-1.* test the first part of this statement
#   (that dropped triggers do not appear in the schema table), and tests
#   droptrigger-2.* test that dropped triggers are not fired by INSERT
#   statements. The following tests verify that they are not fired by
#   UPDATE or DELETE statements.
#
foreach {tn tbl droptrigger before after} {
  1   t1  "DROP TRIGGER tr1" {temp.tr1}                {}
  2   t2  "DROP TRIGGER tr1" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  3   t3  "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  4   t1  "DROP TRIGGER tr2" {temp.tr1}                {temp.tr1}
  5   t2  "DROP TRIGGER tr2" {main.tr1 main.tr2}       {main.tr1}
  6   t3  "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  7   t1  "DROP TRIGGER tr3" {temp.tr1}                {temp.tr1}
  8   t2  "DROP TRIGGER tr3" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  9   t3  "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2}
} {

  do_test 3.1.$tn.1 {
    droptrigger_reopen_db UPDATE
    execsql "UPDATE $tbl SET a = 'abc'"
    set ::triggers_fired
  } $before

  do_test 3.1.$tn.2 {
    droptrigger_reopen_db UPDATE
    execsql $droptrigger
    execsql "UPDATE $tbl SET a = 'abc'"
    set ::triggers_fired
  } $after
}
foreach {tn tbl droptrigger before after} {
  1   t1  "DROP TRIGGER tr1" {temp.tr1}                {}
  2   t2  "DROP TRIGGER tr1" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  3   t3  "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  4   t1  "DROP TRIGGER tr2" {temp.tr1}                {temp.tr1}
  5   t2  "DROP TRIGGER tr2" {main.tr1 main.tr2}       {main.tr1}
  6   t3  "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  7   t1  "DROP TRIGGER tr3" {temp.tr1}                {temp.tr1}
  8   t2  "DROP TRIGGER tr3" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  9   t3  "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2}
} {

  do_test 3.2.$tn.1 {
    droptrigger_reopen_db DELETE
    execsql "DELETE FROM $tbl"
    set ::triggers_fired
  } $before

  do_test 3.2.$tn.2 {
    droptrigger_reopen_db DELETE
    execsql $droptrigger
    execsql "DELETE FROM $tbl"
    set ::triggers_fired
  } $after
}

# EVIDENCE-OF: R-37808-62273 Note that triggers are automatically
# dropped when the associated table is dropped.
#
do_test 4.1 {
  droptrigger_reopen_db
  list_all_triggers
} {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
do_test 4.2 {
  droptrigger_reopen_db
  execsql "DROP TABLE t1"
  list_all_triggers
} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
do_test 4.3 {
  droptrigger_reopen_db
  execsql "DROP TABLE t1"
  list_all_triggers
} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
do_test 4.4 {
  droptrigger_reopen_db
  execsql "DROP TABLE t1"
  list_all_triggers
} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}

finish_test