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