blob: d13effcf54d828deef104b28ff93ddb748dd6aa6 [file] [log] [blame]
danielk19776f349032002-06-11 02:25:40 +00001# 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
drh35d4c2f2004-06-10 01:30:59 +000013
danielk19776f349032002-06-11 02:25:40 +000014set testdir [file dirname $argv0]
15source $testdir/tester.tcl
drhb7f91642004-10-31 02:22:47 +000016ifcapable {!trigger} {
17 finish_test
18 return
19}
danielk19776f349032002-06-11 02:25:40 +000020
21# Test that we can cause ROLLBACK, FAIL and ABORT correctly
22# catchsql { DROP TABLE tbl; }
23catchsql { CREATE TABLE tbl (a, b, c) }
24
25execsql {
26 CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE
27 WHEN (new.a = 4) THEN RAISE(IGNORE) END;
28 END;
29
30 CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE
31 WHEN (new.a = 1) THEN RAISE(ABORT, 'Trigger abort')
32 WHEN (new.a = 2) THEN RAISE(FAIL, 'Trigger fail')
33 WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
34 END;
35}
36# ABORT
drh41a3bd02002-09-14 12:04:56 +000037do_test trigger3-1.1 {
danielk19776f349032002-06-11 02:25:40 +000038 catchsql {
39 BEGIN;
40 INSERT INTO tbl VALUES (5, 5, 6);
41 INSERT INTO tbl VALUES (1, 5, 6);
42 }
43} {1 {Trigger abort}}
drh41a3bd02002-09-14 12:04:56 +000044do_test trigger3-1.2 {
danielk19776f349032002-06-11 02:25:40 +000045 execsql {
46 SELECT * FROM tbl;
47 ROLLBACK;
48 }
49} {5 5 6}
drhdb48ee02003-01-16 13:42:43 +000050do_test trigger3-1.3 {
51 execsql {SELECT * FROM tbl}
52} {}
danielk19776f349032002-06-11 02:25:40 +000053
54# FAIL
drh41a3bd02002-09-14 12:04:56 +000055do_test trigger3-2.1 {
danielk19776f349032002-06-11 02:25:40 +000056 catchsql {
57 BEGIN;
58 INSERT INTO tbl VALUES (5, 5, 6);
59 INSERT INTO tbl VALUES (2, 5, 6);
60 }
61} {1 {Trigger fail}}
drh41a3bd02002-09-14 12:04:56 +000062do_test trigger3-2.2 {
danielk19776f349032002-06-11 02:25:40 +000063 execsql {
64 SELECT * FROM tbl;
65 ROLLBACK;
66 }
67} {5 5 6 2 5 6}
68# ROLLBACK
drh41a3bd02002-09-14 12:04:56 +000069do_test trigger3-3.1 {
danielk19776f349032002-06-11 02:25:40 +000070 catchsql {
71 BEGIN;
72 INSERT INTO tbl VALUES (5, 5, 6);
73 INSERT INTO tbl VALUES (3, 5, 6);
74 }
75} {1 {Trigger rollback}}
drh41a3bd02002-09-14 12:04:56 +000076do_test trigger3-3.2 {
danielk19776f349032002-06-11 02:25:40 +000077 execsql {
78 SELECT * FROM tbl;
danielk19776f349032002-06-11 02:25:40 +000079 }
80} {}
81# IGNORE
drh41a3bd02002-09-14 12:04:56 +000082do_test trigger3-4.1 {
danielk19776f349032002-06-11 02:25:40 +000083 catchsql {
84 BEGIN;
85 INSERT INTO tbl VALUES (5, 5, 6);
86 INSERT INTO tbl VALUES (4, 5, 6);
87 }
88} {0 {}}
drh41a3bd02002-09-14 12:04:56 +000089do_test trigger3-4.2 {
danielk19776f349032002-06-11 02:25:40 +000090 execsql {
91 SELECT * FROM tbl;
92 ROLLBACK;
93 }
94} {5 5 6}
95
96# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
97execsql {DROP TABLE tbl;}
98execsql {CREATE TABLE tbl (a, b, c);}
99execsql {INSERT INTO tbl VALUES(1, 2, 3);}
100execsql {INSERT INTO tbl VALUES(4, 5, 6);}
101execsql {
102 CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
103 SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
104 END;
105
106 CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
107 SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
108 END;
109}
drh41a3bd02002-09-14 12:04:56 +0000110do_test trigger3-5.1 {
danielk19776f349032002-06-11 02:25:40 +0000111 execsql {
112 UPDATE tbl SET c = 10;
113 SELECT * FROM tbl;
114 }
115} {1 2 3 4 5 10}
drh41a3bd02002-09-14 12:04:56 +0000116do_test trigger3-5.2 {
danielk19776f349032002-06-11 02:25:40 +0000117 execsql {
118 DELETE FROM tbl;
119 SELECT * FROM tbl;
120 }
121} {1 2 3}
122
123# Check that RAISE(IGNORE) works correctly for nested triggers:
124execsql {CREATE TABLE tbl2(a, b, c)}
125execsql {
126 CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
127 UPDATE tbl SET c = 10;
128 INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
129 END;
130}
drh41a3bd02002-09-14 12:04:56 +0000131do_test trigger3-6 {
danielk19776f349032002-06-11 02:25:40 +0000132 execsql {
133 INSERT INTO tbl2 VALUES (1, 2, 3);
134 SELECT * FROM tbl2;
135 SELECT * FROM tbl;
136 }
137} {1 2 3 1 2 3 1 2 3}
138
139# Check that things also work for view-triggers
140execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
141execsql {
142 CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
143 SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
144 WHEN (new.a = 2) THEN RAISE(IGNORE)
145 WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
146 END;
147}
148
drh41a3bd02002-09-14 12:04:56 +0000149do_test trigger3-7.1 {
danielk19776f349032002-06-11 02:25:40 +0000150 catchsql {
151 INSERT INTO tbl_view VALUES(1, 2, 3);
152 }
153} {1 {View rollback}}
drh41a3bd02002-09-14 12:04:56 +0000154do_test trigger3-7.2 {
danielk19776f349032002-06-11 02:25:40 +0000155 catchsql {
156 INSERT INTO tbl_view VALUES(2, 2, 3);
157 }
158} {0 {}}
drh41a3bd02002-09-14 12:04:56 +0000159do_test trigger3-7.3 {
danielk19776f349032002-06-11 02:25:40 +0000160 catchsql {
161 INSERT INTO tbl_view VALUES(3, 2, 3);
162 }
163} {1 {View abort}}
164
drhed717fe2003-06-15 23:42:24 +0000165integrity_check trigger3-8.1
166
danielk19776f349032002-06-11 02:25:40 +0000167catchsql { DROP TABLE tbl; }
168catchsql { DROP TABLE tbl2; }
drhe0bc4042002-06-25 01:09:11 +0000169catchsql { DROP VIEW tbl_view; }
danielk19776f349032002-06-11 02:25:40 +0000170
drhe0bc4042002-06-25 01:09:11 +0000171finish_test