/trunk/test/trigger3.test |
@@ -0,0 +1,194 @@ |
# 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 |
} |
|
# The tests in this file were written before SQLite supported recursive } |
# trigger invocation, and some tests depend on that to pass. So disable |
# recursive triggers for this file. |
catchsql { pragma recursive_triggers = off } |
|
# Test that we can cause ROLLBACK, FAIL and ABORT correctly |
# |
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; |
} |
} {} |
|
# Verify that a ROLLBACK trigger works like a FAIL trigger if |
# we are not within a transaction. Ticket #3035. |
# |
do_test trigger3-3.3 { |
catchsql {COMMIT} |
catchsql { |
INSERT INTO tbl VALUES (3, 9, 10); |
} |
} {1 {Trigger rollback}} |
do_test trigger3-3.4 { |
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 |