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