000001  # 2010 November 29
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  # This file implements tests to verify that the "testable statements" in 
000013  # the lang_droptrigger.html document are correct.
000014  #
000015  
000016  set testdir [file dirname $argv0]
000017  source $testdir/tester.tcl
000018  set ::testprefix e_droptrigger
000019  
000020  ifcapable !trigger { finish_test ; return }
000021  
000022  proc do_droptrigger_tests {nm args} {
000023    uplevel do_select_tests [list e_createtable-$nm] $args
000024  }
000025  
000026  proc list_all_triggers {{db db}} {
000027    set res [list]
000028    $db eval { PRAGMA database_list } {
000029      if {$name == "temp"} {
000030        set tbl sqlite_temp_master
000031      } else {
000032        set tbl "$name.sqlite_master"
000033      }
000034      lappend res {*}[
000035        db eval "SELECT '$name.' || name FROM $tbl WHERE type = 'trigger'"
000036      ]
000037    }
000038    set res
000039  }
000040  
000041  
000042  proc droptrigger_reopen_db {{event INSERT}} {
000043    db close
000044    forcedelete test.db test.db2
000045    sqlite3 db test.db
000046  
000047    set ::triggers_fired [list]
000048    proc r {x} { lappend ::triggers_fired $x }
000049    db func r r
000050  
000051    db eval "
000052      ATTACH 'test.db2' AS aux;
000053  
000054      CREATE TEMP TABLE t1(a, b);
000055      INSERT INTO t1 VALUES('a', 'b');
000056      CREATE TRIGGER tr1 AFTER $event ON t1 BEGIN SELECT r('temp.tr1') ; END;
000057  
000058      CREATE TABLE t2(a, b);
000059      INSERT INTO t2 VALUES('a', 'b');
000060      CREATE TRIGGER tr1 BEFORE $event ON t2 BEGIN SELECT r('main.tr1') ; END;
000061      CREATE TRIGGER tr2 AFTER  $event ON t2 BEGIN SELECT r('main.tr2') ; END;
000062  
000063      CREATE TABLE aux.t3(a, b);
000064      INSERT INTO t3 VALUES('a', 'b');
000065      CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END;
000066      CREATE TRIGGER aux.tr2 AFTER  $event ON t3 BEGIN SELECT r('aux.tr2') ; END;
000067      CREATE TRIGGER aux.tr3 AFTER  $event ON t3 BEGIN SELECT r('aux.tr3') ; END;
000068    "
000069  }
000070  
000071  
000072  # -- syntax diagram drop-trigger-stmt
000073  #
000074  do_droptrigger_tests 1.1 -repair {
000075    droptrigger_reopen_db
000076  } -tclquery {
000077    list_all_triggers 
000078  } {
000079    1   "DROP TRIGGER main.tr1"            
000080        {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
000081    2   "DROP TRIGGER IF EXISTS main.tr1"  
000082        {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
000083    3   "DROP TRIGGER tr1"                 
000084        {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
000085    4   "DROP TRIGGER IF EXISTS tr1"       
000086        {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
000087  
000088    5   "DROP TRIGGER aux.tr1"             
000089        {main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3}
000090    6   "DROP TRIGGER IF EXISTS aux.tr1"   
000091        {main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3}
000092  
000093    7   "DROP TRIGGER IF EXISTS aux.xxx"   
000094        {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
000095    8   "DROP TRIGGER IF EXISTS aux.xxx"   
000096        {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
000097  }
000098  
000099  # EVIDENCE-OF: R-61172-15671 The DROP TRIGGER statement removes a
000100  # trigger created by the CREATE TRIGGER statement.
000101  #
000102  foreach {tn tbl droptrigger before after} {
000103    1   t1  "DROP TRIGGER tr1" {temp.tr1}                {}
000104    2   t2  "DROP TRIGGER tr1" {main.tr1 main.tr2}       {main.tr1 main.tr2}
000105    3   t3  "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}
000106  
000107    4   t1  "DROP TRIGGER tr2" {temp.tr1}                {temp.tr1}
000108    5   t2  "DROP TRIGGER tr2" {main.tr1 main.tr2}       {main.tr1}
000109    6   t3  "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}
000110  
000111    7   t1  "DROP TRIGGER tr3" {temp.tr1}                {temp.tr1}
000112    8   t2  "DROP TRIGGER tr3" {main.tr1 main.tr2}       {main.tr1 main.tr2}
000113    9   t3  "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2}
000114  } {
000115  
000116    do_test 2.$tn.1 {
000117      droptrigger_reopen_db
000118      execsql " INSERT INTO $tbl VALUES('1', '2') "
000119      set ::triggers_fired
000120    } $before
000121  
000122    do_test 2.$tn.2 {
000123      droptrigger_reopen_db
000124      execsql $droptrigger
000125      execsql " INSERT INTO $tbl VALUES('1', '2') "
000126      set ::triggers_fired
000127    } $after
000128  }
000129  
000130  # EVIDENCE-OF: R-04950-25529 Once removed, the trigger definition is no
000131  # longer present in the sqlite_schema (or sqlite_temp_schema) table and
000132  # is not fired by any subsequent INSERT, UPDATE or DELETE statements.
000133  #
000134  #   Test cases e_droptrigger-1.* test the first part of this statement
000135  #   (that dropped triggers do not appear in the schema table), and tests
000136  #   droptrigger-2.* test that dropped triggers are not fired by INSERT
000137  #   statements. The following tests verify that they are not fired by
000138  #   UPDATE or DELETE statements.
000139  #
000140  foreach {tn tbl droptrigger before after} {
000141    1   t1  "DROP TRIGGER tr1" {temp.tr1}                {}
000142    2   t2  "DROP TRIGGER tr1" {main.tr1 main.tr2}       {main.tr1 main.tr2}
000143    3   t3  "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}
000144  
000145    4   t1  "DROP TRIGGER tr2" {temp.tr1}                {temp.tr1}
000146    5   t2  "DROP TRIGGER tr2" {main.tr1 main.tr2}       {main.tr1}
000147    6   t3  "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}
000148  
000149    7   t1  "DROP TRIGGER tr3" {temp.tr1}                {temp.tr1}
000150    8   t2  "DROP TRIGGER tr3" {main.tr1 main.tr2}       {main.tr1 main.tr2}
000151    9   t3  "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2}
000152  } {
000153  
000154    do_test 3.1.$tn.1 {
000155      droptrigger_reopen_db UPDATE
000156      execsql "UPDATE $tbl SET a = 'abc'"
000157      set ::triggers_fired
000158    } $before
000159  
000160    do_test 3.1.$tn.2 {
000161      droptrigger_reopen_db UPDATE
000162      execsql $droptrigger
000163      execsql "UPDATE $tbl SET a = 'abc'"
000164      set ::triggers_fired
000165    } $after
000166  }
000167  foreach {tn tbl droptrigger before after} {
000168    1   t1  "DROP TRIGGER tr1" {temp.tr1}                {}
000169    2   t2  "DROP TRIGGER tr1" {main.tr1 main.tr2}       {main.tr1 main.tr2}
000170    3   t3  "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}
000171  
000172    4   t1  "DROP TRIGGER tr2" {temp.tr1}                {temp.tr1}
000173    5   t2  "DROP TRIGGER tr2" {main.tr1 main.tr2}       {main.tr1}
000174    6   t3  "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}
000175  
000176    7   t1  "DROP TRIGGER tr3" {temp.tr1}                {temp.tr1}
000177    8   t2  "DROP TRIGGER tr3" {main.tr1 main.tr2}       {main.tr1 main.tr2}
000178    9   t3  "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2}
000179  } {
000180  
000181    do_test 3.2.$tn.1 {
000182      droptrigger_reopen_db DELETE
000183      execsql "DELETE FROM $tbl"
000184      set ::triggers_fired
000185    } $before
000186  
000187    do_test 3.2.$tn.2 {
000188      droptrigger_reopen_db DELETE
000189      execsql $droptrigger
000190      execsql "DELETE FROM $tbl"
000191      set ::triggers_fired
000192    } $after
000193  }
000194  
000195  # EVIDENCE-OF: R-37808-62273 Note that triggers are automatically
000196  # dropped when the associated table is dropped.
000197  #
000198  do_test 4.1 {
000199    droptrigger_reopen_db
000200    list_all_triggers
000201  } {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
000202  do_test 4.2 {
000203    droptrigger_reopen_db
000204    execsql "DROP TABLE t1"
000205    list_all_triggers
000206  } {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
000207  do_test 4.3 {
000208    droptrigger_reopen_db
000209    execsql "DROP TABLE t1"
000210    list_all_triggers
000211  } {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
000212  do_test 4.4 {
000213    droptrigger_reopen_db
000214    execsql "DROP TABLE t1"
000215    list_all_triggers
000216  } {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
000217  
000218  finish_test