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 <[email protected]>:
>> 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 <[email protected]>:
>>>> 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 <[email protected]>:
>>>>>> On 3/15/10 6:46 PM, "Ronald Rudy" <[email protected]> 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
>>>>>> [email protected]
>>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> [email protected]
>>>> 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) ).
