1#
2# The author disclaims copyright to this source code.  In place of
3# a legal notice, here is a blessing:
4#
5#    May you do good and not evil.
6#    May you find forgiveness for yourself and forgive others.
7#    May you share freely, never taking more than you give.
8#
9#***********************************************************************
10#
11# Tests to make sure that value returned by last_insert_rowid() (LIRID)
12# is updated properly, especially inside triggers
13#
14# Note 1: insert into table is now the only statement which changes LIRID
15# Note 2: upon entry into before or instead of triggers,
16#           LIRID is unchanged (rather than -1)
17# Note 3: LIRID is changed within the context of a trigger,
18#           but is restored once the trigger exits
19# Note 4: LIRID is not changed by an insert into a view (since everything
20#           is done within instead of trigger context)
21#
22
23set testdir [file dirname $argv0]
24source $testdir/tester.tcl
25
26# ----------------------------------------------------------------------------
27# 1.x - basic tests (no triggers)
28
29# LIRID changed properly after an insert into a table
30do_test lastinsert-1.1 {
31    catchsql {
32        create table t1 (k integer primary key);
33        insert into t1 values (1);
34        insert into t1 values (NULL);
35        insert into t1 values (NULL);
36        select last_insert_rowid();
37    }
38} {0 3}
39
40# LIRID unchanged after an update on a table
41do_test lastinsert-1.2 {
42    catchsql {
43        update t1 set k=4 where k=2;
44        select last_insert_rowid();
45    }
46} {0 3}
47
48# LIRID unchanged after a delete from a table
49do_test lastinsert-1.3 {
50    catchsql {
51        delete from t1 where k=4;
52        select last_insert_rowid();
53    }
54} {0 3}
55
56# LIRID unchanged after create table/view statements
57do_test lastinsert-1.4 {
58    catchsql {
59        create table t2 (k integer primary key, val1, val2, val3);
60        create view v as select * from t1;
61        select last_insert_rowid();
62    }
63} {0 3}
64
65# ----------------------------------------------------------------------------
66# 2.x - tests with after insert trigger
67
68# LIRID changed properly after an insert into table containing an after trigger
69do_test lastinsert-2.1 {
70    catchsql {
71        delete from t2;
72        create trigger r1 after insert on t1 for each row begin
73            insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
74            update t2 set k=k+10, val2=100+last_insert_rowid();
75            update t2 set val3=1000+last_insert_rowid();
76        end;
77        insert into t1 values (13);
78        select last_insert_rowid();
79    }
80} {0 13}
81
82# LIRID equals NEW.k upon entry into after insert trigger
83do_test lastinsert-2.2 {
84    catchsql {
85        select val1 from t2;
86    }
87} {0 13}
88
89# LIRID changed properly by insert within context of after insert trigger
90do_test lastinsert-2.3 {
91    catchsql {
92        select val2 from t2;
93    }
94} {0 126}
95
96# LIRID unchanged by update within context of after insert trigger
97do_test lastinsert-2.4 {
98    catchsql {
99        select val3 from t2;
100    }
101} {0 1026}
102
103# ----------------------------------------------------------------------------
104# 3.x - tests with after update trigger
105
106# LIRID not changed after an update onto a table containing an after trigger
107do_test lastinsert-3.1 {
108    catchsql {
109        delete from t2;
110        drop trigger r1;
111        create trigger r1 after update on t1 for each row begin
112            insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
113            update t2 set k=k+10, val2=100+last_insert_rowid();
114            update t2 set val3=1000+last_insert_rowid();
115        end;
116        update t1 set k=14 where k=3;
117        select last_insert_rowid();
118    }
119} {0 13}
120
121# LIRID unchanged upon entry into after update trigger
122do_test lastinsert-3.2 {
123    catchsql {
124        select val1 from t2;
125    }
126} {0 13}
127
128# LIRID changed properly by insert within context of after update trigger
129do_test lastinsert-3.3 {
130    catchsql {
131        select val2 from t2;
132    }
133} {0 128}
134
135# LIRID unchanged by update within context of after update trigger
136do_test lastinsert-3.4 {
137    catchsql {
138        select val3 from t2;
139    }
140} {0 1028}
141
142# ----------------------------------------------------------------------------
143# 4.x - tests with instead of insert trigger
144
145# LIRID not changed after an insert into view containing an instead of trigger
146do_test lastinsert-4.1 {
147    catchsql {
148        delete from t2;
149        drop trigger r1;
150        create trigger r1 instead of insert on v for each row begin
151            insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
152            update t2 set k=k+10, val2=100+last_insert_rowid();
153            update t2 set val3=1000+last_insert_rowid();
154        end;
155        insert into v values (15);
156        select last_insert_rowid();
157    }
158} {0 13}
159
160# LIRID unchanged upon entry into instead of trigger
161do_test lastinsert-4.2 {
162    catchsql {
163        select val1 from t2;
164    }
165} {0 13}
166
167# LIRID changed properly by insert within context of instead of trigger
168do_test lastinsert-4.3 {
169    catchsql {
170        select val2 from t2;
171    }
172} {0 130}
173
174# LIRID unchanged by update within context of instead of trigger
175do_test lastinsert-4.4 {
176    catchsql {
177        select val3 from t2;
178    }
179} {0 1030}
180
181# ----------------------------------------------------------------------------
182# 5.x - tests with before delete trigger
183
184# LIRID not changed after a delete on a table containing a before trigger
185do_test lastinsert-5.1 {
186    catchsql {
187        delete from t2;
188        drop trigger r1;
189        create trigger r1 before delete on t1 for each row begin
190            insert into t2 values (77, last_insert_rowid(), NULL, NULL);
191            update t2 set k=k+10, val2=100+last_insert_rowid();
192            update t2 set val3=1000+last_insert_rowid();
193        end;
194        delete from t1 where k=1;
195        select last_insert_rowid();
196    }
197} {0 13}
198
199# LIRID unchanged upon entry into delete trigger
200do_test lastinsert-5.2 {
201    catchsql {
202        select val1 from t2;
203    }
204} {0 13}
205
206# LIRID changed properly by insert within context of delete trigger
207do_test lastinsert-5.3 {
208    catchsql {
209        select val2 from t2;
210    }
211} {0 177}
212
213# LIRID unchanged by update within context of delete trigger
214do_test lastinsert-5.4 {
215    catchsql {
216        select val3 from t2;
217    }
218} {0 1077}
219
220# ----------------------------------------------------------------------------
221# 6.x - tests with instead of update trigger
222
223# LIRID not changed after an update on a view containing an instead of trigger
224do_test lastinsert-6.1 {
225    catchsql {
226        delete from t2;
227        drop trigger r1;
228        create trigger r1 instead of update on v for each row begin
229            insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
230            update t2 set k=k+10, val2=100+last_insert_rowid();
231            update t2 set val3=1000+last_insert_rowid();
232        end;
233        update v set k=16 where k=14;
234        select last_insert_rowid();
235    }
236} {0 13}
237
238# LIRID unchanged upon entry into instead of trigger
239do_test lastinsert-6.2 {
240    catchsql {
241        select val1 from t2;
242    }
243} {0 13}
244
245# LIRID changed properly by insert within context of instead of trigger
246do_test lastinsert-6.3 {
247    catchsql {
248        select val2 from t2;
249    }
250} {0 132}
251
252# LIRID unchanged by update within context of instead of trigger
253do_test lastinsert-6.4 {
254    catchsql {
255        select val3 from t2;
256    }
257} {0 1032}
258
259# ----------------------------------------------------------------------------
260# 7.x - complex tests with temporary tables and nested instead of triggers
261
262do_test lastinsert-7.1 {
263    catchsql {
264        drop table t1; drop table t2; drop trigger r1;
265        create temp table t1 (k integer primary key);
266        create temp table t2 (k integer primary key);
267        create temp view v1 as select * from t1;
268        create temp view v2 as select * from t2;
269        create temp table rid (k integer primary key, rin, rout);
270        insert into rid values (1, NULL, NULL);
271        insert into rid values (2, NULL, NULL);
272        create temp trigger r1 instead of insert on v1 for each row begin
273            update rid set rin=last_insert_rowid() where k=1;
274            insert into t1 values (100+NEW.k);
275            insert into v2 values (100+last_insert_rowid());
276            update rid set rout=last_insert_rowid() where k=1;
277        end;
278        create temp trigger r2 instead of insert on v2 for each row begin
279            update rid set rin=last_insert_rowid() where k=2;
280            insert into t2 values (1000+NEW.k);
281            update rid set rout=last_insert_rowid() where k=2;
282        end;
283        insert into t1 values (77);
284        select last_insert_rowid();
285    }
286} {0 77}
287
288do_test lastinsert-7.2 {
289    catchsql {
290        insert into v1 values (5);
291        select last_insert_rowid();
292    }
293} {0 77}
294
295do_test lastinsert-7.3 {
296    catchsql {
297        select rin from rid where k=1;
298    }
299} {0 77}
300
301do_test lastinsert-7.4 {
302    catchsql {
303        select rout from rid where k=1;
304    }
305} {0 105}
306
307do_test lastinsert-7.5 {
308    catchsql {
309        select rin from rid where k=2;
310    }
311} {0 105}
312
313do_test lastinsert-7.6 {
314    catchsql {
315        select rout from rid where k=2;
316    }
317} {0 1205}
318
319finish_test
320
321