xref: /sqlite-3.40.0/test/triggerB.test (revision f436620e) 
1# 2008 April 15
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 updating tables with constraints within a trigger.  Ticket #3055.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17ifcapable {!trigger} {
18  finish_test
19  return
20}
21
22# Create test tables with constraints.
23#
24do_test triggerB-1.1 {
25  execsql {
26    CREATE TABLE x(x INTEGER PRIMARY KEY, y INT NOT NULL);
27    INSERT INTO x(y) VALUES(1);
28    INSERT INTO x(y) VALUES(1);
29    CREATE TEMP VIEW vx AS SELECT x, y, 0 AS yy FROM x;
30    CREATE TEMP TRIGGER tx INSTEAD OF UPDATE OF y ON vx
31    BEGIN
32      UPDATE x SET y = new.y WHERE x = new.x;
33    END;
34    SELECT * FROM vx;
35  }
36} {1 1 0 2 1 0}
37do_test triggerB-1.2 {
38  execsql {
39    UPDATE vx SET y = yy;
40    SELECT * FROM vx;
41  }
42} {1 0 0 2 0 0}
43
44# Added 2008-08-22:
45#
46# Name resolution within triggers.
47#
48do_test triggerB-2.1 {
49  catchsql {
50    CREATE TRIGGER ty AFTER INSERT ON x BEGIN
51       SELECT wen.x; -- Unrecognized name
52    END;
53    INSERT INTO x VALUES(1,2);
54  }
55} {1 {no such column: wen.x}}
56do_test triggerB-2.2 {
57  catchsql {
58    CREATE TRIGGER tz AFTER UPDATE ON x BEGIN
59       SELECT dlo.x; -- Unrecognized name
60    END;
61    UPDATE x SET y=y+1;
62  }
63} {1 {no such column: dlo.x}}
64
65do_test triggerB-2.3 {
66  execsql {
67    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
68    INSERT INTO t2 VALUES(1,2);
69    CREATE TABLE changes(x,y);
70    CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN
71      INSERT INTO changes VALUES(new.a, new.b);
72    END;
73  }
74  execsql {
75    UPDATE t2 SET a=a+10;
76    SELECT * FROM changes;
77  }
78} {11 2}
79do_test triggerB-2.4 {
80  execsql {
81    CREATE TRIGGER r2t2 AFTER DELETE ON t2 BEGIN
82      INSERT INTO changes VALUES(old.a, old.c);
83    END;
84  }
85  catchsql {
86    DELETE FROM t2;
87  }
88} {1 {no such column: old.c}}
89
90# Triggers maintain a mask of columns from the invoking table that are
91# used in the trigger body as NEW.column or OLD.column.  That mask is then
92# used to reduce the amount of information that needs to be loaded into
93# the NEW and OLD pseudo-tables at run-time.
94#
95# These tests cases check the logic for when there are many columns - more
96# than will fit in a bitmask.
97#
98do_test triggerB-3.1 {
99  execsql {
100    CREATE TABLE t3(
101       c0,  c1,  c2,  c3,  c4,  c5,  c6,  c7,  c8,  c9,
102       c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
103       c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
104       c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
105       c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,
106       c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,
107       c60, c61, c62, c63, c64, c65
108    );
109    CREATE TABLE t3_changes(colnum, oldval, newval);
110    INSERT INTO t3 VALUES(
111       'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
112       'a10','a11','a12','a13','a14','a15','a16','a17','a18','a19',
113       'a20','a21','a22','a23','a24','a25','a26','a27','a28','a29',
114       'a30','a31','a32','a33','a34','a35','a36','a37','a38','a39',
115       'a40','a41','a42','a43','a44','a45','a46','a47','a48','a49',
116       'a50','a51','a52','a53','a54','a55','a56','a57','a58','a59',
117       'a60','a61','a62','a63','a64','a65'
118    );
119  }
120  for {set i 0} {$i<=65} {incr i} {
121    set sql [subst {
122      CREATE TRIGGER t3c$i AFTER UPDATE ON t3
123         WHEN old.c$i!=new.c$i BEGIN
124          INSERT INTO t3_changes VALUES($i, old.c$i, new.c$i);
125      END
126    }]
127    db eval $sql
128  }
129  execsql {
130    SELECT * FROM t3_changes
131  }
132} {}
133for {set i 0} {$i<=64} {incr i} {
134  do_test triggerB-3.2.$i.1 [subst {
135    execsql {
136      UPDATE t3 SET c$i='b$i';
137      SELECT * FROM t3_changes ORDER BY rowid DESC LIMIT 1;
138    }
139  }] [subst {$i a$i b$i}]
140  do_test triggerB-3.2.$i.2 [subst {
141    execsql {
142      SELECT count(*) FROM t3_changes
143    }
144  }] [expr {$i+1}]
145  do_test triggerB-3.2.$i.2 [subst {
146    execsql {
147      SELECT * FROM t3_changes WHERE colnum=$i
148    }
149  }] [subst {$i a$i b$i}]
150}
151
152
153finish_test
154