Annotation of embedaddon/sqlite3/test/trigger3.test, revision 1.1.1.1
1.1 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to trigger3.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