Change Views SQL Language Support

Go Up to Change Views

The following example shows a retooling of the ISQL command-line utility that supports change views.

To display a list of subscriptions defined in the database, you can execute the SHOW SUBSCRIPTIONS command. To display details for a particular subscription, you can execute SHOW SUBSCRIPTION <name>

ISQL SHOW SUBSCRIPTION

SHOW SUBSCRIPTIONS;
Subscription Name
===================================================================
SUB_CUSTOMER_DELETES
SUB_EMPLOYEE_CHANGES
SUB_VARIOUS_CHANGES
 
SHOW SUBSCRIPTION sub_employee_changes;
Subscription name: SUB_EMPLOYEE_CHANGES
Owner: SYSDBA
Description: Subscribe to changes in EMPLOYEE table
        EMPLOYEE (SALARY, DEPT_NO, EMP_NO)
 
SHOW SUBSCRIPTION sub_customer_deletes;
Subscription name: SUB_CUSTOMER_DELETES
Owner: SYSDBA
Description: Subscribe to deletes in CUSTOMER table
        CUSTOMER FOR ROW (DELETE)
 
SHOW SUBSCRIPTION sub_various_changes;
Subscription name: SUB_VARIOUS_CHANGES
Owner: SYSDBA
Description: Subscribe to various changes on multiple tables
        EMPLOYEE FOR ROW (INSERT, UPDATE, DELETE),
        CUSTOMER FOR ROW (INSERT, UPDATE, DELETE),
        SALES FOR ROW (UPDATE),
        DEPARTMENT (LOCATION) FOR ROW (UPDATE)

ISQL SET CHANGES Command

ISQL has a collection of SET statements that toggle a display set. The SET CHANGES display toggle alternates between showing the column data value with its change status as a subordinated annotation. The <change> column is a pseudo column that shows the type of DML statement that modified the value of a column. All of this change state is returned by the XSQLVAR.SQLIND member of the new XSQLDA structure.

Retrieving Change Views from ISQL

<Another user reassigns an existing employee to another department and gives another employee a raise>
 
SET SUBSCRIPTION sub_employee_changes ACTIVE;
SELECT EMP_NO, DEPT_NO, SALARY FROM EMPLOYEE; 
 
 EMP_NO     DEPT_NO     SALARY
   37         120        50000
   109        600        75000
 
SET CHANGES;
 
SELECT EMP_NO, DEPT_NO, SALARY FROM EMPLOYEE;
 EMP_NO   <change>  DEPT_NO    <change>       SALARY      <change>
 --------           ----------                -----------
 37       <same>    120        <update>       50000       <same>
 109      <same>    600        <same>         75000       <update>
 
COMMIT;

SQL Extensions for Change Views

Note: This function was introduced with InterBase XE7 Update 1.

InterBase SQL provides support for Change Views with the IS [NOT] {CHANGED | INSERTED | UPDATED | DELETED} clause as the following example illustrates:

Using IS [ NOT ] UPDATED in SELECT queries

SET SUBSCRIPTION sub_employee_changes ACTIVE;
SELECT EMP_NO, DEPT_NO, SALARY FROM EMPLOYEE WHERE SALARY IS UPDATED;
 EMP_NO     DEPT_NO           SALARY
 --------   ----------        ----------
 109        600               75000

We see that EMP_NO=37 employee's department reassignment is not returned since he received no compensation adjustment for a lateral move. The IS CHANGED clause will detect the modification of a column due to any kind of SQL operation.

NEXT:

• Metadata Support