Annotation of embedaddon/sqlite3/test/e_droptrigger.test, revision 1.1
1.1 ! misho 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-27975-10951 -- 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_droptrigger.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test