Creating Subscriptions to Change Views
Go Up to Change Views
To establish interest in observing changed data on a set of tables beyond the natural boundary of a database connection, a subscription must be created on a list of tables (base tables or views):
Syntax for CREATE SUBSCRIPTION
CREATE SUBSCRIPTION <subscription_name> ON<
<table_name>[(column_name_comma_list)][FOR ROW (CHANGE | {INSERT, UPDATE, DELETE})]
[, <table_name>[(column_name_comma_list)][FOR ROW (CHANGE | {INSERT, UPDATE, DELETE})] ...]
[DESCRIPTION user-description];
- The
FOR ROW
clause describes what types of row modifications will cause column-level changes to be tracked for the subscription. - If the
FOR ROW
clause is omitted, then the behavior defaults toFOR ROW (CHANGE)
. TheCHANGE
option tracksINSERT
andUPDATE
data changing operations and returns a row as soon as any tracked columns changes value. - When
INSERT
,UPDATE
, andDELETE
are specified, then change status on every tracked column is determined. This is called "deep record introspection" and theCHANGE
option is called "shallow record introspection". It is expected that Change Views performance is faster with theCHANGE
option, but that theINSERT
,UPDATE
, andDELETE
combinations are more complete.DELETE
is not tracked by default, unless explicitly specified.
- Deep record introspection is required when it is necessary to account for every tracked column and to know exactly which operation(s) caused a column's value change.
- Shallow record introspection is for the use case where it is only necessary to know that one or more columns changed and not how every tracked column value changed or if they even changed value at all; it indicates that the row changed somehow with respect to the subscription.
- If a table alone is specified then all columns of the table are tracked.
- If only a subset of columns is desired to be tracked, then an optional list of columns can be specified by the subscription. For example,
- The
Sample for CREATE SUBSCRIPTION
CREATE SUBSCRIPTION sub_employee_changes ON EMPLOYEE (EMP_NO, DEPT_NO, SALARY) DESCRIPTION 'Subscribe to changes in EMPLOYEE table';
CREATE SUBSCRIPTION sub_customer_deletes ON CUSTOMER FOR ROW (DELETE) DESCRIPTION 'Subscribe to deletes in CUSTOMER table';
CREATE SUBSCRIPTION sub_various_changes
ON EMPLOYEE FOR ROW (INSERT, UPDATE, DELETE),
CUSTOMER FOR ROW (INSERT, UPDATE, DELETE),
SALES FOR ROW (UPDATE),
DEPARTMENT (LOCATION) FOR ROW (UPDATE)
DESCRIPTION 'Subscribe to various changes on multiple tables';
An optional list of columns is specified for the EMPLOYEE table so that only changes on those columns are tracked. Since no FOR ROW clause is specified for EMPLOYEE table, the default for FOR ROW is the CHANGE option, which causes all insert, update, and delete changes are tracked by the subscription and that any change will return the row. The CUSTOMER table clause specifies that only row deletions are tracked.
DROP SUBSCRIPTION
- Note: This new function was introduced with InterBase XE7 Update 1.
To eliminate interest in observing a set of change views, a subscription must be dropped.
- If
RESTRICT
is specified, then a check of existing subscribers is performed. If there are subscribers, then an error is returned without dropping the subscription. - If
CASCADE
is specified, then all subscribers of this subscription are also dropped. - If neither
RESTRICT
norCASCADE
is specified, thenRESTRICT
is assumed.
- If
Syntax for DROP SUBSCRIPTION
DROP SUBSCRIPTION <subscription_name> [RESTRICT | CASCADE];
Grant Subscribe
A user is then granted SUBSCRIBE privilege to subscribe to the subscription in order to track changes on the listed tables:
Syntax for GRANT SUBSCRIBE
GRANT SUBSCRIBE ON SUBSCRIPTION <subscription_name> TO <user_name>; REVOKE SUBSCRIBE ON SUBSCRIPTION <subscription_name> FROM <user_name>;
Set Subscription
To set a subscription as active, an application issues a SET SUBSCRIPTION statement. The SET SUBSCRIPTION
statement allows multiple subscriptions to be activated and includes an AT clause to denote a destination or device name as a recipient of subscribed changes. The subscriber user name is implied by the user identity of the database connection.
The notion of multiple subscriptions against the same schema object for a user, via the AT clause, is motivated by two observations:
- First, each subscription for a user might connote a separate device among many that have a disconnected interest in a change set that is queried independently at different times for different purposes.
- Second, some multiuser applications use pooled database connections under the umbrella of a single user name (e.g. CRM_User or even SYSDBA). In these cases, an alternate identifier must be provided to distinguish which subscription should be used to query a change set. That additional identifier can be loosely thought of as a destination or a "device name".
The SET SUBSCRIPTION
statement activates/deactivates a subscription for a subscriber and binds/unbinds that subscription to the transaction that executed the command. All subscribed tables in a subscription behave as change views for table references in user-level queries when executed by a transaction with bound subscriptions. At transaction termination, the subscription is necessarily unbound, so it is necessary to issue a SET SUBSCRIPTION in subsequent transactions if a change view is still wanted.
At transaction commit, the subscriber transaction state is updated to allow the subscriber to see changes that occur after the commit, but not the former changes. A transaction rollback keeps the subscriber transaction state unchanged, so the change view still sees the former changes but cannot see newer changes. If a transaction does not read any subscribed tables from a bound subscription then the subscriber transaction context for that subscription is not updated.
SET SUBSCRIPTION; syntax and example
SET SUBSCRIPTION [<subscription_name> [, <subscription_name> ...]] [AT <destination>] {ACTIVE | INACTIVE}; SET SUBSCRIPTION sub_employee_changes, sub_customer_deletes AT 'smartphone_123' ACTIVE; SELECT EMP_NO, DEPT_NO, SALARY FROM EMPLOYEE; SELECT * FROM CUSTOMER; COMMIT or COMMIT RETAIN;
This example activates two subscription and returns changed data sets from the subscribed tables.
- The
COMMIT
updates all subscriptions for schema objects referenced during the transaction to set the last observed timestamp and transaction context. - The
COMMIT RETAIN
does not change the last observed state and maintains the current snapshot as always. The subscription is unbound for the transaction at commit, which makes any subsequent queries against subscribed schema objects return normal data sets, without regard to changed data status. Any number of subscriptions can be activated simultaneously by a transaction.
- The