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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15 16# Test that we can cause ROLLBACK, FAIL and ABORT correctly 17# catchsql { DROP TABLE tbl; } 18catchsql { CREATE TABLE tbl (a, b, c) } 19 20execsql { 21 CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE 22 WHEN (new.a = 4) THEN RAISE(IGNORE) END; 23 END; 24 25 CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE 26 WHEN (new.a = 1) THEN RAISE(ABORT, 'Trigger abort') 27 WHEN (new.a = 2) THEN RAISE(FAIL, 'Trigger fail') 28 WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END; 29 END; 30} 31# ABORT 32do_test trig-raise-1.1 { 33 catchsql { 34 BEGIN; 35 INSERT INTO tbl VALUES (5, 5, 6); 36 INSERT INTO tbl VALUES (1, 5, 6); 37 } 38} {1 {Trigger abort}} 39 40do_test trig-raise-1.2 { 41 execsql { 42 SELECT * FROM tbl; 43 ROLLBACK; 44 } 45} {5 5 6} 46 47# FAIL 48do_test trig-raise-2.1 { 49 catchsql { 50 BEGIN; 51 INSERT INTO tbl VALUES (5, 5, 6); 52 INSERT INTO tbl VALUES (2, 5, 6); 53 } 54} {1 {Trigger fail}} 55do_test trig-raise-2.2 { 56 execsql { 57 SELECT * FROM tbl; 58 ROLLBACK; 59 } 60} {5 5 6 2 5 6} 61# ROLLBACK 62do_test trig-raise-3.1 { 63 catchsql { 64 BEGIN; 65 INSERT INTO tbl VALUES (5, 5, 6); 66 INSERT INTO tbl VALUES (3, 5, 6); 67 } 68} {1 {Trigger rollback}} 69do_test trig-raise-3.2 { 70 execsql { 71 SELECT * FROM tbl; 72 ROLLBACK; 73 } 74} {} 75# IGNORE 76do_test trig-raise-4.1 { 77 catchsql { 78 BEGIN; 79 INSERT INTO tbl VALUES (5, 5, 6); 80 INSERT INTO tbl VALUES (4, 5, 6); 81 } 82} {0 {}} 83do_test trig-raise-4.2 { 84 execsql { 85 SELECT * FROM tbl; 86 ROLLBACK; 87 } 88} {5 5 6} 89 90# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE 91execsql {DROP TABLE tbl;} 92execsql {CREATE TABLE tbl (a, b, c);} 93execsql {INSERT INTO tbl VALUES(1, 2, 3);} 94execsql {INSERT INTO tbl VALUES(4, 5, 6);} 95execsql { 96 CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN 97 SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; 98 END; 99 100 CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN 101 SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; 102 END; 103} 104do_test trig-raise-5.1 { 105 execsql { 106 UPDATE tbl SET c = 10; 107 SELECT * FROM tbl; 108 } 109} {1 2 3 4 5 10} 110do_test trig-raise-5.2 { 111 execsql { 112 DELETE FROM tbl; 113 SELECT * FROM tbl; 114 } 115} {1 2 3} 116 117# Check that RAISE(IGNORE) works correctly for nested triggers: 118execsql {CREATE TABLE tbl2(a, b, c)} 119execsql { 120 CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN 121 UPDATE tbl SET c = 10; 122 INSERT INTO tbl2 VALUES (new.a, new.b, new.c); 123 END; 124} 125do_test trig-raise-6 { 126 execsql { 127 INSERT INTO tbl2 VALUES (1, 2, 3); 128 SELECT * FROM tbl2; 129 SELECT * FROM tbl; 130 } 131} {1 2 3 1 2 3 1 2 3} 132 133# Check that things also work for view-triggers 134execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl} 135execsql { 136 CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN 137 SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback') 138 WHEN (new.a = 2) THEN RAISE(IGNORE) 139 WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END; 140 END; 141} 142 143do_test trig-raise-7.1 { 144 catchsql { 145 INSERT INTO tbl_view VALUES(1, 2, 3); 146 } 147} {1 {View rollback}} 148do_test trig-raise-7.2 { 149 catchsql { 150 INSERT INTO tbl_view VALUES(2, 2, 3); 151 } 152} {0 {}} 153do_test trig-raise-7.3 { 154 catchsql { 155 INSERT INTO tbl_view VALUES(3, 2, 3); 156 } 157} {1 {View abort}} 158 159catchsql { DROP TABLE tbl; } 160catchsql { DROP TABLE tbl2; } 161catchsql { DROP VIEW tbl_view; } 162 163 164