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