wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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