Debian Codegraph @

test/triggerG.test

raw
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://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