1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/trigger3.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,190 @@
1.4 +# The author disclaims copyright to this source code. In place of
1.5 +# a legal notice, here is a blessing:
1.6 +#
1.7 +# May you do good and not evil.
1.8 +# May you find forgiveness for yourself and forgive others.
1.9 +# May you share freely, never taking more than you give.
1.10 +#
1.11 +#***********************************************************************
1.12 +#
1.13 +# This file tests the RAISE() function.
1.14 +#
1.15 +
1.16 +
1.17 +set testdir [file dirname $argv0]
1.18 +source $testdir/tester.tcl
1.19 +ifcapable {!trigger} {
1.20 + finish_test
1.21 + return
1.22 +}
1.23 +
1.24 +# Test that we can cause ROLLBACK, FAIL and ABORT correctly
1.25 +# catchsql { DROP TABLE tbl; }
1.26 +catchsql { CREATE TABLE tbl (a, b, c) }
1.27 +
1.28 +execsql {
1.29 + CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE
1.30 + WHEN (new.a = 4) THEN RAISE(IGNORE) END;
1.31 + END;
1.32 +
1.33 + CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE
1.34 + WHEN (new.a = 1) THEN RAISE(ABORT, 'Trigger abort')
1.35 + WHEN (new.a = 2) THEN RAISE(FAIL, 'Trigger fail')
1.36 + WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
1.37 + END;
1.38 +}
1.39 +# ABORT
1.40 +do_test trigger3-1.1 {
1.41 + catchsql {
1.42 + BEGIN;
1.43 + INSERT INTO tbl VALUES (5, 5, 6);
1.44 + INSERT INTO tbl VALUES (1, 5, 6);
1.45 + }
1.46 +} {1 {Trigger abort}}
1.47 +do_test trigger3-1.2 {
1.48 + execsql {
1.49 + SELECT * FROM tbl;
1.50 + ROLLBACK;
1.51 + }
1.52 +} {5 5 6}
1.53 +do_test trigger3-1.3 {
1.54 + execsql {SELECT * FROM tbl}
1.55 +} {}
1.56 +
1.57 +# FAIL
1.58 +do_test trigger3-2.1 {
1.59 + catchsql {
1.60 + BEGIN;
1.61 + INSERT INTO tbl VALUES (5, 5, 6);
1.62 + INSERT INTO tbl VALUES (2, 5, 6);
1.63 + }
1.64 +} {1 {Trigger fail}}
1.65 +do_test trigger3-2.2 {
1.66 + execsql {
1.67 + SELECT * FROM tbl;
1.68 + ROLLBACK;
1.69 + }
1.70 +} {5 5 6 2 5 6}
1.71 +# ROLLBACK
1.72 +do_test trigger3-3.1 {
1.73 + catchsql {
1.74 + BEGIN;
1.75 + INSERT INTO tbl VALUES (5, 5, 6);
1.76 + INSERT INTO tbl VALUES (3, 5, 6);
1.77 + }
1.78 +} {1 {Trigger rollback}}
1.79 +do_test trigger3-3.2 {
1.80 + execsql {
1.81 + SELECT * FROM tbl;
1.82 + }
1.83 +} {}
1.84 +
1.85 +# Verify that a ROLLBACK trigger works like a FAIL trigger if
1.86 +# we are not within a transaction. Ticket #3035.
1.87 +#
1.88 +do_test trigger3-3.3 {
1.89 + catchsql {COMMIT}
1.90 + catchsql {
1.91 + INSERT INTO tbl VALUES (3, 9, 10);
1.92 + }
1.93 +} {1 {Trigger rollback}}
1.94 +do_test trigger3-3.4 {
1.95 + execsql {SELECT * FROM tbl}
1.96 +} {}
1.97 +
1.98 +# IGNORE
1.99 +do_test trigger3-4.1 {
1.100 + catchsql {
1.101 + BEGIN;
1.102 + INSERT INTO tbl VALUES (5, 5, 6);
1.103 + INSERT INTO tbl VALUES (4, 5, 6);
1.104 + }
1.105 +} {0 {}}
1.106 +do_test trigger3-4.2 {
1.107 + execsql {
1.108 + SELECT * FROM tbl;
1.109 + ROLLBACK;
1.110 + }
1.111 +} {5 5 6}
1.112 +
1.113 +# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
1.114 +execsql {DROP TABLE tbl;}
1.115 +execsql {CREATE TABLE tbl (a, b, c);}
1.116 +execsql {INSERT INTO tbl VALUES(1, 2, 3);}
1.117 +execsql {INSERT INTO tbl VALUES(4, 5, 6);}
1.118 +execsql {
1.119 + CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
1.120 + SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
1.121 + END;
1.122 +
1.123 + CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
1.124 + SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
1.125 + END;
1.126 +}
1.127 +do_test trigger3-5.1 {
1.128 + execsql {
1.129 + UPDATE tbl SET c = 10;
1.130 + SELECT * FROM tbl;
1.131 + }
1.132 +} {1 2 3 4 5 10}
1.133 +do_test trigger3-5.2 {
1.134 + execsql {
1.135 + DELETE FROM tbl;
1.136 + SELECT * FROM tbl;
1.137 + }
1.138 +} {1 2 3}
1.139 +
1.140 +# Check that RAISE(IGNORE) works correctly for nested triggers:
1.141 +execsql {CREATE TABLE tbl2(a, b, c)}
1.142 +execsql {
1.143 + CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
1.144 + UPDATE tbl SET c = 10;
1.145 + INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
1.146 + END;
1.147 +}
1.148 +do_test trigger3-6 {
1.149 + execsql {
1.150 + INSERT INTO tbl2 VALUES (1, 2, 3);
1.151 + SELECT * FROM tbl2;
1.152 + SELECT * FROM tbl;
1.153 + }
1.154 +} {1 2 3 1 2 3 1 2 3}
1.155 +
1.156 +# Check that things also work for view-triggers
1.157 +
1.158 +ifcapable view {
1.159 +
1.160 +execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
1.161 +execsql {
1.162 + CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
1.163 + SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
1.164 + WHEN (new.a = 2) THEN RAISE(IGNORE)
1.165 + WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
1.166 + END;
1.167 +}
1.168 +
1.169 +do_test trigger3-7.1 {
1.170 + catchsql {
1.171 + INSERT INTO tbl_view VALUES(1, 2, 3);
1.172 + }
1.173 +} {1 {View rollback}}
1.174 +do_test trigger3-7.2 {
1.175 + catchsql {
1.176 + INSERT INTO tbl_view VALUES(2, 2, 3);
1.177 + }
1.178 +} {0 {}}
1.179 +do_test trigger3-7.3 {
1.180 + catchsql {
1.181 + INSERT INTO tbl_view VALUES(3, 2, 3);
1.182 + }
1.183 +} {1 {View abort}}
1.184 +
1.185 +} ;# ifcapable view
1.186 +
1.187 +integrity_check trigger3-8.1
1.188 +
1.189 +catchsql { DROP TABLE tbl; }
1.190 +catchsql { DROP TABLE tbl2; }
1.191 +catchsql { DROP VIEW tbl_view; }
1.192 +
1.193 +finish_test