xref: /sqlite-3.40.0/test/trigger3.test (revision 0fa8ddbd) 
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
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16ifcapable {!trigger} {
17  finish_test
18  return
19}
20
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
37do_test trigger3-1.1 {
38    catchsql {
39	BEGIN;
40        INSERT INTO tbl VALUES (5, 5, 6);
41        INSERT INTO tbl VALUES (1, 5, 6);
42    }
43} {1 {Trigger abort}}
44do_test trigger3-1.2 {
45    execsql {
46	SELECT * FROM tbl;
47	ROLLBACK;
48    }
49} {5 5 6}
50do_test trigger3-1.3 {
51    execsql {SELECT * FROM tbl}
52} {}
53
54# FAIL
55do_test trigger3-2.1 {
56    catchsql {
57	BEGIN;
58        INSERT INTO tbl VALUES (5, 5, 6);
59        INSERT INTO tbl VALUES (2, 5, 6);
60    }
61} {1 {Trigger fail}}
62do_test trigger3-2.2 {
63    execsql {
64	SELECT * FROM tbl;
65	ROLLBACK;
66    }
67} {5 5 6 2 5 6}
68# ROLLBACK
69do_test trigger3-3.1 {
70    catchsql {
71	BEGIN;
72        INSERT INTO tbl VALUES (5, 5, 6);
73        INSERT INTO tbl VALUES (3, 5, 6);
74    }
75} {1 {Trigger rollback}}
76do_test trigger3-3.2 {
77    execsql {
78	SELECT * FROM tbl;
79    }
80} {}
81# IGNORE
82do_test trigger3-4.1 {
83    catchsql {
84	BEGIN;
85        INSERT INTO tbl VALUES (5, 5, 6);
86        INSERT INTO tbl VALUES (4, 5, 6);
87    }
88} {0 {}}
89do_test trigger3-4.2 {
90    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}
110do_test trigger3-5.1 {
111    execsql {
112	UPDATE tbl SET c = 10;
113	SELECT * FROM tbl;
114    }
115} {1 2 3 4 5 10}
116do_test trigger3-5.2 {
117    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}
131do_test trigger3-6 {
132    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
140
141ifcapable view {
142
143execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
144execsql {
145    CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
146	SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
147	            WHEN (new.a = 2) THEN RAISE(IGNORE)
148	            WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
149    END;
150}
151
152do_test trigger3-7.1 {
153    catchsql {
154	INSERT INTO tbl_view VALUES(1, 2, 3);
155    }
156} {1 {View rollback}}
157do_test trigger3-7.2 {
158    catchsql {
159	INSERT INTO tbl_view VALUES(2, 2, 3);
160    }
161} {0 {}}
162do_test trigger3-7.3 {
163    catchsql {
164	INSERT INTO tbl_view VALUES(3, 2, 3);
165    }
166} {1 {View abort}}
167
168} ;# ifcapable view
169
170integrity_check trigger3-8.1
171
172catchsql { DROP TABLE tbl; }
173catchsql { DROP TABLE tbl2; }
174catchsql { DROP VIEW tbl_view; }
175
176finish_test
177