Replicating database using triggers

Suppose you have a table on any RDBMS database:

table123:  column uid and column addr

You can create another table to capture insert, update, and delete operations on table123:

create table table123_trigger_table

(

ts datetime primary key,

uid int,

addr varchar(64),

action: char(1)

);

 

Then you can create three triggers to capture the changes in table123:

DELIMITER $$
CREATE TRIGGER after_table123_insert AFTER INSERT ON table123 FOR EACH ROW
BEGIN
INSERT INTO table123_trigger_table
SET action = ‘I’,
uid = NEW.uid,
addr = NEW.addr,
ts = NOW();
END$$
DELIMITER ;

 

DELIMITER $$
CREATE TRIGGER after_table123_update AFTER UPDATE ON table123 FOR EACH ROW
BEGIN
INSERT INTO table123_trigger_table
SET action = ‘U’,
uid = NEW.uid,
addr = NEW.addr,
ts = NOW();
END$$
DELIMITER ;

 

DELIMITER $$
CREATE TRIGGER after_table123_delete AFTER DELETE ON table123 FOR EACH ROW
BEGIN
INSERT INTO table123_trigger_table
SET action = ‘D’,
uid = OLD.uid,
addr = OLD.addr,
ts = NOW();
END$$
DELIMITER ;

 

After the 3 triggers are created, you can write a Java program to use JDBC and pull the records into target database and table. The ‘ts’ column in the trigger table is a timestamp and is primary key, which can be used to track the change time. The trigger table can be cleaned up periodically.

 

 

Advertisements