xref: /sqlite-3.40.0/test/trigger3.test (revision cb6acda9) 
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# 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.
24catchsql { pragma recursive_triggers = off }
25
26# Test that we can cause ROLLBACK, FAIL and ABORT correctly
27#
28catchsql { CREATE TABLE tbl(a, b ,c) }
29execsql {
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
41do_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}}
48verify_ex_errcode trigger3-1.1b SQLITE_CONSTRAINT_TRIGGER
49do_test trigger3-1.2 {
50    execsql {
51        SELECT * FROM tbl;
52        ROLLBACK;
53    }
54} {5 5 6}
55do_test trigger3-1.3 {
56    execsql {SELECT * FROM tbl}
57} {}
58
59# FAIL
60do_test trigger3-2.1 {
61    catchsql {
62        BEGIN;
63        INSERT INTO tbl VALUES (5, 5, 6);
64        INSERT INTO tbl VALUES (2, 5, 6);
65    }
66} {1 {Trigger fail}}
67verify_ex_errcode trigger3-2.1b SQLITE_CONSTRAINT_TRIGGER
68do_test trigger3-2.2 {
69    execsql {
70        SELECT * FROM tbl;
71        ROLLBACK;
72    }
73} {5 5 6 2 5 6}
74# ROLLBACK
75do_test trigger3-3.1 {
76    catchsql {
77        BEGIN;
78        INSERT INTO tbl VALUES (5, 5, 6);
79        INSERT INTO tbl VALUES (3, 5, 6);
80    }
81} {1 {Trigger rollback}}
82verify_ex_errcode trigger3-3.1b SQLITE_CONSTRAINT_TRIGGER
83do_test trigger3-3.2 {
84    execsql {
85        SELECT * FROM tbl;
86    }
87} {}
88
89# Verify that a ROLLBACK trigger works like a FAIL trigger if
90# we are not within a transaction.  Ticket #3035.
91#
92do_test trigger3-3.3 {
93    catchsql {COMMIT}
94    catchsql {
95        INSERT INTO tbl VALUES (3, 9, 10);
96    }
97} {1 {Trigger rollback}}
98verify_ex_errcode trigger3-3.3b SQLITE_CONSTRAINT_TRIGGER
99do_test trigger3-3.4 {
100    execsql {SELECT * FROM tbl}
101} {}
102
103# IGNORE
104do_test trigger3-4.1 {
105    catchsql {
106        BEGIN;
107        INSERT INTO tbl VALUES (5, 5, 6);
108        INSERT INTO tbl VALUES (4, 5, 6);
109    }
110} {0 {}}
111do_test trigger3-4.2 {
112    execsql {
113        SELECT * FROM tbl;
114        ROLLBACK;
115    }
116} {5 5 6}
117
118# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
119execsql {DROP TABLE tbl;}
120execsql {CREATE TABLE tbl (a, b, c);}
121execsql {INSERT INTO tbl VALUES(1, 2, 3);}
122execsql {INSERT INTO tbl VALUES(4, 5, 6);}
123execsql {
124    CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
125        SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
126    END;
127
128    CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
129        SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
130    END;
131}
132do_test trigger3-5.1 {
133    execsql {
134        UPDATE tbl SET c = 10;
135        SELECT * FROM tbl;
136    }
137} {1 2 3 4 5 10}
138do_test trigger3-5.2 {
139    execsql {
140        DELETE FROM tbl;
141        SELECT * FROM tbl;
142    }
143} {1 2 3}
144
145# Check that RAISE(IGNORE) works correctly for nested triggers:
146execsql {CREATE TABLE tbl2(a, b, c)}
147execsql {
148    CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
149        UPDATE tbl SET c = 10;
150        INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
151    END;
152}
153do_test trigger3-6 {
154    execsql {
155        INSERT INTO tbl2 VALUES (1, 2, 3);
156        SELECT * FROM tbl2;
157        SELECT * FROM tbl;
158    }
159} {1 2 3 1 2 3 1 2 3}
160
161# Check that things also work for view-triggers
162
163ifcapable view {
164
165execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
166execsql {
167    CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
168        SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
169                    WHEN (new.a = 2) THEN RAISE(IGNORE)
170                    WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
171    END;
172}
173
174do_test trigger3-7.1 {
175    catchsql {
176        INSERT INTO tbl_view VALUES(1, 2, 3);
177    }
178} {1 {View rollback}}
179verify_ex_errcode trigger3-7.1b SQLITE_CONSTRAINT_TRIGGER
180do_test trigger3-7.2 {
181    catchsql {
182        INSERT INTO tbl_view VALUES(2, 2, 3);
183    }
184} {0 {}}
185do_test trigger3-7.3 {
186    catchsql {
187        INSERT INTO tbl_view VALUES(3, 2, 3);
188    }
189} {1 {View abort}}
190verify_ex_errcode trigger3-7.3b SQLITE_CONSTRAINT_TRIGGER
191
192} ;# ifcapable view
193
194integrity_check trigger3-8.1
195
196catchsql { DROP TABLE tbl; }
197catchsql { DROP TABLE tbl2; }
198catchsql { DROP VIEW tbl_view; }
199
200finish_test
201