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
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15
16# Test that we can cause ROLLBACK, FAIL and ABORT correctly
17# catchsql { DROP TABLE tbl; }
18catchsql { CREATE TABLE tbl (a, b, c) }
19
20execsql {
21    CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE
22	WHEN (new.a = 4) THEN RAISE(IGNORE) END;
23    END;
24
25    CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE
26	WHEN (new.a = 1) THEN RAISE(ABORT,    'Trigger abort')
27	WHEN (new.a = 2) THEN RAISE(FAIL,     'Trigger fail')
28	WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
29    END;
30}
31# ABORT
32do_test trigger3-1.1 {
33    catchsql {
34	BEGIN;
35        INSERT INTO tbl VALUES (5, 5, 6);
36        INSERT INTO tbl VALUES (1, 5, 6);
37    }
38} {1 {Trigger abort}}
39do_test trigger3-1.2 {
40    execsql {
41	SELECT * FROM tbl;
42	ROLLBACK;
43    }
44} {5 5 6}
45do_test trigger3-1.3 {
46    execsql {SELECT * FROM tbl}
47} {}
48
49# FAIL
50do_test trigger3-2.1 {
51    catchsql {
52	BEGIN;
53        INSERT INTO tbl VALUES (5, 5, 6);
54        INSERT INTO tbl VALUES (2, 5, 6);
55    }
56} {1 {Trigger fail}}
57do_test trigger3-2.2 {
58    execsql {
59	SELECT * FROM tbl;
60	ROLLBACK;
61    }
62} {5 5 6 2 5 6}
63# ROLLBACK
64do_test trigger3-3.1 {
65    catchsql {
66	BEGIN;
67        INSERT INTO tbl VALUES (5, 5, 6);
68        INSERT INTO tbl VALUES (3, 5, 6);
69    }
70} {1 {Trigger rollback}}
71do_test trigger3-3.2 {
72    execsql {
73	SELECT * FROM tbl;
74    }
75} {}
76# IGNORE
77do_test trigger3-4.1 {
78    catchsql {
79	BEGIN;
80        INSERT INTO tbl VALUES (5, 5, 6);
81        INSERT INTO tbl VALUES (4, 5, 6);
82    }
83} {0 {}}
84do_test trigger3-4.2 {
85    execsql {
86	SELECT * FROM tbl;
87	ROLLBACK;
88    }
89} {5 5 6}
90
91# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
92execsql {DROP TABLE tbl;}
93execsql {CREATE TABLE tbl (a, b, c);}
94execsql {INSERT INTO tbl VALUES(1, 2, 3);}
95execsql {INSERT INTO tbl VALUES(4, 5, 6);}
96execsql {
97    CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
98	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
99    END;
100
101    CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
102	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
103    END;
104}
105do_test trigger3-5.1 {
106    execsql {
107	UPDATE tbl SET c = 10;
108	SELECT * FROM tbl;
109    }
110} {1 2 3 4 5 10}
111do_test trigger3-5.2 {
112    execsql {
113	DELETE FROM tbl;
114	SELECT * FROM tbl;
115    }
116} {1 2 3}
117
118# Check that RAISE(IGNORE) works correctly for nested triggers:
119execsql {CREATE TABLE tbl2(a, b, c)}
120execsql {
121    CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
122	UPDATE tbl SET c = 10;
123        INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
124    END;
125}
126do_test trigger3-6 {
127    execsql {
128	INSERT INTO tbl2 VALUES (1, 2, 3);
129	SELECT * FROM tbl2;
130	SELECT * FROM tbl;
131    }
132} {1 2 3 1 2 3 1 2 3}
133
134# Check that things also work for view-triggers
135execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
136execsql {
137    CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
138	SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
139	            WHEN (new.a = 2) THEN RAISE(IGNORE)
140	            WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
141    END;
142}
143
144do_test trigger3-7.1 {
145    catchsql {
146	INSERT INTO tbl_view VALUES(1, 2, 3);
147    }
148} {1 {View rollback}}
149do_test trigger3-7.2 {
150    catchsql {
151	INSERT INTO tbl_view VALUES(2, 2, 3);
152    }
153} {0 {}}
154do_test trigger3-7.3 {
155    catchsql {
156	INSERT INTO tbl_view VALUES(3, 2, 3);
157    }
158} {1 {View abort}}
159
160integrity_check trigger3-8.1
161
162catchsql { DROP TABLE tbl; }
163catchsql { DROP TABLE tbl2; }
164catchsql { DROP VIEW tbl_view; }
165
166finish_test
167