Creating Notification Directives on Tables

This topic shows examples of notification directives on tables.

In these examples, the current user creates a notification directive in the SCOTT schema on the SCOTT.EMP table. The notification table and notification view are created in the directive owner’s schema.

Run each CREATE DIRECTIVE example separately, or drop the previous directive using DROP DIRECTIVE before running the next example.

Create a basic table notification directive

CREATE DIRECTIVE scott.emp_ndrtv FOR scott.emp
  NOTIFY
  USING NOTIFICATION TABLE emp_ntable
    NOTIFICATION VIEW emp_nview;

This directive tracks inserts, updates, and deletes on SCOTT.EMP. It logs all columns of the base table, because LOGGED COLUMNS is omitted. The directive is enabled by default and is not purged automatically.

Track only inserts and updates

CREATE DIRECTIVE scott.emp_ndrtv FOR scott.emp
  NOTIFY ON INSERT UPDATE
  USING NOTIFICATION TABLE emp_ntable
    NOTIFICATION VIEW emp_nview
  WITH KEY (empno);

This directive tracks inserts and updates. It does not track deletes.

Log selected columns

CREATE DIRECTIVE scott.emp_ndrtv FOR scott.emp
  NOTIFY
  USING NOTIFICATION TABLE emp_ntable
    NOTIFICATION VIEW emp_nview
  WITH LOGGED COLUMNS (empno, ename, sal);

This directive logs only the empno, ename, and sal columns.

Include old values

CREATE DIRECTIVE scott.emp_ndrtv FOR scott.emp
  NOTIFY
  USING NOTIFICATION TABLE emp_ntable
    NOTIFICATION VIEW emp_nview
  WITH LOGGED COLUMNS (empno, ename, sal)
    KEY (empno)
  INCLUDING OLD VALUES;

This directive logs old values for the logged columns.

Log a SYS_CONTEXT attribute

CREATE DIRECTIVE scott.emp_ndrtv FOR scott.emp
  NOTIFY
  USING NOTIFICATION TABLE emp_ntable
    NOTIFICATION VIEW emp_nview
  WITH LOGGED COLUMNS (empno, ename, sal)
    KEY (empno)
  CONTEXT NAMESPACE userenv
    ATTRIBUTES current_user(10);

This directive logs the CURRENT_USER attribute from the USERENV namespace.

Create a disabled directive

CREATE DIRECTIVE scott.emp_ndrtv FOR scott.emp
  NOTIFY
  USING NOTIFICATION TABLE emp_ntable
    NOTIFICATION VIEW emp_nview
  WITH LOGGED COLUMNS (empno, ename, sal)
    KEY (empno)
  DISABLE;

This directive is created but does not track changes until you enable it.

Specify retention

CREATE DIRECTIVE scott.emp_ndrtv FOR scott.emp
  NOTIFY
  USING NOTIFICATION TABLE emp_ntable
    NOTIFICATION VIEW emp_nview
  WITH LOGGED COLUMNS (empno, ename, sal)
    KEY (empno)
  ENABLE
  RETENTION 1 DAYS;

Rows in the notification table become eligible for purge after one day.

Create a comprehensive table notification directive

CREATE DIRECTIVE scott.emp_ndrtv FOR scott.emp
  NOTIFY ON INSERT UPDATE DELETE
  USING NOTIFICATION TABLE emp_ntable
    TABLESPACE tbsp1
    NOTIFICATION VIEW emp_nview
  WITH LOGGED COLUMNS (empno, ename, sal)
    KEY (empno)
  INCLUDING OLD VALUES
  CONTEXT NAMESPACE userenv
    ATTRIBUTES current_user(10)
  ENABLE
  RETENTION 1 DAYS;

This directive tracks all DML operations on SCOTT.EMP, logs the specified columns and old values, logs CURRENT_USER, and makes notification history eligible for purge after one day.