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