# The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file tests the RAISE() function. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable {!trigger} { finish_test return } # Test that we can cause ROLLBACK, FAIL and ABORT correctly # catchsql { DROP TABLE tbl; } catchsql { CREATE TABLE tbl (a, b, c) } execsql { CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE WHEN (new.a = 4) THEN RAISE(IGNORE) END; END; CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE WHEN (new.a = 1) THEN RAISE(ABORT, 'Trigger abort') WHEN (new.a = 2) THEN RAISE(FAIL, 'Trigger fail') WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END; END; } # ABORT do_test trigger3-1.1 { catchsql { BEGIN; INSERT INTO tbl VALUES (5, 5, 6); INSERT INTO tbl VALUES (1, 5, 6); } } {1 {Trigger abort}} do_test trigger3-1.2 { execsql { SELECT * FROM tbl; ROLLBACK; } } {5 5 6} do_test trigger3-1.3 { execsql {SELECT * FROM tbl} } {} # FAIL do_test trigger3-2.1 { catchsql { BEGIN; INSERT INTO tbl VALUES (5, 5, 6); INSERT INTO tbl VALUES (2, 5, 6); } } {1 {Trigger fail}} do_test trigger3-2.2 { execsql { SELECT * FROM tbl; ROLLBACK; } } {5 5 6 2 5 6} # ROLLBACK do_test trigger3-3.1 { catchsql { BEGIN; INSERT INTO tbl VALUES (5, 5, 6); INSERT INTO tbl VALUES (3, 5, 6); } } {1 {Trigger rollback}} do_test trigger3-3.2 { execsql { SELECT * FROM tbl; } } {} # IGNORE do_test trigger3-4.1 { catchsql { BEGIN; INSERT INTO tbl VALUES (5, 5, 6); INSERT INTO tbl VALUES (4, 5, 6); } } {0 {}} do_test trigger3-4.2 { execsql { SELECT * FROM tbl; ROLLBACK; } } {5 5 6} # Check that we can also do RAISE(IGNORE) for UPDATE and DELETE execsql {DROP TABLE tbl;} execsql {CREATE TABLE tbl (a, b, c);} execsql {INSERT INTO tbl VALUES(1, 2, 3);} execsql {INSERT INTO tbl VALUES(4, 5, 6);} execsql { CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; END; CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; END; } do_test trigger3-5.1 { execsql { UPDATE tbl SET c = 10; SELECT * FROM tbl; } } {1 2 3 4 5 10} do_test trigger3-5.2 { execsql { DELETE FROM tbl; SELECT * FROM tbl; } } {1 2 3} # Check that RAISE(IGNORE) works correctly for nested triggers: execsql {CREATE TABLE tbl2(a, b, c)} execsql { CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN UPDATE tbl SET c = 10; INSERT INTO tbl2 VALUES (new.a, new.b, new.c); END; } do_test trigger3-6 { execsql { INSERT INTO tbl2 VALUES (1, 2, 3); SELECT * FROM tbl2; SELECT * FROM tbl; } } {1 2 3 1 2 3 1 2 3} # Check that things also work for view-triggers ifcapable view { execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl} execsql { CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback') WHEN (new.a = 2) THEN RAISE(IGNORE) WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END; END; } do_test trigger3-7.1 { catchsql { INSERT INTO tbl_view VALUES(1, 2, 3); } } {1 {View rollback}} do_test trigger3-7.2 { catchsql { INSERT INTO tbl_view VALUES(2, 2, 3); } } {0 {}} do_test trigger3-7.3 { catchsql { INSERT INTO tbl_view VALUES(3, 2, 3); } } {1 {View abort}} } ;# ifcapable view integrity_check trigger3-8.1 catchsql { DROP TABLE tbl; } catchsql { DROP TABLE tbl2; } catchsql { DROP VIEW tbl_view; } finish_test