xref: /sqlite-3.40.0/test/insert3.test (revision 39df51b2) 
1# 2005 January 13
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.  The
12# focus of this file is testing corner cases of the INSERT statement.
13#
14# $Id: insert3.test,v 1.2 2005/01/15 00:36:37 drh Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# All the tests in this file require trigger support
20#
21ifcapable {trigger} {
22
23# Create a table and a corresponding insert trigger.  Do a self-insert
24# into the table.
25#
26do_test insert3-1.0 {
27  execsql {
28    CREATE TABLE t1(a,b);
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);
33    END;
34    INSERT INTO t1 VALUES('hello','world');
35    INSERT INTO t1 VALUES(5,10);
36    SELECT * FROM log ORDER BY x;
37  }
38} {5 1 hello 1}
39do_test insert3-1.1 {
40  execsql {
41    INSERT INTO t1 SELECT a, b+10 FROM t1;
42    SELECT * FROM log ORDER BY x;
43  }
44} {5 2 hello 2}
45do_test insert3-1.2 {
46  execsql {
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);
51    END;
52    INSERT INTO t1 VALUES(453,'hi');
53    SELECT * FROM log ORDER BY x;
54  }
55} {5 2 453 1 hello 2}
56do_test insert3-1.3 {
57  execsql {
58    SELECT * FROM log2 ORDER BY x;
59  }
60} {hi 1}
61do_test insert3-1.4 {
62  execsql {
63    INSERT INTO t1 SELECT * FROM t1;
64    SELECT 'a:', x, y FROM log UNION ALL SELECT 'b:', x, y FROM log2 ORDER BY x;
65  }
66} {a: 5 4 b: 10 2 b: 20 1 a: 453 2 a: hello 4 b: hi 2 b: world 1}
67do_test insert3-1.5 {
68  execsql {
69    INSERT INTO t1(a) VALUES('xyz');
70    SELECT * FROM log ORDER BY x;
71  }
72} {5 4 453 2 hello 4 xyz 1}
73
74do_test insert3-2.1 {
75  execsql {
76    CREATE TABLE t2(
77      a INTEGER PRIMARY KEY,
78      b DEFAULT 'b',
79      c DEFAULT 'c'
80    );
81    CREATE TABLE t2dup(a,b,c);
82    CREATE TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN
83      INSERT INTO t2dup(a,b,c) VALUES(new.a,new.b,new.c);
84    END;
85    INSERT INTO t2(a) VALUES(123);
86    INSERT INTO t2(b) VALUES(234);
87    INSERT INTO t2(c) VALUES(345);
88    SELECT * FROM t2dup;
89  }
90} {123 b c -1 234 c -1 b 345}
91do_test insert3-2.2 {
92  execsql {
93    DELETE FROM t2dup;
94    INSERT INTO t2(a) SELECT 1 FROM t1 LIMIT 1;
95    INSERT INTO t2(b) SELECT 987 FROM t1 LIMIT 1;
96    INSERT INTO t2(c) SELECT 876 FROM t1 LIMIT 1;
97    SELECT * FROM t2dup;
98  }
99} {1 b c -1 987 c -1 b 876}
100
101# Test for proper detection of malformed WHEN clauses on INSERT triggers.
102#
103do_test insert3-3.1 {
104  execsql {
105    CREATE TABLE t3(a,b,c);
106    CREATE TRIGGER t3r1 BEFORE INSERT on t3 WHEN nosuchcol BEGIN
107      SELECT 'illegal WHEN clause';
108    END;
109  }
110} {}
111do_test insert3-3.2 {
112  catchsql {
113    INSERT INTO t3 VALUES(1,2,3)
114  }
115} {1 {no such column: nosuchcol}}
116do_test insert3-3.3 {
117  execsql {
118    CREATE TABLE t4(a,b,c);
119    CREATE TRIGGER t4r1 AFTER INSERT on t4 WHEN nosuchcol BEGIN
120      SELECT 'illegal WHEN clause';
121    END;
122  }
123} {}
124do_test insert3-3.4 {
125  catchsql {
126    INSERT INTO t4 VALUES(1,2,3)
127  }
128} {1 {no such column: nosuchcol}}
129
130} ;# ifcapable {trigger}
131
132finish_test
133