1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 37c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 47c478bd9Sstevel@tonic-gate# 57c478bd9Sstevel@tonic-gate# May you do good and not evil. 67c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 77c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 87c478bd9Sstevel@tonic-gate# 97c478bd9Sstevel@tonic-gate#*********************************************************************** 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate# Regression testing of FOR EACH ROW table triggers 127c478bd9Sstevel@tonic-gate# 13*1da57d55SToomas Soome# 1. Trigger execution order tests. 147c478bd9Sstevel@tonic-gate# These tests ensure that BEFORE and AFTER triggers are fired at the correct 15*1da57d55SToomas Soome# times relative to each other and the triggering statement. 167c478bd9Sstevel@tonic-gate# 177c478bd9Sstevel@tonic-gate# trigger2-1.1.*: ON UPDATE trigger execution model. 187c478bd9Sstevel@tonic-gate# trigger2-1.2.*: DELETE trigger execution model. 197c478bd9Sstevel@tonic-gate# trigger2-1.3.*: INSERT trigger execution model. 207c478bd9Sstevel@tonic-gate# 217c478bd9Sstevel@tonic-gate# 2. Trigger program execution tests. 227c478bd9Sstevel@tonic-gate# These tests ensure that trigger programs execute correctly (ie. that a 237c478bd9Sstevel@tonic-gate# trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT 247c478bd9Sstevel@tonic-gate# statements, and combinations thereof). 257c478bd9Sstevel@tonic-gate# 26*1da57d55SToomas Soome# 3. Selective trigger execution 277c478bd9Sstevel@tonic-gate# This tests that conditional triggers (ie. UPDATE OF triggers and triggers 287c478bd9Sstevel@tonic-gate# with WHEN clauses) are fired only fired when they are supposed to be. 297c478bd9Sstevel@tonic-gate# 307c478bd9Sstevel@tonic-gate# trigger2-3.1: UPDATE OF triggers 317c478bd9Sstevel@tonic-gate# trigger2-3.2: WHEN clause 327c478bd9Sstevel@tonic-gate# 33*1da57d55SToomas Soome# 4. Cascaded trigger execution 34*1da57d55SToomas Soome# Tests that trigger-programs may cause other triggers to fire. Also that a 357c478bd9Sstevel@tonic-gate# trigger-program is never executed recursively. 36*1da57d55SToomas Soome# 377c478bd9Sstevel@tonic-gate# trigger2-4.1: Trivial cascading trigger 38*1da57d55SToomas Soome# trigger2-4.2: Trivial recursive trigger handling 397c478bd9Sstevel@tonic-gate# 407c478bd9Sstevel@tonic-gate# 5. Count changes behaviour. 417c478bd9Sstevel@tonic-gate# Verify that rows altered by triggers are not included in the return value 427c478bd9Sstevel@tonic-gate# of the "count changes" interface. 437c478bd9Sstevel@tonic-gate# 447c478bd9Sstevel@tonic-gate# 6. ON CONFLICT clause handling 457c478bd9Sstevel@tonic-gate# trigger2-6.1[a-f]: INSERT statements 467c478bd9Sstevel@tonic-gate# trigger2-6.2[a-f]: UPDATE statements 477c478bd9Sstevel@tonic-gate# 487c478bd9Sstevel@tonic-gate# 7. Triggers on views fire correctly. 497c478bd9Sstevel@tonic-gate# 507c478bd9Sstevel@tonic-gate 517c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 527c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 537c478bd9Sstevel@tonic-gate 547c478bd9Sstevel@tonic-gate# 1. 557c478bd9Sstevel@tonic-gateset ii 0 567c478bd9Sstevel@tonic-gateforeach tbl_defn { 57*1da57d55SToomas Soome {CREATE TEMP TABLE tbl (a, b);} 58*1da57d55SToomas Soome {CREATE TABLE tbl (a, b);} 59*1da57d55SToomas Soome {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} 60*1da57d55SToomas Soome {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);} 61*1da57d55SToomas Soome {CREATE TABLE tbl (a, b PRIMARY KEY);} 62*1da57d55SToomas Soome {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} 63*1da57d55SToomas Soome {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} 647c478bd9Sstevel@tonic-gate} { 657c478bd9Sstevel@tonic-gate incr ii 667c478bd9Sstevel@tonic-gate catchsql { DROP INDEX tbl_idx; } 677c478bd9Sstevel@tonic-gate catchsql { 687c478bd9Sstevel@tonic-gate DROP TABLE rlog; 697c478bd9Sstevel@tonic-gate DROP TABLE clog; 707c478bd9Sstevel@tonic-gate DROP TABLE tbl; 717c478bd9Sstevel@tonic-gate DROP TABLE other_tbl; 727c478bd9Sstevel@tonic-gate } 737c478bd9Sstevel@tonic-gate 747c478bd9Sstevel@tonic-gate execsql $tbl_defn 757c478bd9Sstevel@tonic-gate 767c478bd9Sstevel@tonic-gate execsql { 777c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES(1, 2); 787c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES(3, 4); 797c478bd9Sstevel@tonic-gate 807c478bd9Sstevel@tonic-gate CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 817c478bd9Sstevel@tonic-gate CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b); 827c478bd9Sstevel@tonic-gate 83*1da57d55SToomas Soome CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW 847c478bd9Sstevel@tonic-gate BEGIN 85*1da57d55SToomas Soome INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 86*1da57d55SToomas Soome old.a, old.b, 87*1da57d55SToomas Soome (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 887c478bd9Sstevel@tonic-gate new.a, new.b); 897c478bd9Sstevel@tonic-gate END; 907c478bd9Sstevel@tonic-gate 91*1da57d55SToomas Soome CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW 927c478bd9Sstevel@tonic-gate BEGIN 93*1da57d55SToomas Soome INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 94*1da57d55SToomas Soome old.a, old.b, 95*1da57d55SToomas Soome (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 967c478bd9Sstevel@tonic-gate new.a, new.b); 977c478bd9Sstevel@tonic-gate END; 987c478bd9Sstevel@tonic-gate 997c478bd9Sstevel@tonic-gate CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW 1007c478bd9Sstevel@tonic-gate WHEN old.a = 1 1017c478bd9Sstevel@tonic-gate BEGIN 102*1da57d55SToomas Soome INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 103*1da57d55SToomas Soome old.a, old.b, 104*1da57d55SToomas Soome (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 1057c478bd9Sstevel@tonic-gate new.a, new.b); 1067c478bd9Sstevel@tonic-gate END; 1077c478bd9Sstevel@tonic-gate } 1087c478bd9Sstevel@tonic-gate 1097c478bd9Sstevel@tonic-gate do_test trigger2-1.$ii.1 { 110*1da57d55SToomas Soome execsql { 1117c478bd9Sstevel@tonic-gate UPDATE tbl SET a = a * 10, b = b * 10; 1127c478bd9Sstevel@tonic-gate SELECT * FROM rlog ORDER BY idx; 1137c478bd9Sstevel@tonic-gate SELECT * FROM clog ORDER BY idx; 1147c478bd9Sstevel@tonic-gate } 1157c478bd9Sstevel@tonic-gate } [list 1 1 2 4 6 10 20 \ 1167c478bd9Sstevel@tonic-gate 2 1 2 13 24 10 20 \ 1177c478bd9Sstevel@tonic-gate 3 3 4 13 24 30 40 \ 1187c478bd9Sstevel@tonic-gate 4 3 4 40 60 30 40 \ 1197c478bd9Sstevel@tonic-gate 1 1 2 13 24 10 20 ] 1207c478bd9Sstevel@tonic-gate 1217c478bd9Sstevel@tonic-gate execsql { 1227c478bd9Sstevel@tonic-gate DELETE FROM rlog; 1237c478bd9Sstevel@tonic-gate DELETE FROM tbl; 1247c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (100, 100); 1257c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (300, 200); 1267c478bd9Sstevel@tonic-gate CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW 1277c478bd9Sstevel@tonic-gate BEGIN 128*1da57d55SToomas Soome INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 129*1da57d55SToomas Soome old.a, old.b, 130*1da57d55SToomas Soome (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 1317c478bd9Sstevel@tonic-gate 0, 0); 1327c478bd9Sstevel@tonic-gate END; 1337c478bd9Sstevel@tonic-gate 1347c478bd9Sstevel@tonic-gate CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW 1357c478bd9Sstevel@tonic-gate BEGIN 136*1da57d55SToomas Soome INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 137*1da57d55SToomas Soome old.a, old.b, 138*1da57d55SToomas Soome (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 1397c478bd9Sstevel@tonic-gate 0, 0); 1407c478bd9Sstevel@tonic-gate END; 1417c478bd9Sstevel@tonic-gate } 1427c478bd9Sstevel@tonic-gate do_test trigger2-1.$ii.2 { 1437c478bd9Sstevel@tonic-gate execsql { 1447c478bd9Sstevel@tonic-gate DELETE FROM tbl; 1457c478bd9Sstevel@tonic-gate SELECT * FROM rlog; 1467c478bd9Sstevel@tonic-gate } 1477c478bd9Sstevel@tonic-gate } [list 1 100 100 400 300 0 0 \ 1487c478bd9Sstevel@tonic-gate 2 100 100 300 200 0 0 \ 1497c478bd9Sstevel@tonic-gate 3 300 200 300 200 0 0 \ 1507c478bd9Sstevel@tonic-gate 4 300 200 0 0 0 0 ] 1517c478bd9Sstevel@tonic-gate 1527c478bd9Sstevel@tonic-gate execsql { 1537c478bd9Sstevel@tonic-gate DELETE FROM rlog; 1547c478bd9Sstevel@tonic-gate CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW 1557c478bd9Sstevel@tonic-gate BEGIN 156*1da57d55SToomas Soome INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 1577c478bd9Sstevel@tonic-gate 0, 0, 158*1da57d55SToomas Soome (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 1597c478bd9Sstevel@tonic-gate new.a, new.b); 1607c478bd9Sstevel@tonic-gate END; 1617c478bd9Sstevel@tonic-gate 1627c478bd9Sstevel@tonic-gate CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW 1637c478bd9Sstevel@tonic-gate BEGIN 164*1da57d55SToomas Soome INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 1657c478bd9Sstevel@tonic-gate 0, 0, 166*1da57d55SToomas Soome (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 1677c478bd9Sstevel@tonic-gate new.a, new.b); 1687c478bd9Sstevel@tonic-gate END; 1697c478bd9Sstevel@tonic-gate } 1707c478bd9Sstevel@tonic-gate do_test trigger2-1.$ii.3 { 1717c478bd9Sstevel@tonic-gate execsql { 1727c478bd9Sstevel@tonic-gate 1737c478bd9Sstevel@tonic-gate CREATE TABLE other_tbl(a, b); 1747c478bd9Sstevel@tonic-gate INSERT INTO other_tbl VALUES(1, 2); 1757c478bd9Sstevel@tonic-gate INSERT INTO other_tbl VALUES(3, 4); 1767c478bd9Sstevel@tonic-gate -- INSERT INTO tbl SELECT * FROM other_tbl; 1777c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES(5, 6); 1787c478bd9Sstevel@tonic-gate DROP TABLE other_tbl; 1797c478bd9Sstevel@tonic-gate 1807c478bd9Sstevel@tonic-gate SELECT * FROM rlog; 1817c478bd9Sstevel@tonic-gate } 1827c478bd9Sstevel@tonic-gate } [list 1 0 0 0 0 5 6 \ 1837c478bd9Sstevel@tonic-gate 2 0 0 5 6 5 6 ] 1847c478bd9Sstevel@tonic-gate 1857c478bd9Sstevel@tonic-gate do_test trigger2-1.$ii.4 { 1867c478bd9Sstevel@tonic-gate execsql { 1877c478bd9Sstevel@tonic-gate PRAGMA integrity_check; 1887c478bd9Sstevel@tonic-gate } 1897c478bd9Sstevel@tonic-gate } {ok} 1907c478bd9Sstevel@tonic-gate} 1917c478bd9Sstevel@tonic-gatecatchsql { 1927c478bd9Sstevel@tonic-gate DROP TABLE rlog; 1937c478bd9Sstevel@tonic-gate DROP TABLE clog; 1947c478bd9Sstevel@tonic-gate DROP TABLE tbl; 1957c478bd9Sstevel@tonic-gate DROP TABLE other_tbl; 1967c478bd9Sstevel@tonic-gate} 1977c478bd9Sstevel@tonic-gate 1987c478bd9Sstevel@tonic-gate# 2. 1997c478bd9Sstevel@tonic-gateset ii 0 2007c478bd9Sstevel@tonic-gateforeach tr_program { 2017c478bd9Sstevel@tonic-gate {UPDATE tbl SET b = old.b;} 2027c478bd9Sstevel@tonic-gate {INSERT INTO log VALUES(new.c, 2, 3);} 2037c478bd9Sstevel@tonic-gate {DELETE FROM log WHERE a = 1;} 204*1da57d55SToomas Soome {INSERT INTO tbl VALUES(500, new.b * 10, 700); 205*1da57d55SToomas Soome UPDATE tbl SET c = old.c; 2067c478bd9Sstevel@tonic-gate DELETE FROM log;} 207*1da57d55SToomas Soome {INSERT INTO log select * from tbl;} 2087c478bd9Sstevel@tonic-gate} { 2097c478bd9Sstevel@tonic-gate foreach test_varset [ list \ 2107c478bd9Sstevel@tonic-gate { 211*1da57d55SToomas Soome set statement {UPDATE tbl SET c = 10 WHERE a = 1;} 2127c478bd9Sstevel@tonic-gate set prep {INSERT INTO tbl VALUES(1, 2, 3);} 2137c478bd9Sstevel@tonic-gate set newC 10 2147c478bd9Sstevel@tonic-gate set newB 2 2157c478bd9Sstevel@tonic-gate set newA 1 2167c478bd9Sstevel@tonic-gate set oldA 1 2177c478bd9Sstevel@tonic-gate set oldB 2 2187c478bd9Sstevel@tonic-gate set oldC 3 2197c478bd9Sstevel@tonic-gate } \ 2207c478bd9Sstevel@tonic-gate { 2217c478bd9Sstevel@tonic-gate set statement {DELETE FROM tbl WHERE a = 1;} 2227c478bd9Sstevel@tonic-gate set prep {INSERT INTO tbl VALUES(1, 2, 3);} 2237c478bd9Sstevel@tonic-gate set oldA 1 2247c478bd9Sstevel@tonic-gate set oldB 2 2257c478bd9Sstevel@tonic-gate set oldC 3 2267c478bd9Sstevel@tonic-gate } \ 2277c478bd9Sstevel@tonic-gate { 2287c478bd9Sstevel@tonic-gate set statement {INSERT INTO tbl VALUES(1, 2, 3);} 2297c478bd9Sstevel@tonic-gate set newA 1 2307c478bd9Sstevel@tonic-gate set newB 2 2317c478bd9Sstevel@tonic-gate set newC 3 2327c478bd9Sstevel@tonic-gate } 2337c478bd9Sstevel@tonic-gate ] \ 2347c478bd9Sstevel@tonic-gate { 2357c478bd9Sstevel@tonic-gate set statement {} 2367c478bd9Sstevel@tonic-gate set prep {} 2377c478bd9Sstevel@tonic-gate set newA {''} 2387c478bd9Sstevel@tonic-gate set newB {''} 2397c478bd9Sstevel@tonic-gate set newC {''} 2407c478bd9Sstevel@tonic-gate set oldA {''} 2417c478bd9Sstevel@tonic-gate set oldB {''} 2427c478bd9Sstevel@tonic-gate set oldC {''} 2437c478bd9Sstevel@tonic-gate 2447c478bd9Sstevel@tonic-gate incr ii 2457c478bd9Sstevel@tonic-gate 2467c478bd9Sstevel@tonic-gate eval $test_varset 2477c478bd9Sstevel@tonic-gate 2487c478bd9Sstevel@tonic-gate set statement_type [string range $statement 0 5] 2497c478bd9Sstevel@tonic-gate set tr_program_fixed $tr_program 2507c478bd9Sstevel@tonic-gate if {$statement_type == "DELETE"} { 251*1da57d55SToomas Soome regsub -all new\.a $tr_program_fixed {''} tr_program_fixed 252*1da57d55SToomas Soome regsub -all new\.b $tr_program_fixed {''} tr_program_fixed 253*1da57d55SToomas Soome regsub -all new\.c $tr_program_fixed {''} tr_program_fixed 2547c478bd9Sstevel@tonic-gate } 2557c478bd9Sstevel@tonic-gate if {$statement_type == "INSERT"} { 256*1da57d55SToomas Soome regsub -all old\.a $tr_program_fixed {''} tr_program_fixed 257*1da57d55SToomas Soome regsub -all old\.b $tr_program_fixed {''} tr_program_fixed 258*1da57d55SToomas Soome regsub -all old\.c $tr_program_fixed {''} tr_program_fixed 2597c478bd9Sstevel@tonic-gate } 2607c478bd9Sstevel@tonic-gate 2617c478bd9Sstevel@tonic-gate 2627c478bd9Sstevel@tonic-gate set tr_program_cooked $tr_program 263*1da57d55SToomas Soome regsub -all new\.a $tr_program_cooked $newA tr_program_cooked 264*1da57d55SToomas Soome regsub -all new\.b $tr_program_cooked $newB tr_program_cooked 265*1da57d55SToomas Soome regsub -all new\.c $tr_program_cooked $newC tr_program_cooked 266*1da57d55SToomas Soome regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked 267*1da57d55SToomas Soome regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked 268*1da57d55SToomas Soome regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked 2697c478bd9Sstevel@tonic-gate 2707c478bd9Sstevel@tonic-gate catchsql { 2717c478bd9Sstevel@tonic-gate DROP TABLE tbl; 2727c478bd9Sstevel@tonic-gate DROP TABLE log; 2737c478bd9Sstevel@tonic-gate } 2747c478bd9Sstevel@tonic-gate 2757c478bd9Sstevel@tonic-gate execsql { 2767c478bd9Sstevel@tonic-gate CREATE TABLE tbl(a PRIMARY KEY, b, c); 2777c478bd9Sstevel@tonic-gate CREATE TABLE log(a, b, c); 2787c478bd9Sstevel@tonic-gate } 2797c478bd9Sstevel@tonic-gate 2807c478bd9Sstevel@tonic-gate set query {SELECT * FROM tbl; SELECT * FROM log;} 2817c478bd9Sstevel@tonic-gate set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\ 2827c478bd9Sstevel@tonic-gate INSERT INTO log VALUES(10, 20, 30);" 2837c478bd9Sstevel@tonic-gate 2847c478bd9Sstevel@tonic-gate# Check execution of BEFORE programs: 2857c478bd9Sstevel@tonic-gate 2867c478bd9Sstevel@tonic-gate set before_data [ execsql "$prep $tr_program_cooked $statement $query" ] 2877c478bd9Sstevel@tonic-gate 2887c478bd9Sstevel@tonic-gate execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 2897c478bd9Sstevel@tonic-gate execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\ 2907c478bd9Sstevel@tonic-gate ON tbl BEGIN $tr_program_fixed END;" 2917c478bd9Sstevel@tonic-gate 2927c478bd9Sstevel@tonic-gate do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data 2937c478bd9Sstevel@tonic-gate 2947c478bd9Sstevel@tonic-gate execsql "DROP TRIGGER the_trigger;" 2957c478bd9Sstevel@tonic-gate execsql "DELETE FROM tbl; DELETE FROM log;" 2967c478bd9Sstevel@tonic-gate 2977c478bd9Sstevel@tonic-gate# Check execution of AFTER programs 2987c478bd9Sstevel@tonic-gate set after_data [ execsql "$prep $statement $tr_program_cooked $query" ] 2997c478bd9Sstevel@tonic-gate 3007c478bd9Sstevel@tonic-gate execsql "DELETE FROM tbl; DELETE FROM log; $prep"; 3017c478bd9Sstevel@tonic-gate execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\ 3027c478bd9Sstevel@tonic-gate ON tbl BEGIN $tr_program_fixed END;" 3037c478bd9Sstevel@tonic-gate 3047c478bd9Sstevel@tonic-gate do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data 3057c478bd9Sstevel@tonic-gate execsql "DROP TRIGGER the_trigger;" 3067c478bd9Sstevel@tonic-gate 3077c478bd9Sstevel@tonic-gate do_test trigger2-2.$ii-integrity { 3087c478bd9Sstevel@tonic-gate execsql { 3097c478bd9Sstevel@tonic-gate PRAGMA integrity_check; 3107c478bd9Sstevel@tonic-gate } 3117c478bd9Sstevel@tonic-gate } {ok} 3127c478bd9Sstevel@tonic-gate 3137c478bd9Sstevel@tonic-gate } 3147c478bd9Sstevel@tonic-gate} 3157c478bd9Sstevel@tonic-gatecatchsql { 3167c478bd9Sstevel@tonic-gate DROP TABLE tbl; 3177c478bd9Sstevel@tonic-gate DROP TABLE log; 3187c478bd9Sstevel@tonic-gate} 3197c478bd9Sstevel@tonic-gate 3207c478bd9Sstevel@tonic-gate# 3. 3217c478bd9Sstevel@tonic-gate 3227c478bd9Sstevel@tonic-gate# trigger2-3.1: UPDATE OF triggers 3237c478bd9Sstevel@tonic-gateexecsql { 3247c478bd9Sstevel@tonic-gate CREATE TABLE tbl (a, b, c, d); 3257c478bd9Sstevel@tonic-gate CREATE TABLE log (a); 3267c478bd9Sstevel@tonic-gate INSERT INTO log VALUES (0); 3277c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (0, 0, 0, 0); 3287c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (1, 0, 0, 0); 3297c478bd9Sstevel@tonic-gate CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl 3307c478bd9Sstevel@tonic-gate BEGIN 3317c478bd9Sstevel@tonic-gate UPDATE log SET a = a + 1; 3327c478bd9Sstevel@tonic-gate END; 3337c478bd9Sstevel@tonic-gate} 3347c478bd9Sstevel@tonic-gatedo_test trigger2-3.1 { 3357c478bd9Sstevel@tonic-gate execsql { 3367c478bd9Sstevel@tonic-gate UPDATE tbl SET b = 1, c = 10; -- 2 3377c478bd9Sstevel@tonic-gate UPDATE tbl SET b = 10; -- 0 3387c478bd9Sstevel@tonic-gate UPDATE tbl SET d = 4 WHERE a = 0; --1 3397c478bd9Sstevel@tonic-gate UPDATE tbl SET a = 4, b = 10; --0 3407c478bd9Sstevel@tonic-gate SELECT * FROM log; 3417c478bd9Sstevel@tonic-gate } 3427c478bd9Sstevel@tonic-gate} {3} 3437c478bd9Sstevel@tonic-gateexecsql { 3447c478bd9Sstevel@tonic-gate DROP TABLE tbl; 3457c478bd9Sstevel@tonic-gate DROP TABLE log; 3467c478bd9Sstevel@tonic-gate} 3477c478bd9Sstevel@tonic-gate 3487c478bd9Sstevel@tonic-gate# trigger2-3.2: WHEN clause 3497c478bd9Sstevel@tonic-gateset when_triggers [ list \ 3507c478bd9Sstevel@tonic-gate {t1 BEFORE INSERT ON tbl WHEN new.a > 20} \ 3517c478bd9Sstevel@tonic-gate {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} ] 3527c478bd9Sstevel@tonic-gate 3537c478bd9Sstevel@tonic-gateexecsql { 3547c478bd9Sstevel@tonic-gate CREATE TABLE tbl (a, b, c, d); 3557c478bd9Sstevel@tonic-gate CREATE TABLE log (a); 3567c478bd9Sstevel@tonic-gate INSERT INTO log VALUES (0); 3577c478bd9Sstevel@tonic-gate} 3587c478bd9Sstevel@tonic-gate 3597c478bd9Sstevel@tonic-gateforeach trig $when_triggers { 3607c478bd9Sstevel@tonic-gate execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;" 3617c478bd9Sstevel@tonic-gate} 3627c478bd9Sstevel@tonic-gate 3637c478bd9Sstevel@tonic-gatedo_test trigger2-3.2 { 364*1da57d55SToomas Soome execsql { 3657c478bd9Sstevel@tonic-gate 3667c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1 3677c478bd9Sstevel@tonic-gate SELECT * FROM log; 3687c478bd9Sstevel@tonic-gate UPDATE log SET a = 0; 3697c478bd9Sstevel@tonic-gate 3707c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES(0, 0, 0, 0); -- 0 3717c478bd9Sstevel@tonic-gate SELECT * FROM log; 3727c478bd9Sstevel@tonic-gate UPDATE log SET a = 0; 3737c478bd9Sstevel@tonic-gate 3747c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES(200, 0, 0, 0); -- 1 3757c478bd9Sstevel@tonic-gate SELECT * FROM log; 3767c478bd9Sstevel@tonic-gate UPDATE log SET a = 0; 3777c478bd9Sstevel@tonic-gate } 3787c478bd9Sstevel@tonic-gate} {1 0 1} 3797c478bd9Sstevel@tonic-gateexecsql { 3807c478bd9Sstevel@tonic-gate DROP TABLE tbl; 3817c478bd9Sstevel@tonic-gate DROP TABLE log; 3827c478bd9Sstevel@tonic-gate} 3837c478bd9Sstevel@tonic-gatedo_test trigger2-3.3 { 3847c478bd9Sstevel@tonic-gate execsql { 3857c478bd9Sstevel@tonic-gate PRAGMA integrity_check; 3867c478bd9Sstevel@tonic-gate } 3877c478bd9Sstevel@tonic-gate} {ok} 3887c478bd9Sstevel@tonic-gate 3897c478bd9Sstevel@tonic-gate# Simple cascaded trigger 3907c478bd9Sstevel@tonic-gateexecsql { 3917c478bd9Sstevel@tonic-gate CREATE TABLE tblA(a, b); 3927c478bd9Sstevel@tonic-gate CREATE TABLE tblB(a, b); 3937c478bd9Sstevel@tonic-gate CREATE TABLE tblC(a, b); 3947c478bd9Sstevel@tonic-gate 3957c478bd9Sstevel@tonic-gate CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN 3967c478bd9Sstevel@tonic-gate INSERT INTO tblB values(new.a, new.b); 3977c478bd9Sstevel@tonic-gate END; 3987c478bd9Sstevel@tonic-gate 3997c478bd9Sstevel@tonic-gate CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN 4007c478bd9Sstevel@tonic-gate INSERT INTO tblC values(new.a, new.b); 4017c478bd9Sstevel@tonic-gate END; 4027c478bd9Sstevel@tonic-gate} 4037c478bd9Sstevel@tonic-gatedo_test trigger2-4.1 { 4047c478bd9Sstevel@tonic-gate execsql { 4057c478bd9Sstevel@tonic-gate INSERT INTO tblA values(1, 2); 4067c478bd9Sstevel@tonic-gate SELECT * FROM tblA; 4077c478bd9Sstevel@tonic-gate SELECT * FROM tblB; 4087c478bd9Sstevel@tonic-gate SELECT * FROM tblC; 4097c478bd9Sstevel@tonic-gate } 4107c478bd9Sstevel@tonic-gate} {1 2 1 2 1 2} 4117c478bd9Sstevel@tonic-gateexecsql { 4127c478bd9Sstevel@tonic-gate DROP TABLE tblA; 4137c478bd9Sstevel@tonic-gate DROP TABLE tblB; 4147c478bd9Sstevel@tonic-gate DROP TABLE tblC; 4157c478bd9Sstevel@tonic-gate} 4167c478bd9Sstevel@tonic-gate 4177c478bd9Sstevel@tonic-gate# Simple recursive trigger 4187c478bd9Sstevel@tonic-gateexecsql { 4197c478bd9Sstevel@tonic-gate CREATE TABLE tbl(a, b, c); 420*1da57d55SToomas Soome CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 4217c478bd9Sstevel@tonic-gate BEGIN 4227c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (new.a, new.b, new.c); 4237c478bd9Sstevel@tonic-gate END; 4247c478bd9Sstevel@tonic-gate} 4257c478bd9Sstevel@tonic-gatedo_test trigger2-4.2 { 4267c478bd9Sstevel@tonic-gate execsql { 4277c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (1, 2, 3); 4287c478bd9Sstevel@tonic-gate select * from tbl; 4297c478bd9Sstevel@tonic-gate } 4307c478bd9Sstevel@tonic-gate} {1 2 3 1 2 3} 4317c478bd9Sstevel@tonic-gateexecsql { 4327c478bd9Sstevel@tonic-gate DROP TABLE tbl; 4337c478bd9Sstevel@tonic-gate} 4347c478bd9Sstevel@tonic-gate 4357c478bd9Sstevel@tonic-gate# 5. 4367c478bd9Sstevel@tonic-gateexecsql { 4377c478bd9Sstevel@tonic-gate CREATE TABLE tbl(a, b, c); 438*1da57d55SToomas Soome CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 4397c478bd9Sstevel@tonic-gate BEGIN 4407c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (1, 2, 3); 4417c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (2, 2, 3); 4427c478bd9Sstevel@tonic-gate UPDATE tbl set b = 10 WHERE a = 1; 4437c478bd9Sstevel@tonic-gate DELETE FROM tbl WHERE a = 1; 4447c478bd9Sstevel@tonic-gate DELETE FROM tbl; 4457c478bd9Sstevel@tonic-gate END; 4467c478bd9Sstevel@tonic-gate} 4477c478bd9Sstevel@tonic-gatedo_test trigger2-5 { 4487c478bd9Sstevel@tonic-gate execsql { 4497c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES(100, 200, 300); 4507c478bd9Sstevel@tonic-gate } 4517c478bd9Sstevel@tonic-gate db changes 4527c478bd9Sstevel@tonic-gate} {1} 4537c478bd9Sstevel@tonic-gateexecsql { 4547c478bd9Sstevel@tonic-gate DROP TABLE tbl; 4557c478bd9Sstevel@tonic-gate} 4567c478bd9Sstevel@tonic-gate 4577c478bd9Sstevel@tonic-gate# Handling of ON CONFLICT by INSERT statements inside triggers 4587c478bd9Sstevel@tonic-gateexecsql { 4597c478bd9Sstevel@tonic-gate CREATE TABLE tbl (a primary key, b, c); 4607c478bd9Sstevel@tonic-gate CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN 4617c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO tbl values (new.a, 0, 0); 4627c478bd9Sstevel@tonic-gate END; 4637c478bd9Sstevel@tonic-gate} 4647c478bd9Sstevel@tonic-gatedo_test trigger2-6.1a { 4657c478bd9Sstevel@tonic-gate execsql { 4667c478bd9Sstevel@tonic-gate BEGIN; 4677c478bd9Sstevel@tonic-gate INSERT INTO tbl values (1, 2, 3); 4687c478bd9Sstevel@tonic-gate SELECT * from tbl; 4697c478bd9Sstevel@tonic-gate } 4707c478bd9Sstevel@tonic-gate} {1 2 3} 4717c478bd9Sstevel@tonic-gatedo_test trigger2-6.1b { 4727c478bd9Sstevel@tonic-gate catchsql { 4737c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO tbl values (2, 2, 3); 4747c478bd9Sstevel@tonic-gate } 4757c478bd9Sstevel@tonic-gate} {1 {column a is not unique}} 4767c478bd9Sstevel@tonic-gatedo_test trigger2-6.1c { 4777c478bd9Sstevel@tonic-gate execsql { 4787c478bd9Sstevel@tonic-gate SELECT * from tbl; 4797c478bd9Sstevel@tonic-gate } 4807c478bd9Sstevel@tonic-gate} {1 2 3} 4817c478bd9Sstevel@tonic-gatedo_test trigger2-6.1d { 4827c478bd9Sstevel@tonic-gate catchsql { 4837c478bd9Sstevel@tonic-gate INSERT OR FAIL INTO tbl values (2, 2, 3); 4847c478bd9Sstevel@tonic-gate } 4857c478bd9Sstevel@tonic-gate} {1 {column a is not unique}} 4867c478bd9Sstevel@tonic-gatedo_test trigger2-6.1e { 4877c478bd9Sstevel@tonic-gate execsql { 4887c478bd9Sstevel@tonic-gate SELECT * from tbl; 4897c478bd9Sstevel@tonic-gate } 4907c478bd9Sstevel@tonic-gate} {1 2 3 2 2 3} 4917c478bd9Sstevel@tonic-gatedo_test trigger2-6.1f { 4927c478bd9Sstevel@tonic-gate execsql { 4937c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO tbl values (2, 2, 3); 4947c478bd9Sstevel@tonic-gate SELECT * from tbl; 4957c478bd9Sstevel@tonic-gate } 4967c478bd9Sstevel@tonic-gate} {1 2 3 2 0 0} 4977c478bd9Sstevel@tonic-gatedo_test trigger2-6.1g { 4987c478bd9Sstevel@tonic-gate catchsql { 4997c478bd9Sstevel@tonic-gate INSERT OR ROLLBACK INTO tbl values (3, 2, 3); 5007c478bd9Sstevel@tonic-gate } 5017c478bd9Sstevel@tonic-gate} {1 {column a is not unique}} 5027c478bd9Sstevel@tonic-gatedo_test trigger2-6.1h { 5037c478bd9Sstevel@tonic-gate execsql { 5047c478bd9Sstevel@tonic-gate SELECT * from tbl; 5057c478bd9Sstevel@tonic-gate } 5067c478bd9Sstevel@tonic-gate} {} 5077c478bd9Sstevel@tonic-gateexecsql {DELETE FROM tbl} 5087c478bd9Sstevel@tonic-gate 5097c478bd9Sstevel@tonic-gate 5107c478bd9Sstevel@tonic-gate# Handling of ON CONFLICT by UPDATE statements inside triggers 5117c478bd9Sstevel@tonic-gateexecsql { 5127c478bd9Sstevel@tonic-gate INSERT INTO tbl values (4, 2, 3); 5137c478bd9Sstevel@tonic-gate INSERT INTO tbl values (6, 3, 4); 5147c478bd9Sstevel@tonic-gate CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN 5157c478bd9Sstevel@tonic-gate UPDATE OR IGNORE tbl SET a = new.a, c = 10; 5167c478bd9Sstevel@tonic-gate END; 5177c478bd9Sstevel@tonic-gate} 5187c478bd9Sstevel@tonic-gatedo_test trigger2-6.2a { 5197c478bd9Sstevel@tonic-gate execsql { 5207c478bd9Sstevel@tonic-gate BEGIN; 5217c478bd9Sstevel@tonic-gate UPDATE tbl SET a = 1 WHERE a = 4; 5227c478bd9Sstevel@tonic-gate SELECT * from tbl; 5237c478bd9Sstevel@tonic-gate } 5247c478bd9Sstevel@tonic-gate} {1 2 10 6 3 4} 5257c478bd9Sstevel@tonic-gatedo_test trigger2-6.2b { 5267c478bd9Sstevel@tonic-gate catchsql { 5277c478bd9Sstevel@tonic-gate UPDATE OR ABORT tbl SET a = 4 WHERE a = 1; 5287c478bd9Sstevel@tonic-gate } 5297c478bd9Sstevel@tonic-gate} {1 {column a is not unique}} 5307c478bd9Sstevel@tonic-gatedo_test trigger2-6.2c { 5317c478bd9Sstevel@tonic-gate execsql { 5327c478bd9Sstevel@tonic-gate SELECT * from tbl; 5337c478bd9Sstevel@tonic-gate } 5347c478bd9Sstevel@tonic-gate} {1 2 10 6 3 4} 5357c478bd9Sstevel@tonic-gatedo_test trigger2-6.2d { 5367c478bd9Sstevel@tonic-gate catchsql { 5377c478bd9Sstevel@tonic-gate UPDATE OR FAIL tbl SET a = 4 WHERE a = 1; 5387c478bd9Sstevel@tonic-gate } 5397c478bd9Sstevel@tonic-gate} {1 {column a is not unique}} 5407c478bd9Sstevel@tonic-gatedo_test trigger2-6.2e { 5417c478bd9Sstevel@tonic-gate execsql { 5427c478bd9Sstevel@tonic-gate SELECT * from tbl; 5437c478bd9Sstevel@tonic-gate } 5447c478bd9Sstevel@tonic-gate} {4 2 10 6 3 4} 5457c478bd9Sstevel@tonic-gatedo_test trigger2-6.2f.1 { 5467c478bd9Sstevel@tonic-gate execsql { 5477c478bd9Sstevel@tonic-gate UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4; 5487c478bd9Sstevel@tonic-gate SELECT * from tbl; 5497c478bd9Sstevel@tonic-gate } 5507c478bd9Sstevel@tonic-gate} {1 3 10} 5517c478bd9Sstevel@tonic-gatedo_test trigger2-6.2f.2 { 5527c478bd9Sstevel@tonic-gate execsql { 5537c478bd9Sstevel@tonic-gate INSERT INTO tbl VALUES (2, 3, 4); 5547c478bd9Sstevel@tonic-gate SELECT * FROM tbl; 5557c478bd9Sstevel@tonic-gate } 5567c478bd9Sstevel@tonic-gate} {1 3 10 2 3 4} 5577c478bd9Sstevel@tonic-gatedo_test trigger2-6.2g { 5587c478bd9Sstevel@tonic-gate catchsql { 5597c478bd9Sstevel@tonic-gate UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1; 5607c478bd9Sstevel@tonic-gate } 5617c478bd9Sstevel@tonic-gate} {1 {column a is not unique}} 5627c478bd9Sstevel@tonic-gatedo_test trigger2-6.2h { 5637c478bd9Sstevel@tonic-gate execsql { 5647c478bd9Sstevel@tonic-gate SELECT * from tbl; 5657c478bd9Sstevel@tonic-gate } 5667c478bd9Sstevel@tonic-gate} {4 2 3 6 3 4} 5677c478bd9Sstevel@tonic-gateexecsql { 5687c478bd9Sstevel@tonic-gate DROP TABLE tbl; 5697c478bd9Sstevel@tonic-gate} 5707c478bd9Sstevel@tonic-gate 5717c478bd9Sstevel@tonic-gate# 7. Triggers on views 5727c478bd9Sstevel@tonic-gatedo_test trigger2-7.1 { 5737c478bd9Sstevel@tonic-gate execsql { 5747c478bd9Sstevel@tonic-gate CREATE TABLE ab(a, b); 5757c478bd9Sstevel@tonic-gate CREATE TABLE cd(c, d); 5767c478bd9Sstevel@tonic-gate INSERT INTO ab VALUES (1, 2); 5777c478bd9Sstevel@tonic-gate INSERT INTO ab VALUES (0, 0); 5787c478bd9Sstevel@tonic-gate INSERT INTO cd VALUES (3, 4); 5797c478bd9Sstevel@tonic-gate 580*1da57d55SToomas Soome CREATE TABLE tlog(ii INTEGER PRIMARY KEY, 5817c478bd9Sstevel@tonic-gate olda, oldb, oldc, oldd, newa, newb, newc, newd); 5827c478bd9Sstevel@tonic-gate 5837c478bd9Sstevel@tonic-gate CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd; 5847c478bd9Sstevel@tonic-gate 5857c478bd9Sstevel@tonic-gate CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN 586*1da57d55SToomas Soome INSERT INTO tlog VALUES(NULL, 5877c478bd9Sstevel@tonic-gate old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 5887c478bd9Sstevel@tonic-gate END; 5897c478bd9Sstevel@tonic-gate CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN 590*1da57d55SToomas Soome INSERT INTO tlog VALUES(NULL, 5917c478bd9Sstevel@tonic-gate old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d); 5927c478bd9Sstevel@tonic-gate END; 5937c478bd9Sstevel@tonic-gate 5947c478bd9Sstevel@tonic-gate CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN 595*1da57d55SToomas Soome INSERT INTO tlog VALUES(NULL, 5967c478bd9Sstevel@tonic-gate old.a, old.b, old.c, old.d, 0, 0, 0, 0); 5977c478bd9Sstevel@tonic-gate END; 5987c478bd9Sstevel@tonic-gate CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN 599*1da57d55SToomas Soome INSERT INTO tlog VALUES(NULL, 6007c478bd9Sstevel@tonic-gate old.a, old.b, old.c, old.d, 0, 0, 0, 0); 6017c478bd9Sstevel@tonic-gate END; 6027c478bd9Sstevel@tonic-gate 6037c478bd9Sstevel@tonic-gate CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN 604*1da57d55SToomas Soome INSERT INTO tlog VALUES(NULL, 6057c478bd9Sstevel@tonic-gate 0, 0, 0, 0, new.a, new.b, new.c, new.d); 6067c478bd9Sstevel@tonic-gate END; 6077c478bd9Sstevel@tonic-gate CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN 608*1da57d55SToomas Soome INSERT INTO tlog VALUES(NULL, 6097c478bd9Sstevel@tonic-gate 0, 0, 0, 0, new.a, new.b, new.c, new.d); 6107c478bd9Sstevel@tonic-gate END; 6117c478bd9Sstevel@tonic-gate } 6127c478bd9Sstevel@tonic-gate} {}; 6137c478bd9Sstevel@tonic-gate 6147c478bd9Sstevel@tonic-gatedo_test trigger2-7.2 { 6157c478bd9Sstevel@tonic-gate execsql { 6167c478bd9Sstevel@tonic-gate UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 6177c478bd9Sstevel@tonic-gate DELETE FROM abcd WHERE a = 1; 6187c478bd9Sstevel@tonic-gate INSERT INTO abcd VALUES(10, 20, 30, 40); 6197c478bd9Sstevel@tonic-gate SELECT * FROM tlog; 6207c478bd9Sstevel@tonic-gate } 6217c478bd9Sstevel@tonic-gate} [ list 1 1 2 3 4 100 25 3 4 \ 6227c478bd9Sstevel@tonic-gate 2 1 2 3 4 100 25 3 4 \ 6237c478bd9Sstevel@tonic-gate 3 1 2 3 4 0 0 0 0 \ 6247c478bd9Sstevel@tonic-gate 4 1 2 3 4 0 0 0 0 \ 6257c478bd9Sstevel@tonic-gate 5 0 0 0 0 10 20 30 40 \ 6267c478bd9Sstevel@tonic-gate 6 0 0 0 0 10 20 30 40 ] 6277c478bd9Sstevel@tonic-gate 6287c478bd9Sstevel@tonic-gatedo_test trigger2-7.3 { 6297c478bd9Sstevel@tonic-gate execsql { 6307c478bd9Sstevel@tonic-gate DELETE FROM tlog; 6317c478bd9Sstevel@tonic-gate INSERT INTO abcd VALUES(10, 20, 30, 40); 6327c478bd9Sstevel@tonic-gate UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 6337c478bd9Sstevel@tonic-gate DELETE FROM abcd WHERE a = 1; 6347c478bd9Sstevel@tonic-gate SELECT * FROM tlog; 6357c478bd9Sstevel@tonic-gate } 6367c478bd9Sstevel@tonic-gate} [ list \ 6377c478bd9Sstevel@tonic-gate 1 0 0 0 0 10 20 30 40 \ 6387c478bd9Sstevel@tonic-gate 2 0 0 0 0 10 20 30 40 \ 6397c478bd9Sstevel@tonic-gate 3 1 2 3 4 100 25 3 4 \ 6407c478bd9Sstevel@tonic-gate 4 1 2 3 4 100 25 3 4 \ 6417c478bd9Sstevel@tonic-gate 5 1 2 3 4 0 0 0 0 \ 6427c478bd9Sstevel@tonic-gate 6 1 2 3 4 0 0 0 0 \ 6437c478bd9Sstevel@tonic-gate] 6447c478bd9Sstevel@tonic-gatedo_test trigger2-7.4 { 6457c478bd9Sstevel@tonic-gate execsql { 6467c478bd9Sstevel@tonic-gate DELETE FROM tlog; 6477c478bd9Sstevel@tonic-gate DELETE FROM abcd WHERE a = 1; 6487c478bd9Sstevel@tonic-gate INSERT INTO abcd VALUES(10, 20, 30, 40); 6497c478bd9Sstevel@tonic-gate UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1; 6507c478bd9Sstevel@tonic-gate SELECT * FROM tlog; 6517c478bd9Sstevel@tonic-gate } 6527c478bd9Sstevel@tonic-gate} [ list \ 6537c478bd9Sstevel@tonic-gate 1 1 2 3 4 0 0 0 0 \ 6547c478bd9Sstevel@tonic-gate 2 1 2 3 4 0 0 0 0 \ 6557c478bd9Sstevel@tonic-gate 3 0 0 0 0 10 20 30 40 \ 6567c478bd9Sstevel@tonic-gate 4 0 0 0 0 10 20 30 40 \ 6577c478bd9Sstevel@tonic-gate 5 1 2 3 4 100 25 3 4 \ 6587c478bd9Sstevel@tonic-gate 6 1 2 3 4 100 25 3 4 \ 6597c478bd9Sstevel@tonic-gate] 6607c478bd9Sstevel@tonic-gate 6617c478bd9Sstevel@tonic-gatedo_test trigger2-8.1 { 6627c478bd9Sstevel@tonic-gate execsql { 6637c478bd9Sstevel@tonic-gate CREATE TABLE t1(a,b,c); 6647c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3); 6657c478bd9Sstevel@tonic-gate CREATE VIEW v1 AS 6667c478bd9Sstevel@tonic-gate SELECT a+b AS x, b+c AS y, a+c AS z FROM t1; 6677c478bd9Sstevel@tonic-gate SELECT * FROM v1; 6687c478bd9Sstevel@tonic-gate } 6697c478bd9Sstevel@tonic-gate} {3 5 4} 6707c478bd9Sstevel@tonic-gatedo_test trigger2-8.2 { 6717c478bd9Sstevel@tonic-gate execsql { 6727c478bd9Sstevel@tonic-gate CREATE TABLE v1log(a,b,c,d,e,f); 6737c478bd9Sstevel@tonic-gate CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN 6747c478bd9Sstevel@tonic-gate INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL); 6757c478bd9Sstevel@tonic-gate END; 6767c478bd9Sstevel@tonic-gate DELETE FROM v1 WHERE x=1; 6777c478bd9Sstevel@tonic-gate SELECT * FROM v1log; 6787c478bd9Sstevel@tonic-gate } 6797c478bd9Sstevel@tonic-gate} {} 6807c478bd9Sstevel@tonic-gatedo_test trigger2-8.3 { 6817c478bd9Sstevel@tonic-gate execsql { 6827c478bd9Sstevel@tonic-gate DELETE FROM v1 WHERE x=3; 6837c478bd9Sstevel@tonic-gate SELECT * FROM v1log; 6847c478bd9Sstevel@tonic-gate } 6857c478bd9Sstevel@tonic-gate} {3 {} 5 {} 4 {}} 6867c478bd9Sstevel@tonic-gatedo_test trigger2-8.4 { 6877c478bd9Sstevel@tonic-gate execsql { 6887c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(4,5,6); 6897c478bd9Sstevel@tonic-gate DELETE FROM v1log; 6907c478bd9Sstevel@tonic-gate DELETE FROM v1 WHERE y=11; 6917c478bd9Sstevel@tonic-gate SELECT * FROM v1log; 6927c478bd9Sstevel@tonic-gate } 6937c478bd9Sstevel@tonic-gate} {9 {} 11 {} 10 {}} 6947c478bd9Sstevel@tonic-gatedo_test trigger2-8.5 { 6957c478bd9Sstevel@tonic-gate execsql { 6967c478bd9Sstevel@tonic-gate CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN 6977c478bd9Sstevel@tonic-gate INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z); 6987c478bd9Sstevel@tonic-gate END; 6997c478bd9Sstevel@tonic-gate DELETE FROM v1log; 7007c478bd9Sstevel@tonic-gate INSERT INTO v1 VALUES(1,2,3); 7017c478bd9Sstevel@tonic-gate SELECT * FROM v1log; 7027c478bd9Sstevel@tonic-gate } 7037c478bd9Sstevel@tonic-gate} {{} 1 {} 2 {} 3} 7047c478bd9Sstevel@tonic-gatedo_test trigger2-8.6 { 7057c478bd9Sstevel@tonic-gate execsql { 7067c478bd9Sstevel@tonic-gate CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN 7077c478bd9Sstevel@tonic-gate INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z); 7087c478bd9Sstevel@tonic-gate END; 7097c478bd9Sstevel@tonic-gate DELETE FROM v1log; 7107c478bd9Sstevel@tonic-gate UPDATE v1 SET x=x+100, y=y+200, z=z+300; 7117c478bd9Sstevel@tonic-gate SELECT * FROM v1log; 7127c478bd9Sstevel@tonic-gate } 7137c478bd9Sstevel@tonic-gate} {3 103 5 205 4 304 9 109 11 211 10 310} 7147c478bd9Sstevel@tonic-gate 7157c478bd9Sstevel@tonic-gatedo_test trigger2-9.9 { 7167c478bd9Sstevel@tonic-gate execsql {PRAGMA integrity_check} 7177c478bd9Sstevel@tonic-gate} {ok} 7187c478bd9Sstevel@tonic-gate 7197c478bd9Sstevel@tonic-gatefinish_test 720