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;
For further information on using SET SUBSCRIPTION
and its options, please refer to Set Subscription
SQL Extensions for Change Views
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.