$ java -Dij.exceptionTrace=true org.apache.derby.tools.ij
ij version 10.1
ij> connect 'jdbc:derby:c:/dellater/db1';
ij> create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int);
0 rows inserted/updated/deleted
ij> create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int);
0 rows inserted/updated/deleted
ij> create trigger t1tr1 after insert on t1 for each row mode db2sql insert into t2 (c22) values (1);
0 rows inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
NULL
1 row selected
ij> insert into t1 (c12) values (1);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
101
1 row selected
ij> select * from t1;
C11 |C12
-----------------------
101 |1
1 row selected
ij> select * from t2;
C21 |C22
-----------------------
201 |1
1 row selected
ij>
What does this return if the table has a trigger defined which inserts
into another table which also has an identity defined? Is it the value
from the statement's table or the one modified by the trigger?
I could not see where this is explictly defined in the docs. I have seen
this cause problems with applications using SQL Server databases and so
it is probably worth spelling out.
--
Jeremy
Daniel John Debrunner wrote:
> Mamta Satoor wrote:
>
>
>>The SELECT IDENTITY_VAL_LOCAL() FROM mytable1 will return the value that
>>got into generated for _any_ table with identity column using single row
>>insert with values clause in the current transaction.
>
>
> Except it doesn't behave like that, with respect to the *current
> transaction*. Derby's implementation returns the last identity value for
> a single row INSERT statement within the same connection.
> See the example below, and note auto commit is true.
>
> And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,
> that will just return the same value multiple times (once per row in the
> table) and the value will be the last identity value for a single row
> INSERT statement within the same connection.
>
> Dan.
>
> ij> connect 'jdbc:derby:foo;create=true';
> ij> create table t (id int generated always as identity, d int);
> 0 rows inserted/updated/deleted
> ij> insert into t(d) values(88);
> 1 row inserted/updated/deleted
> ij> values IDENTITY_VAL_LOCAL();
> 1
> -------------------------------
> 1
>
> 1 row selected
> ij> select * from t;
> ID |D
> -----------------------
> 1 |88
>
> 1 row selected
> ij> values IDENTITY_VAL_LOCAL();
> 1
> -------------------------------
> 1
>
> 1 row selected
>
>
>
>
