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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15set testprefix triggerG 16ifcapable {!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# 26do_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} 43do_execsql_test 110 { 44 SELECT b FROM t2 ORDER BY b; 45} {202 203 302 303 402 403 502 503} 46 47do_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# 67do_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 74do_catchsql_test 310 { 75 INSERT INTO t4 VALUES(1); 76} {1 {hex literal too big: 0x2147483648e0e0099}} 77 78#------------------------------------------------------------------------- 79# 80do_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} 86do_execsql_test 405 { 87 SELECT a FROM v0; 88} {1234} 89do_execsql_test 410 { 90 DELETE FROM v0; 91} 92 93 94finish_test 95