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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set ::testprefix e_droptrigger 19 20ifcapable !trigger { finish_test ; return } 21 22proc do_droptrigger_tests {nm args} { 23 uplevel do_select_tests [list e_createtable-$nm] $args 24} 25 26proc 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 42proc 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# 74do_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# 102foreach {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# 140foreach {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} 167foreach {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# 198do_test 4.1 { 199 droptrigger_reopen_db 200 list_all_triggers 201} {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3} 202do_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} 207do_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} 212do_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 218finish_test 219