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