It's similar to what you're doing, but doing it at the database level..

The reasons you would prefer it at the DB level would be so that if these updates came from various locations in the code, the update would still be supportable and cascade the values.. There are rarely cases where you know with 100% certainty for the lifecycle of the application that specific updates will only happen in one location, so it's usually best to enforce things like this at the DB level.. Even if you think you know 100% that this is the only place it will happen, you are probably wrong :-).. It's really a question of whether it's okay to do in the Java code, or to do at the DB level..

I haven't done something like this in a long while, and I'm not certain if Derby triggers support it, but something like this conceptually:

ON BEFORE UPDATE ON domaintablename OF columnname FOR EACH ROW
drop/disable constraint(s) to allow the update;


ON AFTER UPDATE ON domaintablename OF columnname FOR EACH ROW
cascade update to referencing table(s) based on disabled constraints;
recreate/enable constraint(s) ensuring db integrity;

I admit the above isn't perfect, but I've done it with Oracle IIRC (I haven't done a lot of RDBMS development in 4-5 years).. But conceptually it would be the same even in Java - to perform the update I suspect you will need to temporarily disable/remove the constraints then reapply them.. 

It should be done within a transaction that can be rolled back.. 

You could try to batch all the above updates in Java in a transaction and see if committing them all together works, but I suspect it will throw from the DB.. 


On Mar 16, 2010, at 8:59:15 AM, Gabriele Kahlout wrote:

what is the best approach? What you describe seems a DEFFERABLE
constraint/transaction (checking).
"|Manually cascade the updates| , that's what I'm doing. Not?

2010/3/16, Ronald Rudy <ronchalant@gmail.com>:
I haven't worked much with Derby triggers but that seems like a logical
place to start.  You may run into situations where you want to (if
possible?) disable the constraints temporarily while you manually cascade
the updates, and put them back into place once the updates have been
completed, but I think that's probably the best approach.

-Ron


On Mar 16, 2010, at 3:26:29 AM, Gabriele Kahlout wrote:

Okay, and how do you recommend I work around it?
I should retrieve the effected tuples (the referencing) and store
their values (onto the stack/heap) and then delete the records (since
the referencing fields are primary keys), and then finally updated the
referred to tuples, and then re-insert the data into the referencing
table, with the appropriate modification.
Unfortunately violation checking is done after each statement, and may
not be deffered.

Would this be permissible?

final Connection con = getConnection(false);
final Statement st = con.createStatement();
                      final ResultSet rs =
st.executeQuery(SqlWrapper.select(objColumn, refTable, wColumn,
currentSpelling));


                      final Statement st1 = con.createStatement();
                      st1.executeUpdate(SqlWrapper.delete(refTable,
wColumn, currentSpelling));
st1.executeUpdate(SqlWrapper.update(expTable, expColumn,
newSpelling, currentSpelling));


                      while (rs.next()){
                          rs.absolute(SqlWrapper.index);
                          rs.updateString(SqlWrapper.index, newSpelling);
                          rs.updateRow();
                      }
                      st1.close();
                      rs.close();
                      st.close();

con.commit();
con.close();

2010/3/16, Sylvain Leroux <sylvain@chicoree.fr>:
Hi,

Just passing by:
http://issues.apache.org/jira/browse/DERBY-735


Regards,
- Sylvain

Gabriele Kahlout a écrit :
ya. Also SQLite does.
I tried to look where to submit feature requests (not bug reports),
but failed. Any links?

2010/3/16, Rick Genter <rgenter@silverlink.com>:
On 3/15/10 6:46 PM, "Ronald Rudy" <ronchalant@gmail.com> wrote:

I believe you want to look at this:
http://db.apache.org/derby/manuals/reference/sqlj32.html

<http://db.apache.org/derby/manuals/reference/sqlj32.html>
Specifically
the
referential ON UPDATE in constraints.  The only "ON UPDATE" actions
that
are
allowed are "NO ACTION" and "RESTRICT"; Derby does not appear to
support
cascading updates.  You can log a feature request, I believe there
might
already be one for it based on this thread:
http://old.nabble.com/ON-UPDATE-CASCADE-in-derby-td1633870.html

Incidentally I do believe that MySQL supports on update cascade with
its
INNODB engine, and I do think MS SQL does as well
(http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx)

Thank you for the pointer. I learn something new every day ;-).
--
Rick Genter
Principal Software Engineer
Silverlink Communications
rgenter@silverlink.com
www.silverlink.com
Office (781) 583-7145
Mobile (408) 398-7006

This e-mail, including attachments, may include confidential and/or
proprietary information, and may only be used by the person or entity
to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify
the
sender by replying to this message and delete this e-mail immediately






--
sylvain@chicoree.fr
http://www.chicoree.fr





--
Regards,
K. Gabriele

--- unchanged since 25/1/10 ---
P.S. Unless a notification (LON), please reply either with an answer
OR with " ACK" appended to this subject within 48 hours. Otherwise, I
might resend.
In(LON, this) ∨ In(48h, TimeNow) ∨ ∃x. In(x, MyInbox) ∧ IsAnswerTo(x,
this) ∨ (In(subject(this), subject(x)) ∧ In(ACK, subject(x)) ∧
¬IsAnswerTo(x,this)) ⇒ ¬IResend(this).

Also note that correspondence may be received only from specified a
priori senders, or if the subject of this email ends with a code, eg.
-LICHT01X, then also from senders whose reply contains it.
∀x. In(x, MyInbox) ⇒ In(senderAddress(x), MySafeSenderList) ∨ (∃y.
In(y, subject(this) ) ∧ In(y,x) ∧ isCodeLike(y, -LICHT01X) ).




--
Regards,
K. Gabriele

--- unchanged since 25/1/10 ---
P.S. Unless a notification (LON), please reply either with an answer
OR with " ACK" appended to this subject within 48 hours. Otherwise, I
might resend.
In(LON, this) ∨ In(48h, TimeNow) ∨ ∃x. In(x, MyInbox) ∧ IsAnswerTo(x,
this) ∨ (In(subject(this), subject(x)) ∧ In(ACK, subject(x)) ∧
¬IsAnswerTo(x,this)) ⇒ ¬IResend(this).

Also note that correspondence may be received only from specified a
priori senders, or if the subject of this email ends with a code, eg.
-LICHT01X, then also from senders whose reply contains it.
∀x. In(x, MyInbox) ⇒ In(senderAddress(x), MySafeSenderList) ∨ (∃y.
In(y, subject(this) ) ∧ In(y,x) ∧ isCodeLike(y, -LICHT01X) ).