xref: /sqlite-3.40.0/test/trigger3.test (revision 8a29dfde) 
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
82# Verify that a ROLLBACK trigger works like a FAIL trigger if
83# we are not within a transaction.  Ticket #3035.
84#
85do_test trigger3-3.3 {
86    catchsql {COMMIT}
87    catchsql {
88        INSERT INTO tbl VALUES (3, 9, 10);
89    }
90} {1 {Trigger rollback}}
91do_test trigger3-3.4 {
92    execsql {SELECT * FROM tbl}
93} {}
94
95# IGNORE
96do_test trigger3-4.1 {
97    catchsql {
98	BEGIN;
99        INSERT INTO tbl VALUES (5, 5, 6);
100        INSERT INTO tbl VALUES (4, 5, 6);
101    }
102} {0 {}}
103do_test trigger3-4.2 {
104    execsql {
105	SELECT * FROM tbl;
106	ROLLBACK;
107    }
108} {5 5 6}
109
110# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
111execsql {DROP TABLE tbl;}
112execsql {CREATE TABLE tbl (a, b, c);}
113execsql {INSERT INTO tbl VALUES(1, 2, 3);}
114execsql {INSERT INTO tbl VALUES(4, 5, 6);}
115execsql {
116    CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
117	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
118    END;
119
120    CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
121	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
122    END;
123}
124do_test trigger3-5.1 {
125    execsql {
126	UPDATE tbl SET c = 10;
127	SELECT * FROM tbl;
128    }
129} {1 2 3 4 5 10}
130do_test trigger3-5.2 {
131    execsql {
132	DELETE FROM tbl;
133	SELECT * FROM tbl;
134    }
135} {1 2 3}
136
137# Check that RAISE(IGNORE) works correctly for nested triggers:
138execsql {CREATE TABLE tbl2(a, b, c)}
139execsql {
140    CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
141	UPDATE tbl SET c = 10;
142        INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
143    END;
144}
145do_test trigger3-6 {
146    execsql {
147	INSERT INTO tbl2 VALUES (1, 2, 3);
148	SELECT * FROM tbl2;
149	SELECT * FROM tbl;
150    }
151} {1 2 3 1 2 3 1 2 3}
152
153# Check that things also work for view-triggers
154
155ifcapable view {
156
157execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
158execsql {
159    CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
160	SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
161	            WHEN (new.a = 2) THEN RAISE(IGNORE)
162	            WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
163    END;
164}
165
166do_test trigger3-7.1 {
167    catchsql {
168	INSERT INTO tbl_view VALUES(1, 2, 3);
169    }
170} {1 {View rollback}}
171do_test trigger3-7.2 {
172    catchsql {
173	INSERT INTO tbl_view VALUES(2, 2, 3);
174    }
175} {0 {}}
176do_test trigger3-7.3 {
177    catchsql {
178	INSERT INTO tbl_view VALUES(3, 2, 3);
179    }
180} {1 {View abort}}
181
182} ;# ifcapable view
183
184integrity_check trigger3-8.1
185
186catchsql { DROP TABLE tbl; }
187catchsql { DROP TABLE tbl2; }
188catchsql { DROP VIEW tbl_view; }
189
190finish_test
191