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