2 # The author disclaims copyright to this source code. In place of
3 # a legal notice, here is a blessing:
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.
9 #***********************************************************************
11 # Tests to make sure that value returned by last_insert_rowid() (LIRID)
12 # is updated properly, especially inside triggers
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)
23 set testdir [file dirname $argv0]
24 source $testdir/tester.tcl
26 # ----------------------------------------------------------------------------
27 # 1.x - basic tests (no triggers)
29 # LIRID changed properly after an insert into a table
30 do_test lastinsert-1.1 {
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();
40 # LIRID unchanged after an update on a table
41 do_test lastinsert-1.2 {
43 update t1 set k=4 where k=2;
44 select last_insert_rowid();
48 # LIRID unchanged after a delete from a table
49 do_test lastinsert-1.3 {
51 delete from t1 where k=4;
52 select last_insert_rowid();
56 # LIRID unchanged after create table/view statements
57 do_test lastinsert-1.4 {
59 create table t2 (k integer primary key, val1, val2, val3);
60 create view v as select * from t1;
61 select last_insert_rowid();
65 # ----------------------------------------------------------------------------
66 # 2.x - tests with after insert trigger
68 # LIRID changed properly after an insert into table containing an after trigger
69 do_test lastinsert-2.1 {
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();
77 insert into t1 values (13);
78 select last_insert_rowid();
82 # LIRID equals NEW.k upon entry into after insert trigger
83 do_test lastinsert-2.2 {
89 # LIRID changed properly by insert within context of after insert trigger
90 do_test lastinsert-2.3 {
96 # LIRID unchanged by update within context of after insert trigger
97 do_test lastinsert-2.4 {
103 # ----------------------------------------------------------------------------
104 # 3.x - tests with after update trigger
106 # LIRID not changed after an update onto a table containing an after trigger
107 do_test lastinsert-3.1 {
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();
116 update t1 set k=14 where k=3;
117 select last_insert_rowid();
121 # LIRID unchanged upon entry into after update trigger
122 do_test lastinsert-3.2 {
128 # LIRID changed properly by insert within context of after update trigger
129 do_test lastinsert-3.3 {
135 # LIRID unchanged by update within context of after update trigger
136 do_test lastinsert-3.4 {
142 # ----------------------------------------------------------------------------
143 # 4.x - tests with instead of insert trigger
145 # LIRID not changed after an insert into view containing an instead of trigger
146 do_test lastinsert-4.1 {
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();
155 insert into v values (15);
156 select last_insert_rowid();
160 # LIRID unchanged upon entry into instead of trigger
161 do_test lastinsert-4.2 {
167 # LIRID changed properly by insert within context of instead of trigger
168 do_test lastinsert-4.3 {
174 # LIRID unchanged by update within context of instead of trigger
175 do_test lastinsert-4.4 {
181 # ----------------------------------------------------------------------------
182 # 5.x - tests with before delete trigger
184 # LIRID not changed after a delete on a table containing a before trigger
185 do_test lastinsert-5.1 {
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();
194 delete from t1 where k=1;
195 select last_insert_rowid();
199 # LIRID unchanged upon entry into delete trigger
200 do_test lastinsert-5.2 {
206 # LIRID changed properly by insert within context of delete trigger
207 do_test lastinsert-5.3 {
213 # LIRID unchanged by update within context of delete trigger
214 do_test lastinsert-5.4 {
220 # ----------------------------------------------------------------------------
221 # 6.x - tests with instead of update trigger
223 # LIRID not changed after an update on a view containing an instead of trigger
224 do_test lastinsert-6.1 {
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();
233 update v set k=16 where k=14;
234 select last_insert_rowid();
238 # LIRID unchanged upon entry into instead of trigger
239 do_test lastinsert-6.2 {
245 # LIRID changed properly by insert within context of instead of trigger
246 do_test lastinsert-6.3 {
252 # LIRID unchanged by update within context of instead of trigger
253 do_test lastinsert-6.4 {
259 # ----------------------------------------------------------------------------
260 # 7.x - complex tests with temporary tables and nested instead of triggers
262 do_test lastinsert-7.1 {
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;
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;
283 insert into t1 values (77);
284 select last_insert_rowid();
288 do_test lastinsert-7.2 {
290 insert into v1 values (5);
291 select last_insert_rowid();
295 do_test lastinsert-7.3 {
297 select rin from rid where k=1;
301 do_test lastinsert-7.4 {
303 select rout from rid where k=1;
307 do_test lastinsert-7.5 {
309 select rin from rid where k=2;
313 do_test lastinsert-7.6 {
315 select rout from rid where k=2;