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.
