Annotation of embedaddon/sqlite3/test/trigger3.test, revision 1.1
1.1 ! misho 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:
! 13:
! 14: set testdir [file dirname $argv0]
! 15: source $testdir/tester.tcl
! 16: ifcapable {!trigger} {
! 17: finish_test
! 18: return
! 19: }
! 20:
! 21: # The tests in this file were written before SQLite supported recursive }
! 22: # trigger invocation, and some tests depend on that to pass. So disable
! 23: # recursive triggers for this file.
! 24: catchsql { pragma recursive_triggers = off }
! 25:
! 26: # Test that we can cause ROLLBACK, FAIL and ABORT correctly
! 27: #
! 28: catchsql { CREATE TABLE tbl(a, b ,c) }
! 29: execsql {
! 30: CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE
! 31: WHEN (new.a = 4) THEN RAISE(IGNORE) END;
! 32: END;
! 33:
! 34: CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE
! 35: WHEN (new.a = 1) THEN RAISE(ABORT, 'Trigger abort')
! 36: WHEN (new.a = 2) THEN RAISE(FAIL, 'Trigger fail')
! 37: WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
! 38: END;
! 39: }
! 40: # ABORT
! 41: do_test trigger3-1.1 {
! 42: catchsql {
! 43: BEGIN;
! 44: INSERT INTO tbl VALUES (5, 5, 6);
! 45: INSERT INTO tbl VALUES (1, 5, 6);
! 46: }
! 47: } {1 {Trigger abort}}
! 48: do_test trigger3-1.2 {
! 49: execsql {
! 50: SELECT * FROM tbl;
! 51: ROLLBACK;
! 52: }
! 53: } {5 5 6}
! 54: do_test trigger3-1.3 {
! 55: execsql {SELECT * FROM tbl}
! 56: } {}
! 57:
! 58: # FAIL
! 59: do_test trigger3-2.1 {
! 60: catchsql {
! 61: BEGIN;
! 62: INSERT INTO tbl VALUES (5, 5, 6);
! 63: INSERT INTO tbl VALUES (2, 5, 6);
! 64: }
! 65: } {1 {Trigger fail}}
! 66: do_test trigger3-2.2 {
! 67: execsql {
! 68: SELECT * FROM tbl;
! 69: ROLLBACK;
! 70: }
! 71: } {5 5 6 2 5 6}
! 72: # ROLLBACK
! 73: do_test trigger3-3.1 {
! 74: catchsql {
! 75: BEGIN;
! 76: INSERT INTO tbl VALUES (5, 5, 6);
! 77: INSERT INTO tbl VALUES (3, 5, 6);
! 78: }
! 79: } {1 {Trigger rollback}}
! 80: do_test trigger3-3.2 {
! 81: execsql {
! 82: SELECT * FROM tbl;
! 83: }
! 84: } {}
! 85:
! 86: # Verify that a ROLLBACK trigger works like a FAIL trigger if
! 87: # we are not within a transaction. Ticket #3035.
! 88: #
! 89: do_test trigger3-3.3 {
! 90: catchsql {COMMIT}
! 91: catchsql {
! 92: INSERT INTO tbl VALUES (3, 9, 10);
! 93: }
! 94: } {1 {Trigger rollback}}
! 95: do_test trigger3-3.4 {
! 96: execsql {SELECT * FROM tbl}
! 97: } {}
! 98:
! 99: # IGNORE
! 100: do_test trigger3-4.1 {
! 101: catchsql {
! 102: BEGIN;
! 103: INSERT INTO tbl VALUES (5, 5, 6);
! 104: INSERT INTO tbl VALUES (4, 5, 6);
! 105: }
! 106: } {0 {}}
! 107: do_test trigger3-4.2 {
! 108: execsql {
! 109: SELECT * FROM tbl;
! 110: ROLLBACK;
! 111: }
! 112: } {5 5 6}
! 113:
! 114: # Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
! 115: execsql {DROP TABLE tbl;}
! 116: execsql {CREATE TABLE tbl (a, b, c);}
! 117: execsql {INSERT INTO tbl VALUES(1, 2, 3);}
! 118: execsql {INSERT INTO tbl VALUES(4, 5, 6);}
! 119: execsql {
! 120: CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN
! 121: SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
! 122: END;
! 123:
! 124: CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
! 125: SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
! 126: END;
! 127: }
! 128: do_test trigger3-5.1 {
! 129: execsql {
! 130: UPDATE tbl SET c = 10;
! 131: SELECT * FROM tbl;
! 132: }
! 133: } {1 2 3 4 5 10}
! 134: do_test trigger3-5.2 {
! 135: execsql {
! 136: DELETE FROM tbl;
! 137: SELECT * FROM tbl;
! 138: }
! 139: } {1 2 3}
! 140:
! 141: # Check that RAISE(IGNORE) works correctly for nested triggers:
! 142: execsql {CREATE TABLE tbl2(a, b, c)}
! 143: execsql {
! 144: CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
! 145: UPDATE tbl SET c = 10;
! 146: INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
! 147: END;
! 148: }
! 149: do_test trigger3-6 {
! 150: execsql {
! 151: INSERT INTO tbl2 VALUES (1, 2, 3);
! 152: SELECT * FROM tbl2;
! 153: SELECT * FROM tbl;
! 154: }
! 155: } {1 2 3 1 2 3 1 2 3}
! 156:
! 157: # Check that things also work for view-triggers
! 158:
! 159: ifcapable view {
! 160:
! 161: execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}
! 162: execsql {
! 163: CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
! 164: SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
! 165: WHEN (new.a = 2) THEN RAISE(IGNORE)
! 166: WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
! 167: END;
! 168: }
! 169:
! 170: do_test trigger3-7.1 {
! 171: catchsql {
! 172: INSERT INTO tbl_view VALUES(1, 2, 3);
! 173: }
! 174: } {1 {View rollback}}
! 175: do_test trigger3-7.2 {
! 176: catchsql {
! 177: INSERT INTO tbl_view VALUES(2, 2, 3);
! 178: }
! 179: } {0 {}}
! 180: do_test trigger3-7.3 {
! 181: catchsql {
! 182: INSERT INTO tbl_view VALUES(3, 2, 3);
! 183: }
! 184: } {1 {View abort}}
! 185:
! 186: } ;# ifcapable view
! 187:
! 188: integrity_check trigger3-8.1
! 189:
! 190: catchsql { DROP TABLE tbl; }
! 191: catchsql { DROP TABLE tbl2; }
! 192: catchsql { DROP VIEW tbl_view; }
! 193:
! 194: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to trigger3.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test