xref: /sqlite-3.40.0/test/trigger9.test (revision 8f2c54e6) 
1# 2008 January 1
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# This file implements regression tests for SQLite library. Specifically,
12# it tests some compiler optimizations for SQL statements featuring
13# triggers:
14#
15#
16#
17
18# trigger9-1.* -   Test that if there are no references to OLD.* cols, or a
19#                  reference to only OLD.rowid, the data is not loaded.
20#
21# trigger9-2.* -   Test that for NEW.* records populated by UPDATE
22#                  statements, unused fields are populated with NULL values.
23#
24# trigger9-3.* -   Test that the temporary tables used for OLD.* references
25#                  in "INSTEAD OF" triggers have NULL values in unused
26#                  fields.
27#
28
29set testdir [file dirname $argv0]
30source $testdir/tester.tcl
31ifcapable {!trigger} {
32  finish_test
33  return
34}
35
36proc has_rowdata {sql} {
37  expr {[lsearch [execsql "explain $sql"] RowData]>=0}
38}
39
40do_test trigger9-1.1 {
41  execsql {
42    PRAGMA page_size = 1024;
43    CREATE TABLE t1(x, y, z);
44    INSERT INTO t1 VALUES('1', randstr(10000,10000), '2');
45    INSERT INTO t1 VALUES('2', randstr(10000,10000), '4');
46    INSERT INTO t1 VALUES('3', randstr(10000,10000), '6');
47    CREATE TABLE t2(x);
48  }
49} {}
50
51do_test trigger9-1.2.1 {
52  sqlite3 db test.db
53  execsql {
54    BEGIN;
55      CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN
56        INSERT INTO t2 VALUES(old.rowid);
57      END;
58      DELETE FROM t1;
59      SELECT * FROM t2;
60  }
61} {1 2 3}
62do_test trigger9-1.2.3 {
63  has_rowdata {DELETE FROM t1}
64} 0
65do_test trigger9-1.2.4 { execsql { ROLLBACK } } {}
66
67do_test trigger9-1.3.1 {
68  sqlite3 db test.db
69  execsql {
70    BEGIN;
71      CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN
72        INSERT INTO t2 VALUES(old.x);
73      END;
74      DELETE FROM t1;
75      SELECT * FROM t2;
76  }
77} {1 2 3}
78do_test trigger9-1.3.2 {
79  has_rowdata {DELETE FROM t1}
80} 1
81do_test trigger9-1.3.3 { execsql { ROLLBACK } } {}
82
83do_test trigger9-1.4.1 {
84  sqlite3 db test.db
85  execsql {
86    BEGIN;
87      CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN old.x='1' BEGIN
88        INSERT INTO t2 VALUES(old.rowid);
89      END;
90      DELETE FROM t1;
91      SELECT * FROM t2;
92  }
93} {1}
94do_test trigger9-1.4.2 {
95  has_rowdata {DELETE FROM t1}
96} 1
97do_test trigger9-1.4.3 { execsql { ROLLBACK } } {}
98
99do_test trigger9-1.5.1 {
100  sqlite3 db test.db
101  execsql {
102    BEGIN;
103      CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN
104        INSERT INTO t2 VALUES(old.rowid);
105      END;
106      UPDATE t1 SET y = '';
107      SELECT * FROM t2;
108  }
109} {1 2 3}
110do_test trigger9-1.5.2 {
111  has_rowdata {UPDATE t1 SET y = ''}
112} 0
113do_test trigger9-1.5.3 { execsql { ROLLBACK } } {}
114
115do_test trigger9-1.6.1 {
116  sqlite3 db test.db
117  execsql {
118    BEGIN;
119      CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN
120        INSERT INTO t2 VALUES(old.x);
121      END;
122      UPDATE t1 SET y = '';
123      SELECT * FROM t2;
124  }
125} {1 2 3}
126do_test trigger9-1.6.2 {
127  has_rowdata {UPDATE t1 SET y = ''}
128} 1
129do_test trigger9-1.6.3 { execsql { ROLLBACK } } {}
130
131do_test trigger9-1.7.1 {
132  sqlite3 db test.db
133  execsql {
134    BEGIN;
135      CREATE TRIGGER trig1 BEFORE UPDATE ON t1 WHEN old.x>='2' BEGIN
136        INSERT INTO t2 VALUES(old.x);
137      END;
138      UPDATE t1 SET y = '';
139      SELECT * FROM t2;
140  }
141} {2 3}
142do_test trigger9-1.7.2 {
143  has_rowdata {UPDATE t1 SET y = ''}
144} 1
145do_test trigger9-1.7.3 { execsql { ROLLBACK } } {}
146
147finish_test
148