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-50239-29811 Once removed, the trigger definition is no
000131 # longer present in the sqlite_master (or sqlite_temp_master) 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