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 # 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

    37 do_test trigger3-1.1 {

    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}}

    44 do_test trigger3-1.2 {

    45     execsql {

    46 	SELECT * FROM tbl;

    47 	ROLLBACK;

    48     }

    49 } {5 5 6}

    50 do_test trigger3-1.3 {

    51     execsql {SELECT * FROM tbl}

    52 } {}

    53 

    54 # FAIL

    55 do_test trigger3-2.1 {

    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}}

    62 do_test trigger3-2.2 {

    63     execsql {

    64 	SELECT * FROM tbl;

    65 	ROLLBACK;

    66     }

    67 } {5 5 6 2 5 6}

    68 # ROLLBACK

    69 do_test trigger3-3.1 {

    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}}

    76 do_test trigger3-3.2 {

    77     execsql {

    78 	SELECT * FROM tbl;

    79     }

    80 } {}

    81 

    82 # Verify that a ROLLBACK trigger works like a FAIL trigger if

    83 # we are not within a transaction.  Ticket #3035.

    84 #

    85 do_test trigger3-3.3 {

    86     catchsql {COMMIT}

    87     catchsql {

    88         INSERT INTO tbl VALUES (3, 9, 10);

    89     }

    90 } {1 {Trigger rollback}}

    91 do_test trigger3-3.4 {

    92     execsql {SELECT * FROM tbl}

    93 } {}

    94 

    95 # IGNORE

    96 do_test trigger3-4.1 {

    97     catchsql {

    98 	BEGIN;

    99         INSERT INTO tbl VALUES (5, 5, 6);

   100         INSERT INTO tbl VALUES (4, 5, 6);

   101     }

   102 } {0 {}}

   103 do_test trigger3-4.2 {

   104     execsql {

   105 	SELECT * FROM tbl;

   106 	ROLLBACK;

   107     }

   108 } {5 5 6}

   109 

   110 # Check that we can also do RAISE(IGNORE) for UPDATE and DELETE

   111 execsql {DROP TABLE tbl;}

   112 execsql {CREATE TABLE tbl (a, b, c);}

   113 execsql {INSERT INTO tbl VALUES(1, 2, 3);}

   114 execsql {INSERT INTO tbl VALUES(4, 5, 6);}

   115 execsql {

   116     CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN

   117 	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;

   118     END;

   119 

   120     CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN

   121 	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;

   122     END;

   123 }

   124 do_test trigger3-5.1 {

   125     execsql {

   126 	UPDATE tbl SET c = 10;

   127 	SELECT * FROM tbl;

   128     }

   129 } {1 2 3 4 5 10}

   130 do_test trigger3-5.2 {

   131     execsql {

   132 	DELETE FROM tbl;

   133 	SELECT * FROM tbl;

   134     }

   135 } {1 2 3}

   136 

   137 # Check that RAISE(IGNORE) works correctly for nested triggers:

   138 execsql {CREATE TABLE tbl2(a, b, c)}

   139 execsql {

   140     CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN

   141 	UPDATE tbl SET c = 10;

   142         INSERT INTO tbl2 VALUES (new.a, new.b, new.c);

   143     END;

   144 }

   145 do_test trigger3-6 {

   146     execsql {

   147 	INSERT INTO tbl2 VALUES (1, 2, 3);

   148 	SELECT * FROM tbl2;

   149 	SELECT * FROM tbl;

   150     }

   151 } {1 2 3 1 2 3 1 2 3}

   152 

   153 # Check that things also work for view-triggers

   154 

   155 ifcapable view {

   156 

   157 execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl}

   158 execsql {

   159     CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN

   160 	SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')

   161 	            WHEN (new.a = 2) THEN RAISE(IGNORE) 

   162 	            WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;

   163     END;

   164 }

   165 

   166 do_test trigger3-7.1 {

   167     catchsql {

   168 	INSERT INTO tbl_view VALUES(1, 2, 3);

   169     }

   170 } {1 {View rollback}}

   171 do_test trigger3-7.2 {

   172     catchsql {

   173 	INSERT INTO tbl_view VALUES(2, 2, 3);

   174     }

   175 } {0 {}}

   176 do_test trigger3-7.3 {

   177     catchsql {

   178 	INSERT INTO tbl_view VALUES(3, 2, 3);

   179     }

   180 } {1 {View abort}}

   181 

   182 } ;# ifcapable view

   183 

   184 integrity_check trigger3-8.1

   185 

   186 catchsql { DROP TABLE tbl; } 

   187 catchsql { DROP TABLE tbl2; } 

   188 catchsql { DROP VIEW tbl_view; }

   189 

   190 finish_test