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