1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 37c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 47c478bd9Sstevel@tonic-gate# 57c478bd9Sstevel@tonic-gate# May you do good and not evil. 67c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 77c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 87c478bd9Sstevel@tonic-gate# 97c478bd9Sstevel@tonic-gate#*********************************************************************** 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate# Tests to make sure that value returned by last_insert_rowid() (LIRID) 127c478bd9Sstevel@tonic-gate# is updated properly, especially inside triggers 137c478bd9Sstevel@tonic-gate# 147c478bd9Sstevel@tonic-gate# Note 1: insert into table is now the only statement which changes LIRID 157c478bd9Sstevel@tonic-gate# Note 2: upon entry into before or instead of triggers, 167c478bd9Sstevel@tonic-gate# LIRID is unchanged (rather than -1) 177c478bd9Sstevel@tonic-gate# Note 3: LIRID is changed within the context of a trigger, 187c478bd9Sstevel@tonic-gate# but is restored once the trigger exits 197c478bd9Sstevel@tonic-gate# Note 4: LIRID is not changed by an insert into a view (since everything 207c478bd9Sstevel@tonic-gate# is done within instead of trigger context) 217c478bd9Sstevel@tonic-gate# 227c478bd9Sstevel@tonic-gate 237c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 247c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 257c478bd9Sstevel@tonic-gate 267c478bd9Sstevel@tonic-gate# ---------------------------------------------------------------------------- 277c478bd9Sstevel@tonic-gate# 1.x - basic tests (no triggers) 287c478bd9Sstevel@tonic-gate 297c478bd9Sstevel@tonic-gate# LIRID changed properly after an insert into a table 307c478bd9Sstevel@tonic-gatedo_test lastinsert-1.1 { 317c478bd9Sstevel@tonic-gate catchsql { 327c478bd9Sstevel@tonic-gate create table t1 (k integer primary key); 337c478bd9Sstevel@tonic-gate insert into t1 values (1); 347c478bd9Sstevel@tonic-gate insert into t1 values (NULL); 357c478bd9Sstevel@tonic-gate insert into t1 values (NULL); 367c478bd9Sstevel@tonic-gate select last_insert_rowid(); 377c478bd9Sstevel@tonic-gate } 387c478bd9Sstevel@tonic-gate} {0 3} 397c478bd9Sstevel@tonic-gate 407c478bd9Sstevel@tonic-gate# LIRID unchanged after an update on a table 417c478bd9Sstevel@tonic-gatedo_test lastinsert-1.2 { 427c478bd9Sstevel@tonic-gate catchsql { 437c478bd9Sstevel@tonic-gate update t1 set k=4 where k=2; 447c478bd9Sstevel@tonic-gate select last_insert_rowid(); 457c478bd9Sstevel@tonic-gate } 467c478bd9Sstevel@tonic-gate} {0 3} 477c478bd9Sstevel@tonic-gate 487c478bd9Sstevel@tonic-gate# LIRID unchanged after a delete from a table 497c478bd9Sstevel@tonic-gatedo_test lastinsert-1.3 { 507c478bd9Sstevel@tonic-gate catchsql { 517c478bd9Sstevel@tonic-gate delete from t1 where k=4; 527c478bd9Sstevel@tonic-gate select last_insert_rowid(); 537c478bd9Sstevel@tonic-gate } 547c478bd9Sstevel@tonic-gate} {0 3} 557c478bd9Sstevel@tonic-gate 567c478bd9Sstevel@tonic-gate# LIRID unchanged after create table/view statements 577c478bd9Sstevel@tonic-gatedo_test lastinsert-1.4 { 587c478bd9Sstevel@tonic-gate catchsql { 597c478bd9Sstevel@tonic-gate create table t2 (k integer primary key, val1, val2, val3); 607c478bd9Sstevel@tonic-gate create view v as select * from t1; 617c478bd9Sstevel@tonic-gate select last_insert_rowid(); 627c478bd9Sstevel@tonic-gate } 637c478bd9Sstevel@tonic-gate} {0 3} 647c478bd9Sstevel@tonic-gate 657c478bd9Sstevel@tonic-gate# ---------------------------------------------------------------------------- 667c478bd9Sstevel@tonic-gate# 2.x - tests with after insert trigger 677c478bd9Sstevel@tonic-gate 687c478bd9Sstevel@tonic-gate# LIRID changed properly after an insert into table containing an after trigger 697c478bd9Sstevel@tonic-gatedo_test lastinsert-2.1 { 707c478bd9Sstevel@tonic-gate catchsql { 717c478bd9Sstevel@tonic-gate delete from t2; 727c478bd9Sstevel@tonic-gate create trigger r1 after insert on t1 for each row begin 737c478bd9Sstevel@tonic-gate insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL); 747c478bd9Sstevel@tonic-gate update t2 set k=k+10, val2=100+last_insert_rowid(); 757c478bd9Sstevel@tonic-gate update t2 set val3=1000+last_insert_rowid(); 767c478bd9Sstevel@tonic-gate end; 777c478bd9Sstevel@tonic-gate insert into t1 values (13); 787c478bd9Sstevel@tonic-gate select last_insert_rowid(); 797c478bd9Sstevel@tonic-gate } 807c478bd9Sstevel@tonic-gate} {0 13} 817c478bd9Sstevel@tonic-gate 827c478bd9Sstevel@tonic-gate# LIRID equals NEW.k upon entry into after insert trigger 837c478bd9Sstevel@tonic-gatedo_test lastinsert-2.2 { 847c478bd9Sstevel@tonic-gate catchsql { 857c478bd9Sstevel@tonic-gate select val1 from t2; 867c478bd9Sstevel@tonic-gate } 877c478bd9Sstevel@tonic-gate} {0 13} 887c478bd9Sstevel@tonic-gate 897c478bd9Sstevel@tonic-gate# LIRID changed properly by insert within context of after insert trigger 907c478bd9Sstevel@tonic-gatedo_test lastinsert-2.3 { 917c478bd9Sstevel@tonic-gate catchsql { 927c478bd9Sstevel@tonic-gate select val2 from t2; 937c478bd9Sstevel@tonic-gate } 947c478bd9Sstevel@tonic-gate} {0 126} 957c478bd9Sstevel@tonic-gate 967c478bd9Sstevel@tonic-gate# LIRID unchanged by update within context of after insert trigger 977c478bd9Sstevel@tonic-gatedo_test lastinsert-2.4 { 987c478bd9Sstevel@tonic-gate catchsql { 997c478bd9Sstevel@tonic-gate select val3 from t2; 1007c478bd9Sstevel@tonic-gate } 1017c478bd9Sstevel@tonic-gate} {0 1026} 1027c478bd9Sstevel@tonic-gate 1037c478bd9Sstevel@tonic-gate# ---------------------------------------------------------------------------- 1047c478bd9Sstevel@tonic-gate# 3.x - tests with after update trigger 1057c478bd9Sstevel@tonic-gate 1067c478bd9Sstevel@tonic-gate# LIRID not changed after an update onto a table containing an after trigger 1077c478bd9Sstevel@tonic-gatedo_test lastinsert-3.1 { 1087c478bd9Sstevel@tonic-gate catchsql { 1097c478bd9Sstevel@tonic-gate delete from t2; 1107c478bd9Sstevel@tonic-gate drop trigger r1; 1117c478bd9Sstevel@tonic-gate create trigger r1 after update on t1 for each row begin 1127c478bd9Sstevel@tonic-gate insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL); 1137c478bd9Sstevel@tonic-gate update t2 set k=k+10, val2=100+last_insert_rowid(); 1147c478bd9Sstevel@tonic-gate update t2 set val3=1000+last_insert_rowid(); 1157c478bd9Sstevel@tonic-gate end; 1167c478bd9Sstevel@tonic-gate update t1 set k=14 where k=3; 1177c478bd9Sstevel@tonic-gate select last_insert_rowid(); 1187c478bd9Sstevel@tonic-gate } 1197c478bd9Sstevel@tonic-gate} {0 13} 1207c478bd9Sstevel@tonic-gate 1217c478bd9Sstevel@tonic-gate# LIRID unchanged upon entry into after update trigger 1227c478bd9Sstevel@tonic-gatedo_test lastinsert-3.2 { 1237c478bd9Sstevel@tonic-gate catchsql { 1247c478bd9Sstevel@tonic-gate select val1 from t2; 1257c478bd9Sstevel@tonic-gate } 1267c478bd9Sstevel@tonic-gate} {0 13} 1277c478bd9Sstevel@tonic-gate 1287c478bd9Sstevel@tonic-gate# LIRID changed properly by insert within context of after update trigger 1297c478bd9Sstevel@tonic-gatedo_test lastinsert-3.3 { 1307c478bd9Sstevel@tonic-gate catchsql { 1317c478bd9Sstevel@tonic-gate select val2 from t2; 1327c478bd9Sstevel@tonic-gate } 1337c478bd9Sstevel@tonic-gate} {0 128} 1347c478bd9Sstevel@tonic-gate 1357c478bd9Sstevel@tonic-gate# LIRID unchanged by update within context of after update trigger 1367c478bd9Sstevel@tonic-gatedo_test lastinsert-3.4 { 1377c478bd9Sstevel@tonic-gate catchsql { 1387c478bd9Sstevel@tonic-gate select val3 from t2; 1397c478bd9Sstevel@tonic-gate } 1407c478bd9Sstevel@tonic-gate} {0 1028} 1417c478bd9Sstevel@tonic-gate 1427c478bd9Sstevel@tonic-gate# ---------------------------------------------------------------------------- 1437c478bd9Sstevel@tonic-gate# 4.x - tests with instead of insert trigger 1447c478bd9Sstevel@tonic-gate 1457c478bd9Sstevel@tonic-gate# LIRID not changed after an insert into view containing an instead of trigger 1467c478bd9Sstevel@tonic-gatedo_test lastinsert-4.1 { 1477c478bd9Sstevel@tonic-gate catchsql { 1487c478bd9Sstevel@tonic-gate delete from t2; 1497c478bd9Sstevel@tonic-gate drop trigger r1; 1507c478bd9Sstevel@tonic-gate create trigger r1 instead of insert on v for each row begin 1517c478bd9Sstevel@tonic-gate insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL); 1527c478bd9Sstevel@tonic-gate update t2 set k=k+10, val2=100+last_insert_rowid(); 1537c478bd9Sstevel@tonic-gate update t2 set val3=1000+last_insert_rowid(); 1547c478bd9Sstevel@tonic-gate end; 1557c478bd9Sstevel@tonic-gate insert into v values (15); 1567c478bd9Sstevel@tonic-gate select last_insert_rowid(); 1577c478bd9Sstevel@tonic-gate } 1587c478bd9Sstevel@tonic-gate} {0 13} 1597c478bd9Sstevel@tonic-gate 1607c478bd9Sstevel@tonic-gate# LIRID unchanged upon entry into instead of trigger 1617c478bd9Sstevel@tonic-gatedo_test lastinsert-4.2 { 1627c478bd9Sstevel@tonic-gate catchsql { 1637c478bd9Sstevel@tonic-gate select val1 from t2; 1647c478bd9Sstevel@tonic-gate } 1657c478bd9Sstevel@tonic-gate} {0 13} 1667c478bd9Sstevel@tonic-gate 1677c478bd9Sstevel@tonic-gate# LIRID changed properly by insert within context of instead of trigger 1687c478bd9Sstevel@tonic-gatedo_test lastinsert-4.3 { 1697c478bd9Sstevel@tonic-gate catchsql { 1707c478bd9Sstevel@tonic-gate select val2 from t2; 1717c478bd9Sstevel@tonic-gate } 1727c478bd9Sstevel@tonic-gate} {0 130} 1737c478bd9Sstevel@tonic-gate 1747c478bd9Sstevel@tonic-gate# LIRID unchanged by update within context of instead of trigger 1757c478bd9Sstevel@tonic-gatedo_test lastinsert-4.4 { 1767c478bd9Sstevel@tonic-gate catchsql { 1777c478bd9Sstevel@tonic-gate select val3 from t2; 1787c478bd9Sstevel@tonic-gate } 1797c478bd9Sstevel@tonic-gate} {0 1030} 1807c478bd9Sstevel@tonic-gate 1817c478bd9Sstevel@tonic-gate# ---------------------------------------------------------------------------- 1827c478bd9Sstevel@tonic-gate# 5.x - tests with before delete trigger 1837c478bd9Sstevel@tonic-gate 1847c478bd9Sstevel@tonic-gate# LIRID not changed after a delete on a table containing a before trigger 1857c478bd9Sstevel@tonic-gatedo_test lastinsert-5.1 { 1867c478bd9Sstevel@tonic-gate catchsql { 1877c478bd9Sstevel@tonic-gate delete from t2; 1887c478bd9Sstevel@tonic-gate drop trigger r1; 1897c478bd9Sstevel@tonic-gate create trigger r1 before delete on t1 for each row begin 1907c478bd9Sstevel@tonic-gate insert into t2 values (77, last_insert_rowid(), NULL, NULL); 1917c478bd9Sstevel@tonic-gate update t2 set k=k+10, val2=100+last_insert_rowid(); 1927c478bd9Sstevel@tonic-gate update t2 set val3=1000+last_insert_rowid(); 1937c478bd9Sstevel@tonic-gate end; 1947c478bd9Sstevel@tonic-gate delete from t1 where k=1; 1957c478bd9Sstevel@tonic-gate select last_insert_rowid(); 1967c478bd9Sstevel@tonic-gate } 1977c478bd9Sstevel@tonic-gate} {0 13} 1987c478bd9Sstevel@tonic-gate 1997c478bd9Sstevel@tonic-gate# LIRID unchanged upon entry into delete trigger 2007c478bd9Sstevel@tonic-gatedo_test lastinsert-5.2 { 2017c478bd9Sstevel@tonic-gate catchsql { 2027c478bd9Sstevel@tonic-gate select val1 from t2; 2037c478bd9Sstevel@tonic-gate } 2047c478bd9Sstevel@tonic-gate} {0 13} 2057c478bd9Sstevel@tonic-gate 2067c478bd9Sstevel@tonic-gate# LIRID changed properly by insert within context of delete trigger 2077c478bd9Sstevel@tonic-gatedo_test lastinsert-5.3 { 2087c478bd9Sstevel@tonic-gate catchsql { 2097c478bd9Sstevel@tonic-gate select val2 from t2; 2107c478bd9Sstevel@tonic-gate } 2117c478bd9Sstevel@tonic-gate} {0 177} 2127c478bd9Sstevel@tonic-gate 2137c478bd9Sstevel@tonic-gate# LIRID unchanged by update within context of delete trigger 2147c478bd9Sstevel@tonic-gatedo_test lastinsert-5.4 { 2157c478bd9Sstevel@tonic-gate catchsql { 2167c478bd9Sstevel@tonic-gate select val3 from t2; 2177c478bd9Sstevel@tonic-gate } 2187c478bd9Sstevel@tonic-gate} {0 1077} 2197c478bd9Sstevel@tonic-gate 2207c478bd9Sstevel@tonic-gate# ---------------------------------------------------------------------------- 2217c478bd9Sstevel@tonic-gate# 6.x - tests with instead of update trigger 2227c478bd9Sstevel@tonic-gate 2237c478bd9Sstevel@tonic-gate# LIRID not changed after an update on a view containing an instead of trigger 2247c478bd9Sstevel@tonic-gatedo_test lastinsert-6.1 { 2257c478bd9Sstevel@tonic-gate catchsql { 2267c478bd9Sstevel@tonic-gate delete from t2; 2277c478bd9Sstevel@tonic-gate drop trigger r1; 2287c478bd9Sstevel@tonic-gate create trigger r1 instead of update on v for each row begin 2297c478bd9Sstevel@tonic-gate insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL); 2307c478bd9Sstevel@tonic-gate update t2 set k=k+10, val2=100+last_insert_rowid(); 2317c478bd9Sstevel@tonic-gate update t2 set val3=1000+last_insert_rowid(); 2327c478bd9Sstevel@tonic-gate end; 2337c478bd9Sstevel@tonic-gate update v set k=16 where k=14; 2347c478bd9Sstevel@tonic-gate select last_insert_rowid(); 2357c478bd9Sstevel@tonic-gate } 2367c478bd9Sstevel@tonic-gate} {0 13} 2377c478bd9Sstevel@tonic-gate 2387c478bd9Sstevel@tonic-gate# LIRID unchanged upon entry into instead of trigger 2397c478bd9Sstevel@tonic-gatedo_test lastinsert-6.2 { 2407c478bd9Sstevel@tonic-gate catchsql { 2417c478bd9Sstevel@tonic-gate select val1 from t2; 2427c478bd9Sstevel@tonic-gate } 2437c478bd9Sstevel@tonic-gate} {0 13} 2447c478bd9Sstevel@tonic-gate 2457c478bd9Sstevel@tonic-gate# LIRID changed properly by insert within context of instead of trigger 2467c478bd9Sstevel@tonic-gatedo_test lastinsert-6.3 { 2477c478bd9Sstevel@tonic-gate catchsql { 2487c478bd9Sstevel@tonic-gate select val2 from t2; 2497c478bd9Sstevel@tonic-gate } 2507c478bd9Sstevel@tonic-gate} {0 132} 2517c478bd9Sstevel@tonic-gate 2527c478bd9Sstevel@tonic-gate# LIRID unchanged by update within context of instead of trigger 2537c478bd9Sstevel@tonic-gatedo_test lastinsert-6.4 { 2547c478bd9Sstevel@tonic-gate catchsql { 2557c478bd9Sstevel@tonic-gate select val3 from t2; 2567c478bd9Sstevel@tonic-gate } 2577c478bd9Sstevel@tonic-gate} {0 1032} 2587c478bd9Sstevel@tonic-gate 2597c478bd9Sstevel@tonic-gate# ---------------------------------------------------------------------------- 2607c478bd9Sstevel@tonic-gate# 7.x - complex tests with temporary tables and nested instead of triggers 2617c478bd9Sstevel@tonic-gate 2627c478bd9Sstevel@tonic-gatedo_test lastinsert-7.1 { 2637c478bd9Sstevel@tonic-gate catchsql { 2647c478bd9Sstevel@tonic-gate drop table t1; drop table t2; drop trigger r1; 2657c478bd9Sstevel@tonic-gate create temp table t1 (k integer primary key); 2667c478bd9Sstevel@tonic-gate create temp table t2 (k integer primary key); 2677c478bd9Sstevel@tonic-gate create temp view v1 as select * from t1; 2687c478bd9Sstevel@tonic-gate create temp view v2 as select * from t2; 2697c478bd9Sstevel@tonic-gate create temp table rid (k integer primary key, rin, rout); 2707c478bd9Sstevel@tonic-gate insert into rid values (1, NULL, NULL); 2717c478bd9Sstevel@tonic-gate insert into rid values (2, NULL, NULL); 2727c478bd9Sstevel@tonic-gate create temp trigger r1 instead of insert on v1 for each row begin 2737c478bd9Sstevel@tonic-gate update rid set rin=last_insert_rowid() where k=1; 2747c478bd9Sstevel@tonic-gate insert into t1 values (100+NEW.k); 2757c478bd9Sstevel@tonic-gate insert into v2 values (100+last_insert_rowid()); 2767c478bd9Sstevel@tonic-gate update rid set rout=last_insert_rowid() where k=1; 2777c478bd9Sstevel@tonic-gate end; 2787c478bd9Sstevel@tonic-gate create temp trigger r2 instead of insert on v2 for each row begin 2797c478bd9Sstevel@tonic-gate update rid set rin=last_insert_rowid() where k=2; 2807c478bd9Sstevel@tonic-gate insert into t2 values (1000+NEW.k); 2817c478bd9Sstevel@tonic-gate update rid set rout=last_insert_rowid() where k=2; 2827c478bd9Sstevel@tonic-gate end; 2837c478bd9Sstevel@tonic-gate insert into t1 values (77); 2847c478bd9Sstevel@tonic-gate select last_insert_rowid(); 2857c478bd9Sstevel@tonic-gate } 2867c478bd9Sstevel@tonic-gate} {0 77} 2877c478bd9Sstevel@tonic-gate 2887c478bd9Sstevel@tonic-gatedo_test lastinsert-7.2 { 2897c478bd9Sstevel@tonic-gate catchsql { 2907c478bd9Sstevel@tonic-gate insert into v1 values (5); 2917c478bd9Sstevel@tonic-gate select last_insert_rowid(); 2927c478bd9Sstevel@tonic-gate } 2937c478bd9Sstevel@tonic-gate} {0 77} 2947c478bd9Sstevel@tonic-gate 2957c478bd9Sstevel@tonic-gatedo_test lastinsert-7.3 { 2967c478bd9Sstevel@tonic-gate catchsql { 2977c478bd9Sstevel@tonic-gate select rin from rid where k=1; 2987c478bd9Sstevel@tonic-gate } 2997c478bd9Sstevel@tonic-gate} {0 77} 3007c478bd9Sstevel@tonic-gate 3017c478bd9Sstevel@tonic-gatedo_test lastinsert-7.4 { 3027c478bd9Sstevel@tonic-gate catchsql { 3037c478bd9Sstevel@tonic-gate select rout from rid where k=1; 3047c478bd9Sstevel@tonic-gate } 3057c478bd9Sstevel@tonic-gate} {0 105} 3067c478bd9Sstevel@tonic-gate 3077c478bd9Sstevel@tonic-gatedo_test lastinsert-7.5 { 3087c478bd9Sstevel@tonic-gate catchsql { 3097c478bd9Sstevel@tonic-gate select rin from rid where k=2; 3107c478bd9Sstevel@tonic-gate } 3117c478bd9Sstevel@tonic-gate} {0 105} 3127c478bd9Sstevel@tonic-gate 3137c478bd9Sstevel@tonic-gatedo_test lastinsert-7.6 { 3147c478bd9Sstevel@tonic-gate catchsql { 3157c478bd9Sstevel@tonic-gate select rout from rid where k=2; 3167c478bd9Sstevel@tonic-gate } 3177c478bd9Sstevel@tonic-gate} {0 1205} 3187c478bd9Sstevel@tonic-gate 3197c478bd9Sstevel@tonic-gatefinish_test 3207c478bd9Sstevel@tonic-gate 321