db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre Tjeldvoll <Dyre.Tjeldv...@oracle.com>
Subject Re: Derby replication system - Need help
Date Mon, 24 Mar 2014 10:33:01 GMT
On 03/23/2014 10:22 PM, spykee wrote:
> Hi,
>  From my application tables ( for each table I have a trigger for INSERT,
> UPDATE and DELETE operation), I will need two important information:
> a) the columns names which changed when an update SQL occured.
> b) the columns values that changed during an UPDATE SQL script.
>   The points a) + b) will be used to create a message to be sent on the
> But with Derby I encountered few "issues" .
> 1. Is not possible to fetch only the updated columns from an UPDATE trigger.

True. There is no diff-interface as such. You have to iterate across all 
columns and compare manually, I think.

But you can specify a correlation name for both the new and old row 
value(*) (e.g. NEW AS <NEW> OLD AS <OLD>) so that you don't have query 
the table for the old value (OLD AS <OLD> is obviously not available in 
insert triggers, and similarly NEW AS <NEW> is not available in delete 

(*) Assuming that we are talking about row-level triggers.

> 2. There is no  way of fetching the columns name from a trigger(That's, I
> want to know from an UPDATE trigger the updated columns names and to use
> this information on building the message for my replication system)

Actually, there is no way to pass the whole row (NEW or OLD) to a stored 
procedure or function, so you have to create separate procedures for the 
tables you create triggers on which take all the columns you are 
interested in as parameters. E.g. your trigger body becomes something like


> I thought I can avoid using a lot of Java code for these tasks.
> Using Java code for searching(filtering) only the updated columns names from
> a specific table, and their values will cause me a delay on my replication
> system, a drawback.
> First a trigger will fire, then a Java code ( stored procedure ) will be
> called ( the Java code will go back and query the db - I don't like this,
> why I can't solve  this problem using Derby functionality, triggers ? ),  a
> comparison will be made on the last 2 rows from the audit table, pick only
> the different values + the columns names, create a specific message with
> this information.
> There is no other ways of avoiding using Java code for these tasks ?
> Please advice me.

The java code in the sp is runs in the same jvm and is JITed just like 
any other code, so it is just as fast as the Derby code which executes a 
normal trigger action.

Assuming that the trigger firing has brought the relevant data into the 
page cache it should be reasonably fast to query the original table from 
within the sp also, at least if you use an index.

Another way is to have the trigger just dump the old and new values in a 
table. Then the trigger would simply do something like


you determine the order in which you insert the NEW and OLD values.
Then you could do all the logic and analysis in the sender thread which 
monitors QUEUE_T. You could add an insert trigger to the QUEUE_T which 
would call a store procedure which would wake up the sender.



View raw message