drh | f573c99 | 2002-07-31 00:32:50 +0000 | [diff] [blame] | 1 | # The author disclaims copyright to this source code. In place of |
| 2 | # a legal notice, here is a blessing: |
| 3 | # |
| 4 | # May you do good and not evil. |
| 5 | # May you find forgiveness for yourself and forgive others. |
| 6 | # May you share freely, never taking more than you give. |
| 7 | # |
| 8 | #*********************************************************************** |
| 9 | # |
| 10 | # This file tests the triggers of views. |
| 11 | # |
| 12 | |
| 13 | set testdir [file dirname $argv0] |
| 14 | source $testdir/tester.tcl |
drh | b7f9164 | 2004-10-31 02:22:47 +0000 | [diff] [blame^] | 15 | ifcapable {!trigger} { |
| 16 | finish_test |
| 17 | return |
| 18 | } |
drh | f573c99 | 2002-07-31 00:32:50 +0000 | [diff] [blame] | 19 | |
| 20 | do_test trigger4-1.1 { |
| 21 | execsql { |
| 22 | create table test1(id integer primary key,a); |
| 23 | create table test2(id integer,b); |
| 24 | create view test as |
| 25 | select test1.id as id,a as a,b as b |
| 26 | from test1 join test2 on test2.id = test1.id; |
| 27 | create trigger I_test instead of insert on test |
| 28 | begin |
| 29 | insert into test1 (id,a) values (NEW.id,NEW.a); |
| 30 | insert into test2 (id,b) values (NEW.id,NEW.b); |
| 31 | end; |
| 32 | insert into test values(1,2,3); |
| 33 | select * from test1; |
| 34 | } |
| 35 | } {1 2} |
| 36 | do_test trigger4-1.2 { |
| 37 | execsql { |
| 38 | select * from test2; |
| 39 | } |
| 40 | } {1 3} |
| 41 | do_test trigger4-1.3 { |
| 42 | db close |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 43 | sqlite3 db test.db |
drh | f573c99 | 2002-07-31 00:32:50 +0000 | [diff] [blame] | 44 | execsql { |
| 45 | insert into test values(4,5,6); |
| 46 | select * from test1; |
| 47 | } |
| 48 | } {1 2 4 5} |
| 49 | do_test trigger4-1.4 { |
| 50 | execsql { |
| 51 | select * from test2; |
| 52 | } |
| 53 | } {1 3 4 6} |
| 54 | |
| 55 | do_test trigger4-2.1 { |
| 56 | execsql { |
| 57 | create trigger U_test instead of update on test |
| 58 | begin |
| 59 | update test1 set a=NEW.a where id=NEW.id; |
| 60 | update test2 set b=NEW.b where id=NEW.id; |
| 61 | end; |
| 62 | update test set a=22 where id=1; |
| 63 | select * from test1; |
| 64 | } |
| 65 | } {1 22 4 5} |
| 66 | do_test trigger4-2.2 { |
| 67 | execsql { |
| 68 | select * from test2; |
| 69 | } |
| 70 | } {1 3 4 6} |
| 71 | do_test trigger4-2.3 { |
| 72 | db close |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 73 | sqlite3 db test.db |
drh | f573c99 | 2002-07-31 00:32:50 +0000 | [diff] [blame] | 74 | execsql { |
| 75 | update test set b=66 where id=4; |
| 76 | select * from test1; |
| 77 | } |
| 78 | } {1 22 4 5} |
| 79 | do_test trigger4-2.4 { |
| 80 | execsql { |
| 81 | select * from test2; |
| 82 | } |
| 83 | } {1 3 4 66} |
| 84 | |
| 85 | do_test trigger4-3.1 { |
| 86 | catchsql { |
| 87 | drop table test2; |
| 88 | insert into test values(7,8,9); |
| 89 | } |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 90 | } {1 {no such table: main.test2}} |
drh | f573c99 | 2002-07-31 00:32:50 +0000 | [diff] [blame] | 91 | do_test trigger4-3.2 { |
| 92 | db close |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 93 | sqlite3 db test.db |
drh | f573c99 | 2002-07-31 00:32:50 +0000 | [diff] [blame] | 94 | catchsql { |
| 95 | insert into test values(7,8,9); |
| 96 | } |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 97 | } {1 {no such table: main.test2}} |
drh | f573c99 | 2002-07-31 00:32:50 +0000 | [diff] [blame] | 98 | do_test trigger4-3.3 { |
| 99 | catchsql { |
| 100 | update test set a=222 where id=1; |
| 101 | } |
drh | f26e09c | 2003-05-31 16:21:12 +0000 | [diff] [blame] | 102 | } {1 {no such table: main.test2}} |
drh | f573c99 | 2002-07-31 00:32:50 +0000 | [diff] [blame] | 103 | do_test trigger4-3.4 { |
| 104 | execsql { |
| 105 | select * from test1; |
| 106 | } |
| 107 | } {1 22 4 5} |
| 108 | do_test trigger4-3.5 { |
| 109 | execsql { |
| 110 | create table test2(id,b); |
| 111 | insert into test values(7,8,9); |
| 112 | select * from test1; |
| 113 | } |
| 114 | } {1 22 4 5 7 8} |
| 115 | do_test trigger4-3.6 { |
| 116 | execsql { |
| 117 | select * from test2; |
| 118 | } |
| 119 | } {7 9} |
| 120 | do_test trigger4-3.7 { |
| 121 | db close |
drh | ef4ac8f | 2004-06-19 00:16:31 +0000 | [diff] [blame] | 122 | sqlite3 db test.db |
drh | f573c99 | 2002-07-31 00:32:50 +0000 | [diff] [blame] | 123 | execsql { |
| 124 | update test set b=99 where id=7; |
| 125 | select * from test2; |
| 126 | } |
| 127 | } {7 99} |
| 128 | |
drh | ed717fe | 2003-06-15 23:42:24 +0000 | [diff] [blame] | 129 | integrity_check trigger4-4.1 |
| 130 | |
drh | f573c99 | 2002-07-31 00:32:50 +0000 | [diff] [blame] | 131 | finish_test |