3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing corner cases of the INSERT statement.
14 # $Id: insert3.test,v 1.3 2005/01/29 08:32:46 danielk1977 Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 # All the tests in this file require trigger support
23 # Create a table and a corresponding insert trigger. Do a self-insert
29 CREATE TABLE log(x UNIQUE, y);
30 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
31 UPDATE log SET y=y+1 WHERE x=new.a;
32 INSERT OR IGNORE INTO log VALUES(new.a, 1);
34 INSERT INTO t1 VALUES('hello','world');
35 INSERT INTO t1 VALUES(5,10);
36 SELECT * FROM log ORDER BY x;
41 INSERT INTO t1 SELECT a, b+10 FROM t1;
42 SELECT * FROM log ORDER BY x;
47 CREATE TABLE log2(x PRIMARY KEY,y);
48 CREATE TRIGGER r2 BEFORE INSERT ON t1 BEGIN
49 UPDATE log2 SET y=y+1 WHERE x=new.b;
50 INSERT OR IGNORE INTO log2 VALUES(new.b,1);
52 INSERT INTO t1 VALUES(453,'hi');
53 SELECT * FROM log ORDER BY x;
58 SELECT * FROM log2 ORDER BY x;
64 INSERT INTO t1 SELECT * FROM t1;
65 SELECT 'a:', x, y FROM log UNION ALL
66 SELECT 'b:', x, y FROM log2 ORDER BY x;
68 } {a: 5 4 b: 10 2 b: 20 1 a: 453 2 a: hello 4 b: hi 2 b: world 1}
71 INSERT INTO t1(a) VALUES('xyz');
72 SELECT * FROM log ORDER BY x;
74 } {5 4 453 2 hello 4 xyz 1}
80 a INTEGER PRIMARY KEY,
84 CREATE TABLE t2dup(a,b,c);
85 CREATE TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN
86 INSERT INTO t2dup(a,b,c) VALUES(new.a,new.b,new.c);
88 INSERT INTO t2(a) VALUES(123);
89 INSERT INTO t2(b) VALUES(234);
90 INSERT INTO t2(c) VALUES(345);
93 } {123 b c -1 234 c -1 b 345}
97 INSERT INTO t2(a) SELECT 1 FROM t1 LIMIT 1;
98 INSERT INTO t2(b) SELECT 987 FROM t1 LIMIT 1;
99 INSERT INTO t2(c) SELECT 876 FROM t1 LIMIT 1;
102 } {1 b c -1 987 c -1 b 876}
104 # Test for proper detection of malformed WHEN clauses on INSERT triggers.
106 do_test insert3-3.1 {
108 CREATE TABLE t3(a,b,c);
109 CREATE TRIGGER t3r1 BEFORE INSERT on t3 WHEN nosuchcol BEGIN
110 SELECT 'illegal WHEN clause';
114 do_test insert3-3.2 {
116 INSERT INTO t3 VALUES(1,2,3)
118 } {1 {no such column: nosuchcol}}
119 do_test insert3-3.3 {
121 CREATE TABLE t4(a,b,c);
122 CREATE TRIGGER t4r1 AFTER INSERT on t4 WHEN nosuchcol BEGIN
123 SELECT 'illegal WHEN clause';
127 do_test insert3-3.4 {
129 INSERT INTO t4 VALUES(1,2,3)
131 } {1 {no such column: nosuchcol}}
133 } ;# ifcapable {trigger}