1 # 2010 November 29 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # 12 # This file implements tests to verify that the "testable statements" in 13 # the lang_droptrigger.html document are correct. 14 # 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 set ::testprefix e_droptrigger 19 20 ifcapable !trigger { finish_test ; return } 21 22 proc do_droptrigger_tests {nm args} { 23 uplevel do_select_tests [list e_createtable-$nm] $args 24 } 25 26 proc list_all_triggers {{db db}} { 27 set res [list] 28 $db eval { PRAGMA database_list } { 29 if {$name == "temp"} { 30 set tbl sqlite_temp_master 31 } else { 32 set tbl "$name.sqlite_master" 33 } 34 lappend res {*}[ 35 db eval "SELECT '$name.' || name FROM $tbl WHERE type = 'trigger'" 36 ] 37 } 38 set res 39 } 40 41 42 proc droptrigger_reopen_db {{event INSERT}} { 43 db close 44 forcedelete test.db test.db2 45 sqlite3 db test.db 46 47 set ::triggers_fired [list] 48 proc r {x} { lappend ::triggers_fired $x } 49 db func r r 50 51 db eval " 52 ATTACH 'test.db2' AS aux; 53 54 CREATE TEMP TABLE t1(a, b); 55 INSERT INTO t1 VALUES('a', 'b'); 56 CREATE TRIGGER tr1 AFTER $event ON t1 BEGIN SELECT r('temp.tr1') ; END; 57 58 CREATE TABLE t2(a, b); 59 INSERT INTO t2 VALUES('a', 'b'); 60 CREATE TRIGGER tr1 BEFORE $event ON t2 BEGIN SELECT r('main.tr1') ; END; 61 CREATE TRIGGER tr2 AFTER $event ON t2 BEGIN SELECT r('main.tr2') ; END; 62 63 CREATE TABLE aux.t3(a, b); 64 INSERT INTO t3 VALUES('a', 'b'); 65 CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END; 66 CREATE TRIGGER aux.tr2 AFTER $event ON t3 BEGIN SELECT r('aux.tr2') ; END; 67 CREATE TRIGGER aux.tr3 AFTER $event ON t3 BEGIN SELECT r('aux.tr3') ; END; 68 " 69 } 70 71 72 # EVIDENCE-OF: R-52650-16855 -- syntax diagram drop-trigger-stmt 73 # 74 do_droptrigger_tests 1.1 -repair { 75 droptrigger_reopen_db 76 } -tclquery { 77 list_all_triggers 78 } { 79 1 "DROP TRIGGER main.tr1" 80 {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} 81 2 "DROP TRIGGER IF EXISTS main.tr1" 82 {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} 83 3 "DROP TRIGGER tr1" 84 {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} 85 4 "DROP TRIGGER IF EXISTS tr1" 86 {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} 87 88 5 "DROP TRIGGER aux.tr1" 89 {main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3} 90 6 "DROP TRIGGER IF EXISTS aux.tr1" 91 {main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3} 92 93 7 "DROP TRIGGER IF EXISTS aux.xxx" 94 {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} 95 8 "DROP TRIGGER IF EXISTS aux.xxx" 96 {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} 97 } 98 99 # EVIDENCE-OF: R-61172-15671 The DROP TRIGGER statement removes a 100 # trigger created by the CREATE TRIGGER statement. 101 # 102 foreach {tn tbl droptrigger before after} { 103 1 t1 "DROP TRIGGER tr1" {temp.tr1} {} 104 2 t2 "DROP TRIGGER tr1" {main.tr1 main.tr2} {main.tr1 main.tr2} 105 3 t3 "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} 106 107 4 t1 "DROP TRIGGER tr2" {temp.tr1} {temp.tr1} 108 5 t2 "DROP TRIGGER tr2" {main.tr1 main.tr2} {main.tr1} 109 6 t3 "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} 110 111 7 t1 "DROP TRIGGER tr3" {temp.tr1} {temp.tr1} 112 8 t2 "DROP TRIGGER tr3" {main.tr1 main.tr2} {main.tr1 main.tr2} 113 9 t3 "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2} 114 } { 115 116 do_test 2.$tn.1 { 117 droptrigger_reopen_db 118 execsql " INSERT INTO $tbl VALUES('1', '2') " 119 set ::triggers_fired 120 } $before 121 122 do_test 2.$tn.2 { 123 droptrigger_reopen_db 124 execsql $droptrigger 125 execsql " INSERT INTO $tbl VALUES('1', '2') " 126 set ::triggers_fired 127 } $after 128 } 129 130 # EVIDENCE-OF: R-50239-29811 Once removed, the trigger definition is no 131 # longer present in the sqlite_master (or sqlite_temp_master) table and 132 # is not fired by any subsequent INSERT, UPDATE or DELETE statements. 133 # 134 # Test cases e_droptrigger-1.* test the first part of this statement 135 # (that dropped triggers do not appear in the schema table), and tests 136 # droptrigger-2.* test that dropped triggers are not fired by INSERT 137 # statements. The following tests verify that they are not fired by 138 # UPDATE or DELETE statements. 139 # 140 foreach {tn tbl droptrigger before after} { 141 1 t1 "DROP TRIGGER tr1" {temp.tr1} {} 142 2 t2 "DROP TRIGGER tr1" {main.tr1 main.tr2} {main.tr1 main.tr2} 143 3 t3 "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} 144 145 4 t1 "DROP TRIGGER tr2" {temp.tr1} {temp.tr1} 146 5 t2 "DROP TRIGGER tr2" {main.tr1 main.tr2} {main.tr1} 147 6 t3 "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} 148 149 7 t1 "DROP TRIGGER tr3" {temp.tr1} {temp.tr1} 150 8 t2 "DROP TRIGGER tr3" {main.tr1 main.tr2} {main.tr1 main.tr2} 151 9 t3 "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2} 152 } { 153 154 do_test 3.1.$tn.1 { 155 droptrigger_reopen_db UPDATE 156 execsql "UPDATE $tbl SET a = 'abc'" 157 set ::triggers_fired 158 } $before 159 160 do_test 3.1.$tn.2 { 161 droptrigger_reopen_db UPDATE 162 execsql $droptrigger 163 execsql "UPDATE $tbl SET a = 'abc'" 164 set ::triggers_fired 165 } $after 166 } 167 foreach {tn tbl droptrigger before after} { 168 1 t1 "DROP TRIGGER tr1" {temp.tr1} {} 169 2 t2 "DROP TRIGGER tr1" {main.tr1 main.tr2} {main.tr1 main.tr2} 170 3 t3 "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} 171 172 4 t1 "DROP TRIGGER tr2" {temp.tr1} {temp.tr1} 173 5 t2 "DROP TRIGGER tr2" {main.tr1 main.tr2} {main.tr1} 174 6 t3 "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2} 175 176 7 t1 "DROP TRIGGER tr3" {temp.tr1} {temp.tr1} 177 8 t2 "DROP TRIGGER tr3" {main.tr1 main.tr2} {main.tr1 main.tr2} 178 9 t3 "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2} 179 } { 180 181 do_test 3.2.$tn.1 { 182 droptrigger_reopen_db DELETE 183 execsql "DELETE FROM $tbl" 184 set ::triggers_fired 185 } $before 186 187 do_test 3.2.$tn.2 { 188 droptrigger_reopen_db DELETE 189 execsql $droptrigger 190 execsql "DELETE FROM $tbl" 191 set ::triggers_fired 192 } $after 193 } 194 195 # EVIDENCE-OF: R-37808-62273 Note that triggers are automatically 196 # dropped when the associated table is dropped. 197 # 198 do_test 4.1 { 199 droptrigger_reopen_db 200 list_all_triggers 201 } {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} 202 do_test 4.2 { 203 droptrigger_reopen_db 204 execsql "DROP TABLE t1" 205 list_all_triggers 206 } {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} 207 do_test 4.3 { 208 droptrigger_reopen_db 209 execsql "DROP TABLE t1" 210 list_all_triggers 211 } {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} 212 do_test 4.4 { 213 droptrigger_reopen_db 214 execsql "DROP TABLE t1" 215 list_all_triggers 216 } {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3} 217 218 finish_test 219
