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
QUEUE.
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
triggers)
(*) 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
... FOR EACH ROW CALL MYPROC(OLD.x, NEW.x, OLD.y, NEW.y,...)
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
... FOR EACH ROW INSERT INTO QUEUE_T VALUES (NEW.x, OLD.x, NEW.y, NEW.y,...)
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.
--
Regards,
Dyre
