I think I start to understand why this restriction is imposed on triggers.
getGeneratedKeys() in H2 can only return a single key instead of many
(as the plural in the method name would suggest).
Further every insert statement sets the scope indemtity to zero even if
it does not a generate a new id.
So without this hack every insert in a trigger would loose the original
generated key.
I would propose the following changes:
1. scope identity should be a list of generated keys instead of a single
key (List<Value>)
2. Only inserts that actually do generate an id would add to that list
Problem: triggers can insert into all kinds of tables so that the values
could be of different types
and thus the could not be crammed into one ResultSet
Solution: I think BIGINTs are the only generated values in H2 so the
scope Identity could be of type: List<ValueLong>
Then we could allow triggers adding to that list of generated keys.
One hacky way to prevent inserts that do not generate a key to set the
scope identity would be
to simply change the method
public void setScopeIdentity(Value scopeIdentity) {
this.scopeIdentity = scopeIdentity;
}
to
public void setScopeIdentity(Value scopeIdentity) {
if ( ((ValueLong) scopeIdentity).getLong() > 0) this.scopeIdentity
= scopeIdentity;
}
and change the setLastIdentity method accordingly.
What is the meaning of lastIdentity by the way?
And are there cases where H2 generates something else than a Long?
- Rami
5.10.2010 13:59, Rami Ojares kirjoitti:
Hi,
I have implemented a distributed key with triggers.
That is a key that guards uniqueness over many columns in many tables.
It can also generate a unique key for insert statements when they
don't provide an explicit id.
To be compatible with jdbc PreparedStatement's getGeneratedKeys() I
tried to set the generated
id into session:
JdbcConnection jconn = (JdbcConnection);
Session session = (Session) jconn.getSession();
session.setLastIdentity(ValueLong.get(generatedId));
But I realized that this is being explicitly prevented by TriggerObject
that goes to extra length to ensure that trigger can not set that value.
In it's finalize block it set's the old scope identity after trigger.
} finally {
session.setScopeIdentity(identity);
...
}
Is there a reason for this?
I am wondering because I think my need seems reasonable.
- Rami
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
