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