If you have ever spent time planning how to identify what data has changed, then change views is about to simplify the way you develop for ever!
If you haven’t already read Part 1 of InterBase Change Views this provides a high level overview of the technology.
Steps for creating a Change View
Rather than having to create and manage triggers to add records into log tables or planning additional date fields into your metadata (and hoping date & time changes don’t effect you) Change View are easily added to a project once the simplified architecture is complete.
There are two essential steps in creating a successful change view:
- Defining the purpose of the Change View and the data you want to track
- Defining who can subscribe to the change view
1) Creating a change view
Lets start with a simple example. We want to keep our sales fulfilment application up-to-date with the latest stock and suppliers information. This data is stored in two tables, INVENTORY and SUPPLIERS.
Example 1 – Multiple Tables
CREATE SUBSCRIPTION sub_stock ON INVENTORY FOR ROW (INSERT, UPDATE, DELETE), SUPPLIERS FOR ROW (INSERT, UPDATE, DELETE) DESCRIPTION 'Track stock and supplier changes';
Subscriptions are created with the CREATE SUBSCRIPTION statement followed by a subscription name that you will need to use in SQL later on.
The simple example tracks all columns in the tables INVENTORY and SUPPLIERS for Inserts, Updates and Deletes.
The description allows easy identification of the subscriptions purpose later on.
Example 2 – Specifying Columns
We may have a requirement to track product name changes for our application. We can do this also with a change view.
CREATE SUBSCRIPTION
sub_stockname
ON
INVENTORY(ITEM_NAME) FOR
ROW (INSERT, UPDATE, DELETE)
DESCRIPTION 'Track stock name changes';
Example 2 is more granular than example 1 and tracks only a single column ITEM_NAME in the INVENTORY table. This is done by providing a comma separated list of field names for the table that you want to track.
Example 3 – Tracking Deletes
We may also want to track deletions from a specific table, lets say ORDERS. A Change Views just tracking DELETE activity on a table will provide a way to recover data if it is deleted
CREATE SUBSCRIPTION
sub_orderDeletes
ON
ORDERS FOR
ROW (DELETE)
DESCRIPTION 'Track Order Deletions';
Using a Change View to track deletes is a powerful feature as you can completely recover the record at its state when it was deleted.
2) Defining who can use a change view / subscription
Once a subscription is created, you need to grant SUBSCRIBE access to the subscription. To do this use the standard GRANT syntax. e.g. (for user SYSDBA)
GRANT SUBSCRIBE ON SUBSCRIPTION sub_stock TO SYSDBA;
InterBase Documentation
For more on Change Views visit the InterBase DocWiki
In my next blog, I’ll look at using a change view
