gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/triggerG.test (about)

     1  # 2017-03-24
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice', here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #***********************************************************************
    11  #
    12  
    13  set testdir [file dirname $argv0]
    14  source $testdir/tester.tcl
    15  set testprefix triggerG
    16  ifcapable {!trigger} {
    17    finish_test
    18    return
    19  }
    20  
    21  # Test cases for ticket 
    22  # https://www.sqlite.org/src/tktview/06796225f59c057cd120f
    23  #
    24  # The OP_Once opcode was not working correctly for recursive triggers.
    25  #
    26  do_execsql_test 100 {
    27    PRAGMA recursive_triggers = 1;
    28    
    29    CREATE TABLE t1(a);
    30    CREATE INDEX i1 ON t1(a);
    31    INSERT INTO t1(a) VALUES(0),(2),(3),(8),(9);
    32    CREATE TABLE t2(b);
    33    CREATE TABLE t3(c);
    34    
    35    CREATE TRIGGER tr AFTER INSERT ON t3 BEGIN
    36      INSERT INTO t3 SELECT new.c+1 WHERE new.c<5;
    37      INSERT INTO t2 SELECT new.c*100+a FROM t1 WHERE a IN (1, 2, 3, 4);
    38    END;
    39    
    40    INSERT INTO t3 VALUES(2);
    41    SELECT c FROM t3 ORDER BY c;;
    42  } {2 3 4 5}
    43  do_execsql_test 110 {
    44    SELECT b FROM t2 ORDER BY b;
    45  } {202 203 302 303 402 403 502 503}
    46  
    47  do_execsql_test 200 {
    48    DELETE FROM t1;
    49    INSERT INTO t1(a) VALUES(0),(2),(3),(8),(9);
    50    DELETE FROM t2;
    51    DELETE FROM t3;
    52    DROP TRIGGER tr;
    53    CREATE TRIGGER tr AFTER INSERT ON t3 BEGIN
    54      INSERT INTO t3 SELECT new.c+1 WHERE new.c<5;
    55      INSERT INTO t2 SELECT new.c*10000+xx.a*100+yy.a
    56                       FROM t1 AS xx, t1 AS yy
    57                      WHERE xx.a IN (1,2,3,4)
    58                        AND yy.a IN (2,3,4,5);
    59    END;
    60  
    61    INSERT INTO t3 VALUES(2);
    62    SELECT b FROM t2 ORDER BY b;
    63  } {20202 20203 20302 20303 30202 30203 30302 30303 40202 40203 40302 40303 50202 50203 50302 50303}
    64  
    65  # At one point the following was causing an assert() to fail.
    66  #
    67  do_execsql_test 300 {
    68    CREATE TABLE t4(x);
    69    CREATE TRIGGER tr4 AFTER INSERT ON t4 BEGIN
    70      SELECT 0x2147483648e0e0099 AS y WHERE y;
    71    END;
    72  }
    73  
    74  do_catchsql_test 310 {
    75    INSERT INTO t4 VALUES(1);
    76  } {1 {hex literal too big: 0x2147483648e0e0099}}
    77  
    78  #-------------------------------------------------------------------------
    79  # 
    80  do_execsql_test 400 {
    81    CREATE VIEW v0(a) AS SELECT 1234;
    82    CREATE TRIGGER t0001 INSTEAD OF DELETE ON v0 BEGIN
    83      SELECT old.a;
    84    END;
    85  }
    86  do_execsql_test 405 {
    87    SELECT a FROM v0;
    88  } {1234}
    89  do_execsql_test 410 {
    90    DELETE FROM v0;
    91  }
    92  
    93  
    94  finish_test