db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Boynes <jboy...@apache.org>
Subject Re: Atomicity of using IDENTITY_VAL_LOCAL()
Date Sat, 14 May 2005 01:17:31 GMT
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
> 
> 
> 
> 



Mime
View raw message