Capture all change events into an event table

In addition to replicating data changes from source to target you may also wish to populate a table on the target to monitor all events.  In this blog I will show you how to create a simple load filter that will record all changes captured by SymmetricDS into a simple audit table.

This table will hold just four columns to record the change.

• Table Name – The table the change occurred on.
• Event Type – Single character to represent Inserts(I), Updates(U), or Deletes(D).
• Primary Key Values – The value of the primary key for the row that was changed (comma separated if more than one).
• Primary Key Names – The name of the primary key column at the time of the change (comma separated if more than one).

First lets create the audit table on the target node by running the following SQL statement.

create table audit (
   table_name varchar(50), 
   event varchar(1), 
   pk varchar(50), 
   pk_columns varchar(250)
);

In the SymmetricDS Pro web console go to the Configure->Load Filters screen

Create a new Load Filter of type BSH with the target table set to * so that all tables setup for replication will be filtered.  You will also need to select the appropriate group link, remember the load filter will fire on the target node.

After saving the filter select the edit scripts button.

Select the “After Write Script” from the drop so that the script we will create will fire after each data event is processed.

Provide the following beanshell code to the text editor and hit save.

String tableName = table.getName();
String eventType = data.getDataEventType().getCode();
String[] pkData = data.getPkData(table);
String[] pkCol = table.getPrimaryKeyColumnNames();
String pkDataAsString = "";
String pkColAsString = "";

for (int i=0; i < pkData.length; i++) {
    if (pkDataAsString.length() > 0) {
        pkDataAsString = pkDataAsString + ",";
        pkColAsString = pkColAsString + ",";
    }
    pkDataAsString = pkDataAsString + pkData[i];
    pkColAsString = pkColAsString + pkCol[i];
}
String sql = "insert into change_data_events (table_name, event, pk, pk_columns) 
   values (?,?,?,?)";
engine.getSqlTemplate().update(sql, 
   new Object[] { tableName, eventType, pkDataAsString, pkColAsString});

Save your script and your all set to start recording change data events into your new table.