AryaWu/sqlite
0
1# 2010 November 292#3# The author disclaims copyright to this source code. In place of4# 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.tcl18set ::testprefix e_droptrigger19 20ifcapable !trigger { finish_test ; return }21 22proc do_droptrigger_tests {nm args} {23 uplevel do_select_tests [list e_createtable-$nm] $args24}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_master31 } 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 res39}40 41 42proc droptrigger_reopen_db {{event INSERT}} {43 db close44 forcedelete test.db test.db245 sqlite3 db test.db46 47 set ::triggers_fired [list]48 proc r {x} { lappend ::triggers_fired $x }49 db func r r50 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# -- syntax diagram drop-trigger-stmt73#74do_droptrigger_tests 1.1 -repair {75 droptrigger_reopen_db76} -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 a100# 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_db118 execsql " INSERT INTO $tbl VALUES('1', '2') "119 set ::triggers_fired120 } $before121 122 do_test 2.$tn.2 {123 droptrigger_reopen_db124 execsql $droptrigger125 execsql " INSERT INTO $tbl VALUES('1', '2') "126 set ::triggers_fired127 } $after128}129 130# EVIDENCE-OF: R-04950-25529 Once removed, the trigger definition is no131# longer present in the sqlite_schema (or sqlite_temp_schema) table and132# is not fired by any subsequent INSERT, UPDATE or DELETE statements.133#134# Test cases e_droptrigger-1.* test the first part of this statement135# (that dropped triggers do not appear in the schema table), and tests136# droptrigger-2.* test that dropped triggers are not fired by INSERT137# statements. The following tests verify that they are not fired by138# 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 UPDATE156 execsql "UPDATE $tbl SET a = 'abc'"157 set ::triggers_fired158 } $before159 160 do_test 3.1.$tn.2 {161 droptrigger_reopen_db UPDATE162 execsql $droptrigger163 execsql "UPDATE $tbl SET a = 'abc'"164 set ::triggers_fired165 } $after166}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 DELETE183 execsql "DELETE FROM $tbl"184 set ::triggers_fired185 } $before186 187 do_test 3.2.$tn.2 {188 droptrigger_reopen_db DELETE189 execsql $droptrigger190 execsql "DELETE FROM $tbl"191 set ::triggers_fired192 } $after193}194 195# EVIDENCE-OF: R-37808-62273 Note that triggers are automatically196# dropped when the associated table is dropped.197#198do_test 4.1 {199 droptrigger_reopen_db200 list_all_triggers201} {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}202do_test 4.2 {203 droptrigger_reopen_db204 execsql "DROP TABLE t1"205 list_all_triggers206} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}207do_test 4.3 {208 droptrigger_reopen_db209 execsql "DROP TABLE t1"210 list_all_triggers211} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}212do_test 4.4 {213 droptrigger_reopen_db214 execsql "DROP TABLE t1"215 list_all_triggers216} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}217 218finish_test219