xref: /sqlite-3.40.0/test/trigger3.test (revision 35d4c2f4) 
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
16set sqlite_os_trace 1
17execsql {
18 PRAGMA vdbe_listing=on;
19 PRAGMA sql_trace=on;
20 PRAGMA vdbe_trace=on;
21}
22
23# Test that we can cause ROLLBACK, FAIL and ABORT correctly
24# catchsql { DROP TABLE tbl; }
25catchsql { CREATE TABLE tbl (a, b, c) }
26
27execsql {
28    CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE
29	WHEN (new.a = 4) THEN RAISE(IGNORE) END;
30    END;
31
32    CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE
33	WHEN (new.a = 1) THEN RAISE(ABORT,    'Trigger abort')
34	WHEN (new.a = 2) THEN RAISE(FAIL,     'Trigger fail')
35	WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
36    END;
37}
38# ABORT
39do_test trigger3-1.1 {
40    catchsql {
41	BEGIN;
42        INSERT INTO tbl VALUES (5, 5, 6);
43        INSERT INTO tbl VALUES (1, 5, 6);
44    }
45} {1 {Trigger abort}}
46do_test trigger3-1.2 {
47    execsql {
48	SELECT * FROM tbl;
49	ROLLBACK;
50    }
51} {5 5 6}
52do_test trigger3-1.3 {
53    execsql {SELECT * FROM tbl}
54} {}
55exit
56
57# FAIL
58do_test trigger3-2.1 {
59    catchsql {
60	BEGIN;
61        INSERT INTO tbl VALUES (5, 5, 6);
62        INSERT INTO tbl VALUES (2, 5, 6);
63    }
64} {1 {Trigger fail}}
65do_test trigger3-2.2 {
66    execsql {
67	SELECT * FROM tbl;
68	ROLLBACK;
69    }
70} {5 5 6 2 5 6}
71# ROLLBACK
72do_test trigger3-3.1 {
73    catchsql {
74	BEGIN;
75        INSERT INTO tbl VALUES (5, 5, 6);
76        INSERT INTO tbl VALUES (3, 5, 6);
77    }
78} {1 {Trigger rollback}}
79do_test trigger3-3.2 {
80    execsql {
81	SELECT * FROM tbl;
82    }
83} {}
84# IGNORE
85do_test trigger3-4.1 {
86    catchsql {
87	BEGIN;
88        INSERT INTO tbl VALUES (5, 5, 6);
89        INSERT INTO tbl VALUES (4, 5, 6);
90    }
91} {0 {}}
92do_test trigger3-4.2 {
93    execsql {
94	SELECT * FROM tbl;
95	ROLLBACK;
96    }
97} {5 5 6}
98
99# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
100execsql {DROP TABLE tbl;}
101execsql {CREATE TABLE tbl (a, b, c);}
102execsql {INSERT INTO tbl VALUES(1, 2, 3);}
103execsql {INSERT INTO tbl VALUES(4, 5, 6);}
104execsql {
105    CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
106	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
107    END;
108
109    CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
110	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
111    END;
112}
113do_test trigger3-5.1 {
114    execsql {
115	UPDATE tbl SET c = 10;
116	SELECT * FROM tbl;
117    }
118} {1 2 3 4 5 10}
119do_test trigger3-5.2 {
120    execsql {
121	DELETE FROM tbl;
122	SELECT * FROM tbl;
123    }
124} {1 2 3}
125
126# Check that RAISE(IGNORE) works correctly for nested triggers:
127execsql {CREATE TABLE tbl2(a, b, c)}
128execsql {
129    CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
130	UPDATE tbl SET c = 10;
131        INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
132    END;
133}
134do_test trigger3-6 {
135    execsql {
136	INSERT INTO tbl2 VALUES (1, 2, 3);
137	SELECT * FROM tbl2;
138	SELECT * FROM tbl;
139    }
140} {1 2 3 1 2 3 1 2 3}
141
142# Check that things also work for view-triggers
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
168integrity_check trigger3-8.1
169
170catchsql { DROP TABLE tbl; }
171catchsql { DROP TABLE tbl2; }
172catchsql { DROP VIEW tbl_view; }
173
174finish_test
175