e_droptrigger.test revision 5821806d5e7f356e8fa4b058a389a808ea183019 
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