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