📄 autoincrement.out
字号:
1 ij> insert into ai_single1 (c) values ('b');1 row inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------3 ij> insert into ai_single2 (c) values ('b');1 row inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------2 ij> insert into ai_single3 (c) values ('b');1 row inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------3 ij> drop table ai_single1;0 rows inserted/updated/deletedij> drop table ai_single2;0 rows inserted/updated/deletedij> drop table ai_single3;0 rows inserted/updated/deletedij> drop table ai_test;0 rows inserted/updated/deletedij> -- nested, nested, nested stuff.-- t1 --> trigger --> insert into t2 -- insert row into t1.-- I can get lastAutoincrementValue for t1 but not t2.create table t1 (c1 int generated always as identity, name char(32));0 rows inserted/updated/deletedij> create table t2 (c2 int generated always as identity, name char(32));0 rows inserted/updated/deletedij> create trigger insert_trigger after insert on t1 for each row mode db2sql insert into t2 (name) values ('Bob Finocchio');0 rows inserted/updated/deletedij> insert into t1 (name) values ('Phil White');1 row inserted/updated/deletedij> select * from t1;C1 |NAME --------------------------------------------1 |Phil White ij> select * from t2;C2 |NAME --------------------------------------------1 |Bob Finocchio ij> values IDENTITY_VAL_LOCAL();1 -------------------------------1 ij> insert into t2 (name) values ('Jean-Yves Dexemier');1 row inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------2 ij> -- insert into multiple tables in different schema names with same tablename,column names-- make sure -- lastAutoincrementValue shouldn't get confused.....drop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> -- APP.TAB1.A1 ==> -1,-2,-3-- APP.TAB1.A2 ==> 1,2,3-- APP.TAB2.A1 ==> 0,-2,-4-- APP.TAB3.A2 ==> 0,2,4create table tab1 (i int, a1 int generated always as identity (start with -1, increment by -1));0 rows inserted/updated/deletedij> create table tab2 (i int, a2 smallint generated always as identity (start with 1, increment by +1));0 rows inserted/updated/deletedij> create table tab3 (i int, a1 int generated always as identity (start with 0, increment by -2));0 rows inserted/updated/deletedij> create table tab4 (i int, a2 bigint generated always as identity (start with 0, increment by 2));0 rows inserted/updated/deletedij> create schema BPP;0 rows inserted/updated/deletedij> set schema BPP;0 rows inserted/updated/deletedij> -- BPP.TAB1.A1 ==> 100,101,102-- BPP.TAB2.A2 ==> 100,99,98-- BPP.TAB3.A1 ==> 100,102,104-- BPP.TAB4.A2 ==> 100,98,96create table tab1 (i int, a1 int generated always as identity (start with 100, increment by 1));0 rows inserted/updated/deletedij> create table tab2 (i int, a2 bigint generated always as identity (start with 100, increment by -1));0 rows inserted/updated/deletedij> create table tab3 (i int, a1 int generated always as identity (start with 100, increment by 2));0 rows inserted/updated/deletedij> create table tab4 (i int, a2 smallint generated always as identity (start with 100, increment by -2));0 rows inserted/updated/deletedij> insert into APP.tab1 (i) values (1);1 row inserted/updated/deletedij> insert into APP.tab2 (i) values (1);1 row inserted/updated/deletedij> insert into APP.tab3 (i) values (1);1 row inserted/updated/deletedij> insert into APP.tab4 (i) values (1);1 row inserted/updated/deletedij> insert into tab1 (i) values (1);1 row inserted/updated/deletedij> insert into tab1 (i) values (2);1 row inserted/updated/deletedij> insert into tab2 (i) values (1);1 row inserted/updated/deletedij> insert into tab2 (i) values (2);1 row inserted/updated/deletedij> insert into tab3 (i) values (1);1 row inserted/updated/deletedij> insert into tab3 (i) values (2);1 row inserted/updated/deletedij> insert into tab4 (i) values (1);1 row inserted/updated/deletedij> insert into tab4 (i) values (2);1 row inserted/updated/deletedij> select a.i, a1, a2 from app.tab1 a join app.tab2 b on a.i = b.i;I |A1 |A2 ------------------------------1 |-1 |1 ij> select a.i, a1, a2 from app.tab3 a join app.tab4 b on a.i = b.i;I |A1 |A2 --------------------------------------------1 |0 |0 ij> select a.i, a1, a2 from tab1 a join tab2 b on a.i = b.i;I |A1 |A2 --------------------------------------------1 |100 |100 2 |101 |99 ij> select a1, a2, a.i from tab3 a join tab4 b on a.i = b.i;A1 |A2 |I ------------------------------100 |100 |1 102 |98 |2 ij> values IDENTITY_VAL_LOCAL();1 -------------------------------98 ij> set schema app;0 rows inserted/updated/deletedij> drop table bpp.tab1;0 rows inserted/updated/deletedij> drop table bpp.tab2;0 rows inserted/updated/deletedij> drop table bpp.tab3;0 rows inserted/updated/deletedij> drop table bpp.tab4;0 rows inserted/updated/deletedij> drop schema bpp restrict;0 rows inserted/updated/deletedij> drop table tab1;0 rows inserted/updated/deletedij> drop table tab2;0 rows inserted/updated/deletedij> drop table tab3;0 rows inserted/updated/deletedij> drop table tab4;0 rows inserted/updated/deletedij> -- trigger, -- insert into t2-- ==> fires trigger which inserts into t1.-- create table tab1 (s1 int generated always as identity, lvl int);0 rows inserted/updated/deletedij> create table tab3 (c1 int);0 rows inserted/updated/deletedij> create trigger tab1_after1 after insert on tab3 referencing new as newrow for each row mode db2sql insert into tab1 (lvl) values 1,2,3;0 rows inserted/updated/deletedij> insert into tab3 values null;1 row inserted/updated/deletedij> select * from tab1;S1 |LVL -----------------------1 |1 2 |2 3 |3 ij> select b.tablename, a.autoincrementvalue, a.autoincrementstart, a.autoincrementinc from sys.syscolumns a, sys.systables b where a.referenceid=b.tableid and a.columnname ='S1' and b.tablename = 'TAB1';TABLENAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TAB1 |4 |1 |1 ij> create table tab2 (lvl int, s1 bigint generated always as identity);0 rows inserted/updated/deletedij> create trigger tab1_after2 after insert on tab3 referencing new as newrow for each row mode db2sql insert into tab2 (lvl) values 1,2,3;0 rows inserted/updated/deletedij> insert into tab3 values null;1 row inserted/updated/deletedij> select * from tab2;LVL |S1 --------------------------------1 |1 2 |2 3 |3 ij> select b.tablename, a.autoincrementvalue, a.autoincrementstart, a.autoincrementinc from sys.syscolumns a, sys.systables b where a.referenceid=b.tableid and a.columnname ='S1' and b.tablename = 'TAB2';TABLENAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TAB2 |4 |1 |1 ij> -- clean updrop trigger tab1_after1;0 rows inserted/updated/deletedij> drop trigger tab1_after2;0 rows inserted/updated/deletedij> drop table tab1;0 rows inserted/updated/deletedij> drop table tab2;0 rows inserted/updated/deletedij> drop table tab3;0 rows inserted/updated/deletedij> -- some more variations of lastAutoincrementValue....-- make sure we don't lose values from previous inserts.create table t1 (x int, s1 int generated always as identity);0 rows inserted/updated/deletedij> create table t2 (x smallint, s2 int generated always as identity (start with 0));0 rows inserted/updated/deletedij> insert into t1 (x) values (1);1 row inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------1 ij> insert into t1 (x) values (2);1 row inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------2 ij> insert into t2 (x) values (1);1 row inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------0 ij> -- alter table tests.drop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> create table t1 (s1 int generated always as identity);0 rows inserted/updated/deletedij> alter table t1 add column x int;0 rows inserted/updated/deletedij> insert into t1 (x) values (1),(2),(3),(4),(5);5 rows inserted/updated/deletedij> create table t2 (s2 int generated always as identity (start with 2));0 rows inserted/updated/deletedij> alter table t2 add column x int;0 rows inserted/updated/deletedij> insert into t2 (x) values (1),(2),(3),(4),(5);5 rows inserted/updated/deletedij> create table t3 (s0 int generated always as identity (start with 0));0 rows inserted/updated/deletedij> alter table t3 add column x int;0 rows inserted/updated/deletedij> insert into t3 (x) values (1),(2),(3),(4),(5);5 rows inserted/updated/deletedij> select a.x, s1, s2, s0 from t1 a join t2 b on a.x = b.x join t3 c on a.x = c.x;X |S1 |S2 |S0 -----------------------------------------------1 |1 |2 |0 2 |2 |3 |1 3 |3 |4 |2 4 |4 |5 |3 5 |5 |6 |4 ij> values IDENTITY_VAL_LOCAL();1 -------------------------------0 ij> -- test some more generated column specscreate table trigtest (s1 smallint generated always as identity, lvl int);0 rows inserted/updated/deletedij> insert into trigtest (lvl) values (0);1 row inserted/updated/deletedij> insert into trigtest (lvl) values (1),(2);2 rows inserted/updated/deletedij> insert into trigtest (lvl) values (3),(4);2 rows inserted/updated/deletedij> insert into trigtest (lvl) values (5),(6);2 rows inserted/updated/deletedij> insert into trigtest (lvl) values (7),(8);2 rows inserted/updated/deletedij> select * from trigtest;S1 |LVL ------------------1 |0 2 |1 3 |2 4 |3 5 |4 6 |5 7 |6 8 |7 9 |8 ij> drop table trigtest;0 rows inserted/updated/deletedij> select count(*) from t1;1 -----------5 ij> select a.x, s1, s2, s0 from t1 a join t2 b on a.x = b.x join t3 c on a.x = c.x;X |S1 |S2 |S0 -----------------------------------------------1 |1 |2 |0 2 |2 |3 |1 3 |3 |4 |2 4 |4 |5 |3 5 |5 |6 |4 ij> delete from t1;5 rows inserted/updated/deletedij> delete from t2;5 rows inserted/updated/deletedij> delete from t3;5 rows inserted/updated/deletedij> insert into t1 (x) values (1),(2),(3),(4),(5);5 rows inserted/updated/deletedij> insert into t2 (x) values (1),(2),(3),(4),(5);5 rows inserted/updated/deletedij> insert into t3 (x) values (1),(2),(3),(4),(5);5 rows inserted/updated/deletedij> -- should have started from after the values in t1 due to alter.select a.x, s1, s2, s0 from t1 a join t2 b on a.x = b.x join t3 c on a.x = c.x;X |S1 |S2 |S0 -----------------------------------------------⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -