diff options
Diffstat (limited to 'third_party/sqlite/src/test/triggerC.test')
| -rw-r--r-- | third_party/sqlite/src/test/triggerC.test | 765 |
1 files changed, 765 insertions, 0 deletions
diff --git a/third_party/sqlite/src/test/triggerC.test b/third_party/sqlite/src/test/triggerC.test new file mode 100644 index 0000000..807a50a --- /dev/null +++ b/third_party/sqlite/src/test/triggerC.test @@ -0,0 +1,765 @@ +# 2009 August 24 +# +# The author disclaims copyright to this source code. In place of +# a legal notice', here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +ifcapable {!trigger} { + finish_test + return +} + +#------------------------------------------------------------------------- +# Test organization: +# +# triggerC-1.*: Haphazardly designed trigger related tests that were useful +# during an upgrade of the triggers sub-system. +# +# triggerC-2.*: +# +# triggerC-3.*: +# +# triggerC-4.*: +# +# triggerC-5.*: Test that when recursive triggers are enabled DELETE +# triggers are fired when rows are deleted as part of OR +# REPLACE conflict resolution. And that they are not fired +# if recursive triggers are not enabled. +# +# triggerC-6.*: Test that the recursive_triggers pragma returns correct +# results when invoked without an argument. +# + +# Enable recursive triggers for this file. +# +execsql { PRAGMA recursive_triggers = on } + +#sqlite3_db_config_lookaside db 0 0 0 + +#------------------------------------------------------------------------- +# This block of tests, triggerC-1.*, are not aimed at any specific +# property of the triggers sub-system. They were created to debug +# specific problems while modifying SQLite to support recursive +# triggers. They are left here in case they can help debug the +# same problems again. +# +do_test triggerC-1.1 { + execsql { + CREATE TABLE t1(a, b, c); + CREATE TABLE log(t, a1, b1, c1, a2, b2, c2); + CREATE TRIGGER trig1 BEFORE INSERT ON t1 BEGIN + INSERT INTO log VALUES('before', NULL, NULL, NULL, new.a, new.b, new.c); + END; + CREATE TRIGGER trig2 AFTER INSERT ON t1 BEGIN + INSERT INTO log VALUES('after', NULL, NULL, NULL, new.a, new.b, new.c); + END; + CREATE TRIGGER trig3 BEFORE UPDATE ON t1 BEGIN + INSERT INTO log VALUES('before', old.a,old.b,old.c, new.a,new.b,new.c); + END; + CREATE TRIGGER trig4 AFTER UPDATE ON t1 BEGIN + INSERT INTO log VALUES('after', old.a,old.b,old.c, new.a,new.b,new.c); + END; + + CREATE TRIGGER trig5 BEFORE DELETE ON t1 BEGIN + INSERT INTO log VALUES('before', old.a,old.b,old.c, NULL,NULL,NULL); + END; + CREATE TRIGGER trig6 AFTER DELETE ON t1 BEGIN + INSERT INTO log VALUES('after', old.a,old.b,old.c, NULL,NULL,NULL); + END; + } +} {} +do_test triggerC-1.2 { + execsql { + INSERT INTO t1 VALUES('A', 'B', 'C'); + SELECT * FROM log; + } +} {before {} {} {} A B C after {} {} {} A B C} +do_test triggerC-1.3 { + execsql { SELECT * FROM t1 } +} {A B C} +do_test triggerC-1.4 { + execsql { + DELETE FROM log; + UPDATE t1 SET a = 'a'; + SELECT * FROM log; + } +} {before A B C a B C after A B C a B C} +do_test triggerC-1.5 { + execsql { SELECT * FROM t1 } +} {a B C} +do_test triggerC-1.6 { + execsql { + DELETE FROM log; + DELETE FROM t1; + SELECT * FROM log; + } +} {before a B C {} {} {} after a B C {} {} {}} +do_test triggerC-1.7 { + execsql { SELECT * FROM t1 } +} {} +do_test triggerC-1.8 { + execsql { + CREATE TABLE t4(a, b); + CREATE TRIGGER t4t AFTER DELETE ON t4 BEGIN + SELECT RAISE(ABORT, 'delete is not supported'); + END; + } +} {} +do_test triggerC-1.9 { + execsql { INSERT INTO t4 VALUES(1, 2) } + catchsql { DELETE FROM t4 } +} {1 {delete is not supported}} +do_test triggerC-1.10 { + execsql { SELECT * FROM t4 } +} {1 2} +do_test triggerC-1.11 { + execsql { + CREATE TABLE t5 (a primary key, b, c); + INSERT INTO t5 values (1, 2, 3); + CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN + UPDATE OR IGNORE t5 SET a = new.a, c = 10; + END; + } +} {} +do_test triggerC-1.12 { + catchsql { UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 } +} {1 {too many levels of trigger recursion}} +do_test triggerC-1.13 { + execsql { + CREATE TABLE t6(a INTEGER PRIMARY KEY, b); + INSERT INTO t6 VALUES(1, 2); + create trigger r1 after update on t6 for each row begin + SELECT 1; + end; + UPDATE t6 SET a=a; + } +} {} +do_test triggerC-1.14 { + execsql { + DROP TABLE t1; + CREATE TABLE cnt(n); + INSERT INTO cnt VALUES(0); + CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c, d, e); + CREATE INDEX t1cd ON t1(c,d); + CREATE TRIGGER t1r1 AFTER UPDATE ON t1 BEGIN UPDATE cnt SET n=n+1; END; + INSERT INTO t1 VALUES(1,2,3,4,5); + INSERT INTO t1 VALUES(6,7,8,9,10); + INSERT INTO t1 VALUES(11,12,13,14,15); + } +} {} +do_test triggerC-1.15 { + catchsql { UPDATE OR ROLLBACK t1 SET a=100 } +} {1 {PRIMARY KEY must be unique}} + + +#------------------------------------------------------------------------- +# This block of tests, triggerC-2.*, tests that recursive trigger +# programs (triggers that fire themselves) work. More specifically, +# this block focuses on recursive INSERT triggers. +# +do_test triggerC-2.1.0 { + execsql { + CREATE TABLE t2(a PRIMARY KEY); + } +} {} + +foreach {n tdefn rc} { + 1 { + CREATE TRIGGER t2_trig AFTER INSERT ON t2 WHEN (new.a>0) BEGIN + INSERT INTO t2 VALUES(new.a - 1); + END; + } {0 {10 9 8 7 6 5 4 3 2 1 0}} + + 2 { + CREATE TRIGGER t2_trig AFTER INSERT ON t2 BEGIN + SELECT CASE WHEN new.a==2 THEN RAISE(IGNORE) ELSE NULL END; + INSERT INTO t2 VALUES(new.a - 1); + END; + } {0 {10 9 8 7 6 5 4 3 2}} + + 3 { + CREATE TRIGGER t2_trig BEFORE INSERT ON t2 WHEN (new.a>0) BEGIN + INSERT INTO t2 VALUES(new.a - 1); + END; + } {0 {0 1 2 3 4 5 6 7 8 9 10}} + + 4 { + CREATE TRIGGER t2_trig BEFORE INSERT ON t2 BEGIN + SELECT CASE WHEN new.a==2 THEN RAISE(IGNORE) ELSE NULL END; + INSERT INTO t2 VALUES(new.a - 1); + END; + } {0 {3 4 5 6 7 8 9 10}} + + 5 { + CREATE TRIGGER t2_trig BEFORE INSERT ON t2 BEGIN + INSERT INTO t2 VALUES(new.a - 1); + END; + } {1 {too many levels of trigger recursion}} + + 6 { + CREATE TRIGGER t2_trig AFTER INSERT ON t2 WHEN (new.a>0) BEGIN + INSERT OR IGNORE INTO t2 VALUES(new.a); + END; + } {0 10} + + 7 { + CREATE TRIGGER t2_trig BEFORE INSERT ON t2 WHEN (new.a>0) BEGIN + INSERT OR IGNORE INTO t2 VALUES(new.a); + END; + } {1 {too many levels of trigger recursion}} +} { + do_test triggerC-2.1.$n { + catchsql { DROP TRIGGER t2_trig } + execsql { DELETE FROM t2 } + execsql $tdefn + catchsql { + INSERT INTO t2 VALUES(10); + SELECT * FROM t2; + } + } $rc +} + +do_test triggerC-2.2 { + execsql { + CREATE TABLE t22(x); + + CREATE TRIGGER t22a AFTER INSERT ON t22 BEGIN + INSERT INTO t22 SELECT x + (SELECT max(x) FROM t22) FROM t22; + END; + CREATE TRIGGER t22b BEFORE INSERT ON t22 BEGIN + SELECT CASE WHEN (SELECT count(*) FROM t22) >= 100 + THEN RAISE(IGNORE) + ELSE NULL END; + END; + + INSERT INTO t22 VALUES(1); + SELECT count(*) FROM t22; + } +} {100} + +do_test triggerC-2.3 { + execsql { + CREATE TABLE t23(x PRIMARY KEY); + + CREATE TRIGGER t23a AFTER INSERT ON t23 BEGIN + INSERT INTO t23 VALUES(new.x + 1); + END; + + CREATE TRIGGER t23b BEFORE INSERT ON t23 BEGIN + SELECT CASE WHEN new.x>500 + THEN RAISE(IGNORE) + ELSE NULL END; + END; + + INSERT INTO t23 VALUES(1); + SELECT count(*) FROM t23; + } +} {500} + + +#----------------------------------------------------------------------- +# This block of tests, triggerC-3.*, test that SQLite throws an exception +# when it detects excessive recursion. +# +do_test triggerC-3.1.1 { + execsql { + CREATE TABLE t3(a, b); + CREATE TRIGGER t3i AFTER INSERT ON t3 BEGIN + DELETE FROM t3 WHERE rowid = new.rowid; + END; + CREATE TRIGGER t3d AFTER DELETE ON t3 BEGIN + INSERT INTO t3 VALUES(old.a, old.b); + END; + } +} {} +do_test triggerC-3.1.2 { + catchsql { INSERT INTO t3 VALUES(0,0) } +} {1 {too many levels of trigger recursion}} +do_test triggerC-3.1.3 { + execsql { SELECT * FROM t3 } +} {} + +do_test triggerC-3.2.1 { + execsql { + CREATE TABLE t3b(x); + CREATE TRIGGER t3bi AFTER INSERT ON t3b WHEN new.x<2000 BEGIN + INSERT INTO t3b VALUES(new.x+1); + END; + } + catchsql { + INSERT INTO t3b VALUES(1); + } +} {1 {too many levels of trigger recursion}} +do_test triggerC-3.2.2 { + db eval {SELECT * FROM t3b} +} {} + +do_test triggerC-3.3.1 { + catchsql { + INSERT INTO t3b VALUES(1000); + } +} {0 {}} +do_test triggerC-3.3.2 { + db eval {SELECT count(*), max(x), min(x) FROM t3b} +} {1001 2000 1000} + +do_test triggerC-3.4.1 { + catchsql { + DELETE FROM t3b; + INSERT INTO t3b VALUES(999); + } +} {1 {too many levels of trigger recursion}} +do_test triggerC-3.4.2 { + db eval {SELECT count(*), max(x), min(x) FROM t3b} +} {0 {} {}} + +do_test triggerC-3.5.1 { + sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 100 + catchsql { + INSERT INTO t3b VALUES(1900); + } +} {0 {}} +do_test triggerC-3.5.2 { + db eval {SELECT count(*), max(x), min(x) FROM t3b} +} {101 2000 1900} + +do_test triggerC-3.5.3 { + catchsql { + DELETE FROM t3b; + INSERT INTO t3b VALUES(1899); + } +} {1 {too many levels of trigger recursion}} +do_test triggerC-3.5.4 { + db eval {SELECT count(*), max(x), min(x) FROM t3b} +} {0 {} {}} + +do_test triggerC-3.6.1 { + sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1 + catchsql { + INSERT INTO t3b VALUES(1999); + } +} {0 {}} +do_test triggerC-3.6.2 { + db eval {SELECT count(*), max(x), min(x) FROM t3b} +} {2 2000 1999} + +do_test triggerC-3.6.3 { + catchsql { + DELETE FROM t3b; + INSERT INTO t3b VALUES(1998); + } +} {1 {too many levels of trigger recursion}} +do_test triggerC-3.6.4 { + db eval {SELECT count(*), max(x), min(x) FROM t3b} +} {0 {} {}} +sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000 + + +#----------------------------------------------------------------------- +# This next block of tests, triggerC-4.*, checks that affinity +# transformations and constraint processing is performed at the correct +# times relative to BEFORE and AFTER triggers. +# +# For an INSERT statement, for each row to be inserted: +# +# 1. Apply affinities to non-rowid values to be inserted. +# 2. Fire BEFORE triggers. +# 3. Process constraints. +# 4. Insert new record. +# 5. Fire AFTER triggers. +# +# If the value of the rowid field is to be automatically assigned, it is +# set to -1 in the new.* record. Even if it is explicitly set to NULL +# by the INSERT statement. +# +# For an UPDATE statement, for each row to be deleted: +# +# 1. Apply affinities to non-rowid values to be inserted. +# 2. Fire BEFORE triggers. +# 3. Process constraints. +# 4. Insert new record. +# 5. Fire AFTER triggers. +# +# For a DELETE statement, for each row to be deleted: +# +# 1. Fire BEFORE triggers. +# 2. Remove database record. +# 3. Fire AFTER triggers. +# +# When a numeric value that as an exact integer representation is stored +# in a column with REAL affinity, it is actually stored as an integer. +# These tests check that the typeof() such values is always 'real', +# not 'integer'. +# +# triggerC-4.1.*: Check that affinity transformations are made before +# triggers are invoked. +# +do_test triggerC-4.1.1 { + catchsql { DROP TABLE log } + catchsql { DROP TABLE t4 } + execsql { + CREATE TABLE log(t); + CREATE TABLE t4(a TEXT,b INTEGER,c REAL); + CREATE TRIGGER t4bi BEFORE INSERT ON t4 BEGIN + INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || + new.a || ' ' || typeof(new.a) || ' ' || + new.b || ' ' || typeof(new.b) || ' ' || + new.c || ' ' || typeof(new.c) + ); + END; + CREATE TRIGGER t4ai AFTER INSERT ON t4 BEGIN + INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || + new.a || ' ' || typeof(new.a) || ' ' || + new.b || ' ' || typeof(new.b) || ' ' || + new.c || ' ' || typeof(new.c) + ); + END; + CREATE TRIGGER t4bd BEFORE DELETE ON t4 BEGIN + INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || + old.a || ' ' || typeof(old.a) || ' ' || + old.b || ' ' || typeof(old.b) || ' ' || + old.c || ' ' || typeof(old.c) + ); + END; + CREATE TRIGGER t4ad AFTER DELETE ON t4 BEGIN + INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || + old.a || ' ' || typeof(old.a) || ' ' || + old.b || ' ' || typeof(old.b) || ' ' || + old.c || ' ' || typeof(old.c) + ); + END; + CREATE TRIGGER t4bu BEFORE UPDATE ON t4 BEGIN + INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || + old.a || ' ' || typeof(old.a) || ' ' || + old.b || ' ' || typeof(old.b) || ' ' || + old.c || ' ' || typeof(old.c) + ); + INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || + new.a || ' ' || typeof(new.a) || ' ' || + new.b || ' ' || typeof(new.b) || ' ' || + new.c || ' ' || typeof(new.c) + ); + END; + CREATE TRIGGER t4au AFTER UPDATE ON t4 BEGIN + INSERT INTO log VALUES(old.rowid || ' ' || typeof(old.rowid) || ' ' || + old.a || ' ' || typeof(old.a) || ' ' || + old.b || ' ' || typeof(old.b) || ' ' || + old.c || ' ' || typeof(old.c) + ); + INSERT INTO log VALUES(new.rowid || ' ' || typeof(new.rowid) || ' ' || + new.a || ' ' || typeof(new.a) || ' ' || + new.b || ' ' || typeof(new.b) || ' ' || + new.c || ' ' || typeof(new.c) + ); + END; + } +} {} +foreach {n insert log} { + + 2 { + INSERT INTO t4 VALUES('1', '1', '1'); + DELETE FROM t4; + } { + -1 integer 1 text 1 integer 1.0 real + 1 integer 1 text 1 integer 1.0 real + 1 integer 1 text 1 integer 1.0 real + 1 integer 1 text 1 integer 1.0 real + } + + 3 { + INSERT INTO t4(rowid,a,b,c) VALUES(45, 45, 45, 45); + DELETE FROM t4; + } { + 45 integer 45 text 45 integer 45.0 real + 45 integer 45 text 45 integer 45.0 real + 45 integer 45 text 45 integer 45.0 real + 45 integer 45 text 45 integer 45.0 real + } + + 4 { + INSERT INTO t4(rowid,a,b,c) VALUES(-42.0, -42.0, -42.0, -42.0); + DELETE FROM t4; + } { + -42 integer -42.0 text -42 integer -42.0 real + -42 integer -42.0 text -42 integer -42.0 real + -42 integer -42.0 text -42 integer -42.0 real + -42 integer -42.0 text -42 integer -42.0 real + } + + 5 { + INSERT INTO t4(rowid,a,b,c) VALUES(NULL, -42.4, -42.4, -42.4); + DELETE FROM t4; + } { + -1 integer -42.4 text -42.4 real -42.4 real + 1 integer -42.4 text -42.4 real -42.4 real + 1 integer -42.4 text -42.4 real -42.4 real + 1 integer -42.4 text -42.4 real -42.4 real + } + + 6 { + INSERT INTO t4 VALUES(7, 7, 7); + UPDATE t4 SET a=8, b=8, c=8; + } { + -1 integer 7 text 7 integer 7.0 real + 1 integer 7 text 7 integer 7.0 real + 1 integer 7 text 7 integer 7.0 real + 1 integer 8 text 8 integer 8.0 real + 1 integer 7 text 7 integer 7.0 real + 1 integer 8 text 8 integer 8.0 real + } + + 7 { + UPDATE t4 SET rowid=2; + } { + 1 integer 8 text 8 integer 8.0 real + 2 integer 8 text 8 integer 8.0 real + 1 integer 8 text 8 integer 8.0 real + 2 integer 8 text 8 integer 8.0 real + } + + 8 { + UPDATE t4 SET a='9', b='9', c='9'; + } { + 2 integer 8 text 8 integer 8.0 real + 2 integer 9 text 9 integer 9.0 real + 2 integer 8 text 8 integer 8.0 real + 2 integer 9 text 9 integer 9.0 real + } + + 9 { + UPDATE t4 SET a='9.1', b='9.1', c='9.1'; + } { + 2 integer 9 text 9 integer 9.0 real + 2 integer 9.1 text 9.1 real 9.1 real + 2 integer 9 text 9 integer 9.0 real + 2 integer 9.1 text 9.1 real 9.1 real + } +} { + do_test triggerC-4.1.$n { + eval concat [execsql " + DELETE FROM log; + $insert ; + SELECT * FROM log; + "] + } [join $log " "] +} + +#------------------------------------------------------------------------- +# This block of tests, triggerC-5.*, test that DELETE triggers are fired +# if a row is deleted as a result of OR REPLACE conflict resolution. +# +do_test triggerC-5.1.0 { + execsql { + DROP TABLE IF EXISTS t5; + CREATE TABLE t5(a INTEGER PRIMARY KEY, b); + CREATE UNIQUE INDEX t5i ON t5(b); + INSERT INTO t5 VALUES(1, 'a'); + INSERT INTO t5 VALUES(2, 'b'); + INSERT INTO t5 VALUES(3, 'c'); + + CREATE TABLE t5g(a, b, c); + CREATE TRIGGER t5t BEFORE DELETE ON t5 BEGIN + INSERT INTO t5g VALUES(old.a, old.b, (SELECT count(*) FROM t5)); + END; + } +} {} +foreach {n dml t5g t5} { + 1 "DELETE FROM t5 WHERE a=2" {2 b 3} {1 a 3 c} + 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {2 b 3} {1 a 2 d 3 c} + 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {2 b 3} {1 a 2 c} + 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {2 b 3} {1 a 3 c 4 b} + 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {2 b 3} {1 a 3 b} + 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {2 b 3 3 c 2} {1 a 2 c} + 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 3 2 b 2} {1 b} +} { + do_test triggerC-5.1.$n { + execsql " + BEGIN; + $dml ; + SELECT * FROM t5g; + SELECT * FROM t5; + ROLLBACK; + " + } [concat $t5g $t5] +} +do_test triggerC-5.2.0 { + execsql { + DROP TRIGGER t5t; + CREATE TRIGGER t5t AFTER DELETE ON t5 BEGIN + INSERT INTO t5g VALUES(old.a, old.b, (SELECT count(*) FROM t5)); + END; + } +} {} +foreach {n dml t5g t5} { + 1 "DELETE FROM t5 WHERE a=2" {2 b 2} {1 a 3 c} + 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {2 b 2} {1 a 2 d 3 c} + 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {2 b 2} {1 a 2 c} + 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {2 b 2} {1 a 3 c 4 b} + 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {2 b 2} {1 a 3 b} + 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {2 b 2 3 c 1} {1 a 2 c} + 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 2 2 b 1} {1 b} +} { + do_test triggerC-5.2.$n { + execsql " + BEGIN; + $dml ; + SELECT * FROM t5g; + SELECT * FROM t5; + ROLLBACK; + " + } [concat $t5g $t5] +} +do_test triggerC-5.3.0 { + execsql { PRAGMA recursive_triggers = off } +} {} +foreach {n dml t5g t5} { + 1 "DELETE FROM t5 WHERE a=2" {2 b 2} {1 a 3 c} + 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {} {1 a 2 d 3 c} + 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {} {1 a 2 c} + 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {} {1 a 3 c 4 b} + 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {} {1 a 3 b} + 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {} {1 a 2 c} + 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {} {1 b} +} { + do_test triggerC-5.3.$n { + execsql " + BEGIN; + $dml ; + SELECT * FROM t5g; + SELECT * FROM t5; + ROLLBACK; + " + } [concat $t5g $t5] +} +do_test triggerC-5.3.8 { + execsql { PRAGMA recursive_triggers = on } +} {} + +#------------------------------------------------------------------------- +# This block of tests, triggerC-6.*, tests that "PRAGMA recursive_triggers" +# statements return the current value of the recursive triggers flag. +# +do_test triggerC-6.1 { + execsql { PRAGMA recursive_triggers } +} {1} +do_test triggerC-6.2 { + execsql { + PRAGMA recursive_triggers = off; + PRAGMA recursive_triggers; + } +} {0} +do_test triggerC-6.3 { + execsql { + PRAGMA recursive_triggers = on; + PRAGMA recursive_triggers; + } +} {1} + +#------------------------------------------------------------------------- +# Test some of the "undefined behaviour" associated with triggers. The +# undefined behaviour occurs when a row being updated or deleted is +# manipulated by a BEFORE trigger. +# +do_test triggerC-7.1 { + execsql { + CREATE TABLE t8(x); + CREATE TABLE t7(a, b); + INSERT INTO t7 VALUES(1, 2); + INSERT INTO t7 VALUES(3, 4); + INSERT INTO t7 VALUES(5, 6); + CREATE TRIGGER t7t BEFORE UPDATE ON t7 BEGIN + DELETE FROM t7 WHERE a = 1; + END; + CREATE TRIGGER t7ta AFTER UPDATE ON t7 BEGIN + INSERT INTO t8 VALUES('after fired ' || old.rowid || '->' || new.rowid); + END; + } +} {} +do_test triggerC-7.2 { + execsql { + BEGIN; + UPDATE t7 SET b=7 WHERE a = 5; + SELECT * FROM t7; + SELECT * FROM t8; + ROLLBACK; + } +} {3 4 5 7 {after fired 3->3}} +do_test triggerC-7.3 { + execsql { + BEGIN; + UPDATE t7 SET b=7 WHERE a = 1; + SELECT * FROM t7; + SELECT * FROM t8; + ROLLBACK; + } +} {3 4 5 6} + +do_test triggerC-7.4 { + execsql { + DROP TRIGGER t7t; + CREATE TRIGGER t7t BEFORE UPDATE ON t7 WHEN (old.rowid!=1 OR new.rowid!=8) + BEGIN + UPDATE t7 set rowid = 8 WHERE rowid=1; + END; + } +} {} +do_test triggerC-7.5 { + execsql { + BEGIN; + UPDATE t7 SET b=7 WHERE a = 5; + SELECT rowid, * FROM t7; + SELECT * FROM t8; + ROLLBACK; + } +} {2 3 4 3 5 7 8 1 2 {after fired 1->8} {after fired 3->3}} +do_test triggerC-7.6 { + execsql { + BEGIN; + UPDATE t7 SET b=7 WHERE a = 1; + SELECT rowid, * FROM t7; + SELECT * FROM t8; + ROLLBACK; + } +} {2 3 4 3 5 6 8 1 2 {after fired 1->8}} + +do_test triggerC-7.7 { + execsql { + DROP TRIGGER t7t; + DROP TRIGGER t7ta; + CREATE TRIGGER t7t BEFORE DELETE ON t7 BEGIN + UPDATE t7 set rowid = 8 WHERE rowid=1; + END; + CREATE TRIGGER t7ta AFTER DELETE ON t7 BEGIN + INSERT INTO t8 VALUES('after fired ' || old.rowid); + END; + } +} {} +do_test triggerC-7.8 { + execsql { + BEGIN; + DELETE FROM t7 WHERE a = 3; + SELECT rowid, * FROM t7; + SELECT * FROM t8; + ROLLBACK; + } +} {3 5 6 8 1 2 {after fired 2}} +do_test triggerC-7.9 { + execsql { + BEGIN; + DELETE FROM t7 WHERE a = 1; + SELECT rowid, * FROM t7; + SELECT * FROM t8; + ROLLBACK; + } +} {2 3 4 3 5 6 8 1 2} + + +finish_test |