2 # The author disclaims copyright to this source code. In place of
3 # a legal notice, here is a blessing:
5 # May you do good and not evil.
6 # May you find forgiveness for yourself and forgive others.
7 # May you share freely, never taking more than you give.
9 #***********************************************************************
11 # This file tests the RAISE() function.
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
17 # Test that we can cause ROLLBACK, FAIL and ABORT correctly
18 # catchsql { DROP TABLE tbl; }
19 catchsql { CREATE TABLE tbl (a, b, c) }
22 CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE
23 WHEN (new.a = 4) THEN RAISE(IGNORE) END;
26 CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE
27 WHEN (new.a = 1) THEN RAISE(ABORT, 'Trigger abort')
28 WHEN (new.a = 2) THEN RAISE(FAIL, 'Trigger fail')
29 WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
33 do_test trigger3-1.1 {
36 INSERT INTO tbl VALUES (5, 5, 6);
37 INSERT INTO tbl VALUES (1, 5, 6);
40 do_test trigger3-1.2 {
46 do_test trigger3-1.3 {
47 execsql {SELECT * FROM tbl}
51 do_test trigger3-2.1 {
54 INSERT INTO tbl VALUES (5, 5, 6);
55 INSERT INTO tbl VALUES (2, 5, 6);
58 do_test trigger3-2.2 {
65 do_test trigger3-3.1 {
68 INSERT INTO tbl VALUES (5, 5, 6);
69 INSERT INTO tbl VALUES (3, 5, 6);
71 } {1 {Trigger rollback}}
72 do_test trigger3-3.2 {
78 do_test trigger3-4.1 {
81 INSERT INTO tbl VALUES (5, 5, 6);
82 INSERT INTO tbl VALUES (4, 5, 6);
85 do_test trigger3-4.2 {
92 # Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
93 execsql {DROP TABLE tbl;}
94 execsql {CREATE TABLE tbl (a, b, c);}
95 execsql {INSERT INTO tbl VALUES(1, 2, 3);}
96 execsql {INSERT INTO tbl VALUES(4, 5, 6);}
98 CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
99 SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
102 CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
103 SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
106 do_test trigger3-5.1 {
108 UPDATE tbl SET c = 10;
112 do_test trigger3-5.2 {
119 # Check that RAISE(IGNORE) works correctly for nested triggers:
120 execsql {CREATE TABLE tbl2(a, b, c)}
122 CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
123 UPDATE tbl SET c = 10;
124 INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
129 INSERT INTO tbl2 VALUES (1, 2, 3);
133 } {1 2 3 1 2 3 1 2 3}
135 # Check that things also work for view-triggers
136 execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
138 CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
139 SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
140 WHEN (new.a = 2) THEN RAISE(IGNORE)
141 WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
145 do_test trigger3-7.1 {
147 INSERT INTO tbl_view VALUES(1, 2, 3);
149 } {1 {View rollback}}
150 do_test trigger3-7.2 {
152 INSERT INTO tbl_view VALUES(2, 2, 3);
155 do_test trigger3-7.3 {
157 INSERT INTO tbl_view VALUES(3, 2, 3);
161 integrity_check trigger3-8.1
163 catchsql { DROP TABLE tbl; }
164 catchsql { DROP TABLE tbl2; }
165 catchsql { DROP VIEW tbl_view; }