1 2#pragma ident "%Z%%M% %I% %E% SMI" 3 4# The author disclaims copyright to this source code. In place of 5# a legal notice, here is a blessing: 6# 7# May you do good and not evil. 8# May you find forgiveness for yourself and forgive others. 9# May you share freely, never taking more than you give. 10# 11#*********************************************************************** 12# 13# This file tests the RAISE() function. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# Test that we can cause ROLLBACK, FAIL and ABORT correctly 20# catchsql { DROP TABLE tbl; } 21catchsql { CREATE TABLE tbl (a, b, c) } 22 23execsql { 24 CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE 25 WHEN (new.a = 4) THEN RAISE(IGNORE) END; 26 END; 27 28 CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE 29 WHEN (new.a = 1) THEN RAISE(ABORT, 'Trigger abort') 30 WHEN (new.a = 2) THEN RAISE(FAIL, 'Trigger fail') 31 WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END; 32 END; 33} 34# ABORT 35do_test trigger3-1.1 { 36 catchsql { 37 BEGIN; 38 INSERT INTO tbl VALUES (5, 5, 6); 39 INSERT INTO tbl VALUES (1, 5, 6); 40 } 41} {1 {Trigger abort}} 42do_test trigger3-1.2 { 43 execsql { 44 SELECT * FROM tbl; 45 ROLLBACK; 46 } 47} {5 5 6} 48do_test trigger3-1.3 { 49 execsql {SELECT * FROM tbl} 50} {} 51 52# FAIL 53do_test trigger3-2.1 { 54 catchsql { 55 BEGIN; 56 INSERT INTO tbl VALUES (5, 5, 6); 57 INSERT INTO tbl VALUES (2, 5, 6); 58 } 59} {1 {Trigger fail}} 60do_test trigger3-2.2 { 61 execsql { 62 SELECT * FROM tbl; 63 ROLLBACK; 64 } 65} {5 5 6 2 5 6} 66# ROLLBACK 67do_test trigger3-3.1 { 68 catchsql { 69 BEGIN; 70 INSERT INTO tbl VALUES (5, 5, 6); 71 INSERT INTO tbl VALUES (3, 5, 6); 72 } 73} {1 {Trigger rollback}} 74do_test trigger3-3.2 { 75 execsql { 76 SELECT * FROM tbl; 77 } 78} {} 79# IGNORE 80do_test trigger3-4.1 { 81 catchsql { 82 BEGIN; 83 INSERT INTO tbl VALUES (5, 5, 6); 84 INSERT INTO tbl VALUES (4, 5, 6); 85 } 86} {0 {}} 87do_test trigger3-4.2 { 88 execsql { 89 SELECT * FROM tbl; 90 ROLLBACK; 91 } 92} {5 5 6} 93 94# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE 95execsql {DROP TABLE tbl;} 96execsql {CREATE TABLE tbl (a, b, c);} 97execsql {INSERT INTO tbl VALUES(1, 2, 3);} 98execsql {INSERT INTO tbl VALUES(4, 5, 6);} 99execsql { 100 CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN 101 SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; 102 END; 103 104 CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN 105 SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; 106 END; 107} 108do_test trigger3-5.1 { 109 execsql { 110 UPDATE tbl SET c = 10; 111 SELECT * FROM tbl; 112 } 113} {1 2 3 4 5 10} 114do_test trigger3-5.2 { 115 execsql { 116 DELETE FROM tbl; 117 SELECT * FROM tbl; 118 } 119} {1 2 3} 120 121# Check that RAISE(IGNORE) works correctly for nested triggers: 122execsql {CREATE TABLE tbl2(a, b, c)} 123execsql { 124 CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN 125 UPDATE tbl SET c = 10; 126 INSERT INTO tbl2 VALUES (new.a, new.b, new.c); 127 END; 128} 129do_test trigger3-6 { 130 execsql { 131 INSERT INTO tbl2 VALUES (1, 2, 3); 132 SELECT * FROM tbl2; 133 SELECT * FROM tbl; 134 } 135} {1 2 3 1 2 3 1 2 3} 136 137# Check that things also work for view-triggers 138execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl} 139execsql { 140 CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN 141 SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback') 142 WHEN (new.a = 2) THEN RAISE(IGNORE) 143 WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END; 144 END; 145} 146 147do_test trigger3-7.1 { 148 catchsql { 149 INSERT INTO tbl_view VALUES(1, 2, 3); 150 } 151} {1 {View rollback}} 152do_test trigger3-7.2 { 153 catchsql { 154 INSERT INTO tbl_view VALUES(2, 2, 3); 155 } 156} {0 {}} 157do_test trigger3-7.3 { 158 catchsql { 159 INSERT INTO tbl_view VALUES(3, 2, 3); 160 } 161} {1 {View abort}} 162 163integrity_check trigger3-8.1 164 165catchsql { DROP TABLE tbl; } 166catchsql { DROP TABLE tbl2; } 167catchsql { DROP VIEW tbl_view; } 168 169finish_test 170