CREATE SUBSCRIPTION

From InterBase

Go Up to Statement and Function Reference (Language Reference Guide)


Establishes 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).

CREATE SUBSCRIPTION <subscription_name> ON
<table>[(column_comma-list)]:[FOR ROW ({INSERT, UPDATE, DELETE})
], <table>[(column-comma_list)][FOR ROW ({INSERT, UPDATE, DELETE})] ...]
[DESCRIPTION user-description];
Argument Description

FOR ROW

Determines what types of row modification causes column-level changes.

<table>

If a table is specified, all table columns are tracked.

column_comma-list

Specifies a subset of columns to be tracked.

user-description

Description: The FOR clause tailors what types of row modifications causes column-level changes to be tracked for the subscription. If the FOR clause is omitted then all data changing row operations cause column data to be tracked for 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.

An optional list of columns is specified for the "Employees" table so that only changes on those columns are tracked. Since no FOR clause is specified for "Employees" the default of FOR assumes that all insert, update, and delete changes are tracked by the subscription. The "Customer" table clause specifies that only row deletions are tracked.

  • If you no longer want to observe a set of changed views, the 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 nor CASCADE is specified then RESTRICT is assumed.

Example: If only a subset of columns is desired to be tracked, then an optional list of columns can be specified by the subscription.

CREATE SUBSCRIPTION "Subscribed_Changes" ON "Employees" (NAME, DEPARTMENT, SALARY), "Customers" FOR ROW (DELETE).

To create your subscriptions (the first line shows new employees, the second shows customer records that were deleted).

CREATE SUBSCRIPTION "Subscribed_Inserts" ON "Employees" (FULL_NAME, DEP_NO, SALARY) FOR ROW (INSERT)
CREATE SUBSCRIPTION Customer_Deletes" ON "Customer" FOR ROW (DELETE)

See Also

Advance To: