db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Derby replication system - Need help
Date Mon, 24 Mar 2014 13:54:13 GMT
Hi George,

Sounds like you're working on an interesting problem. First a general 
comment, then some detailed advice inline.

I'm not sure where you are in your design right now. At one point it 
sounded as though you were going to collect changes via triggers, which 
would call stored procedures. The stored procedures, in turn, would 
populate a queue. And the queue would be implemented by some non-Derby 
technology. This approach will have some interesting transactional 
behavior. What happens if the original update fails and is rolled back? 
If the failure happens after the trigger fires, then the stored 
procedure will still be executed and a change will be queued up and 
ultimately applied to your replicant. Now your original table and its 
replicant are no longer in agreement. The solution suggested by John 
English will not have this problem. In John's solution, the queue is 
essentially implemented by a Derby table. The trigger will write to the 
queue in the same nested transaction as the original change; if the 
original change rolls back, the corresponding queue entry will roll back 
too.

More comments inline...

On 3/23/14 2: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.
> 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)
Note that an update trigger can be defined with a column list. The 
trigger only fires if a change is made to one of the columns in that 
list. The following script shows how to record only the changed columns.

Hope this helps,
-Rick

connect 'jdbc:derby:memory:db;create=true';

create table original
(
     primaryKey  int generated always as identity primary key,
     intCol      int,
     varcharCol  varchar( 20 )
);

create table changeCollector
(
     changeID    int generated always as identity primary key,
     changeType    varchar( 10 ),
     changedColumnName         varchar( 15 ),
     originalPrimaryKey        int,
     originalIntCol            int,
     originalVarcharCol        varchar( 20 )
);

create trigger insertTrigger
after insert
on original
referencing new as new
for each row
insert into changeCollector values ( default, 'insert', 'all', 
new.primaryKey, new.intCol, new.varcharCol );

create trigger updateIntCol
after update of intCol
on original
referencing new as new
for each row
insert into changeCollector values ( default, 'update', 'intCol', 
new.primaryKey, new.intCol, null );

create trigger updateVarcharCol
after update of varcharCol
on original
referencing new as new
for each row
insert into changeCollector values ( default, 'update', 'varcharCol', 
new.primaryKey, null, new.varcharCol );

insert into original( intCol, varcharCol ) values ( 1, 'one' ), ( 2, 
'two' ), ( 3, 'three' );

select * from original order by primaryKey;
select * from changeCollector order by changeID;

update original set intCol = 10 where intCol = 1;
update original set varcharCol = 'twenty' where intCol = 2;

select * from original order by primaryKey;
select * from changeCollector order by changeID;

> 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.
>
> Regards,
> George
>
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Re-Derby-replication-system-Need-help-tp138003p138203.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>


Mime
View raw message