Return to trigger3.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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