wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # The author disclaims copyright to this source code. In place of |
2 | # a legal notice, here is a blessing: |
||
3 | # |
||
4 | # May you do good and not evil. |
||
5 | # May you find forgiveness for yourself and forgive others. |
||
6 | # May you share freely, never taking more than you give. |
||
7 | # |
||
8 | #*********************************************************************** |
||
9 | # |
||
10 | # This file tests the RAISE() function. |
||
11 | # |
||
12 | |||
13 | |||
14 | set testdir [file dirname $argv0] |
||
15 | source $testdir/tester.tcl |
||
16 | ifcapable {!trigger} { |
||
17 | finish_test |
||
18 | return |
||
19 | } |
||
20 | |||
21 | # The tests in this file were written before SQLite supported recursive } |
||
22 | # trigger invocation, and some tests depend on that to pass. So disable |
||
23 | # recursive triggers for this file. |
||
24 | catchsql { pragma recursive_triggers = off } |
||
25 | |||
26 | # Test that we can cause ROLLBACK, FAIL and ABORT correctly |
||
27 | # |
||
28 | catchsql { CREATE TABLE tbl(a, b ,c) } |
||
29 | execsql { |
||
30 | CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE |
||
31 | WHEN (new.a = 4) THEN RAISE(IGNORE) END; |
||
32 | END; |
||
33 | |||
34 | CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE |
||
35 | WHEN (new.a = 1) THEN RAISE(ABORT, 'Trigger abort') |
||
36 | WHEN (new.a = 2) THEN RAISE(FAIL, 'Trigger fail') |
||
37 | WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END; |
||
38 | END; |
||
39 | } |
||
40 | # ABORT |
||
41 | do_test trigger3-1.1 { |
||
42 | catchsql { |
||
43 | BEGIN; |
||
44 | INSERT INTO tbl VALUES (5, 5, 6); |
||
45 | INSERT INTO tbl VALUES (1, 5, 6); |
||
46 | } |
||
47 | } {1 {Trigger abort}} |
||
48 | do_test trigger3-1.2 { |
||
49 | execsql { |
||
50 | SELECT * FROM tbl; |
||
51 | ROLLBACK; |
||
52 | } |
||
53 | } {5 5 6} |
||
54 | do_test trigger3-1.3 { |
||
55 | execsql {SELECT * FROM tbl} |
||
56 | } {} |
||
57 | |||
58 | # FAIL |
||
59 | do_test trigger3-2.1 { |
||
60 | catchsql { |
||
61 | BEGIN; |
||
62 | INSERT INTO tbl VALUES (5, 5, 6); |
||
63 | INSERT INTO tbl VALUES (2, 5, 6); |
||
64 | } |
||
65 | } {1 {Trigger fail}} |
||
66 | do_test trigger3-2.2 { |
||
67 | execsql { |
||
68 | SELECT * FROM tbl; |
||
69 | ROLLBACK; |
||
70 | } |
||
71 | } {5 5 6 2 5 6} |
||
72 | # ROLLBACK |
||
73 | do_test trigger3-3.1 { |
||
74 | catchsql { |
||
75 | BEGIN; |
||
76 | INSERT INTO tbl VALUES (5, 5, 6); |
||
77 | INSERT INTO tbl VALUES (3, 5, 6); |
||
78 | } |
||
79 | } {1 {Trigger rollback}} |
||
80 | do_test trigger3-3.2 { |
||
81 | execsql { |
||
82 | SELECT * FROM tbl; |
||
83 | } |
||
84 | } {} |
||
85 | |||
86 | # Verify that a ROLLBACK trigger works like a FAIL trigger if |
||
87 | # we are not within a transaction. Ticket #3035. |
||
88 | # |
||
89 | do_test trigger3-3.3 { |
||
90 | catchsql {COMMIT} |
||
91 | catchsql { |
||
92 | INSERT INTO tbl VALUES (3, 9, 10); |
||
93 | } |
||
94 | } {1 {Trigger rollback}} |
||
95 | do_test trigger3-3.4 { |
||
96 | execsql {SELECT * FROM tbl} |
||
97 | } {} |
||
98 | |||
99 | # IGNORE |
||
100 | do_test trigger3-4.1 { |
||
101 | catchsql { |
||
102 | BEGIN; |
||
103 | INSERT INTO tbl VALUES (5, 5, 6); |
||
104 | INSERT INTO tbl VALUES (4, 5, 6); |
||
105 | } |
||
106 | } {0 {}} |
||
107 | do_test trigger3-4.2 { |
||
108 | execsql { |
||
109 | SELECT * FROM tbl; |
||
110 | ROLLBACK; |
||
111 | } |
||
112 | } {5 5 6} |
||
113 | |||
114 | # Check that we can also do RAISE(IGNORE) for UPDATE and DELETE |
||
115 | execsql {DROP TABLE tbl;} |
||
116 | execsql {CREATE TABLE tbl (a, b, c);} |
||
117 | execsql {INSERT INTO tbl VALUES(1, 2, 3);} |
||
118 | execsql {INSERT INTO tbl VALUES(4, 5, 6);} |
||
119 | execsql { |
||
120 | CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN |
||
121 | SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; |
||
122 | END; |
||
123 | |||
124 | CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN |
||
125 | SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; |
||
126 | END; |
||
127 | } |
||
128 | do_test trigger3-5.1 { |
||
129 | execsql { |
||
130 | UPDATE tbl SET c = 10; |
||
131 | SELECT * FROM tbl; |
||
132 | } |
||
133 | } {1 2 3 4 5 10} |
||
134 | do_test trigger3-5.2 { |
||
135 | execsql { |
||
136 | DELETE FROM tbl; |
||
137 | SELECT * FROM tbl; |
||
138 | } |
||
139 | } {1 2 3} |
||
140 | |||
141 | # Check that RAISE(IGNORE) works correctly for nested triggers: |
||
142 | execsql {CREATE TABLE tbl2(a, b, c)} |
||
143 | execsql { |
||
144 | CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN |
||
145 | UPDATE tbl SET c = 10; |
||
146 | INSERT INTO tbl2 VALUES (new.a, new.b, new.c); |
||
147 | END; |
||
148 | } |
||
149 | do_test trigger3-6 { |
||
150 | execsql { |
||
151 | INSERT INTO tbl2 VALUES (1, 2, 3); |
||
152 | SELECT * FROM tbl2; |
||
153 | SELECT * FROM tbl; |
||
154 | } |
||
155 | } {1 2 3 1 2 3 1 2 3} |
||
156 | |||
157 | # Check that things also work for view-triggers |
||
158 | |||
159 | ifcapable view { |
||
160 | |||
161 | execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl} |
||
162 | execsql { |
||
163 | CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN |
||
164 | SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback') |
||
165 | WHEN (new.a = 2) THEN RAISE(IGNORE) |
||
166 | WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END; |
||
167 | END; |
||
168 | } |
||
169 | |||
170 | do_test trigger3-7.1 { |
||
171 | catchsql { |
||
172 | INSERT INTO tbl_view VALUES(1, 2, 3); |
||
173 | } |
||
174 | } {1 {View rollback}} |
||
175 | do_test trigger3-7.2 { |
||
176 | catchsql { |
||
177 | INSERT INTO tbl_view VALUES(2, 2, 3); |
||
178 | } |
||
179 | } {0 {}} |
||
180 | do_test trigger3-7.3 { |
||
181 | catchsql { |
||
182 | INSERT INTO tbl_view VALUES(3, 2, 3); |
||
183 | } |
||
184 | } {1 {View abort}} |
||
185 | |||
186 | } ;# ifcapable view |
||
187 | |||
188 | integrity_check trigger3-8.1 |
||
189 | |||
190 | catchsql { DROP TABLE tbl; } |
||
191 | catchsql { DROP TABLE tbl2; } |
||
192 | catchsql { DROP VIEW tbl_view; } |
||
193 | |||
194 | finish_test |