Creating Notification Directives on JSON-Relational Duality Views

This topic shows examples of notification directives on JSON-relational duality views.

The following examples use the car-racing JSON-relational duality view race_dv.

The car-racing tables are defined in Car-Racing Example, Tables. The JSON-relational duality view race_dv is defined in Creating Car-Racing Duality Views Using SQL.

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

Create a basic JSON-relational duality view notification directive

CREATE DIRECTIVE race_drtv FOR race_dv
  NOTIFY
  USING NOTIFICATION TABLE race_ntable
    NOTIFICATION VIEW race_nview;

This directive tracks inserts, updates, and deletes on race_dv. It is enabled by default and is not purged automatically.

Track only inserts and updates

CREATE DIRECTIVE race_drtv FOR race_dv
  NOTIFY ON INSERT UPDATE
  USING NOTIFICATION TABLE race_ntable
    NOTIFICATION VIEW race_nview;

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

Log a SYS_CONTEXT attribute

CREATE DIRECTIVE race_drtv_user FOR race_dv
  NOTIFY
  USING NOTIFICATION TABLE race_user_ntable
    NOTIFICATION VIEW race_user_nview
  CONTEXT NAMESPACE USERENV
    ATTRIBUTES CURRENT_USER(128);

This directive logs the CURRENT_USER attribute from the USERENV namespace.

Create a disabled JSON-relational duality view directive

CREATE DIRECTIVE race_drtv FOR race_dv
  NOTIFY ON INSERT UPDATE
  USING NOTIFICATION TABLE race_ntable
    NOTIFICATION VIEW race_nview
  DISABLE;

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

Specify retention for a JSON-relational duality view directive

CREATE DIRECTIVE race_drtv FOR race_dv
  NOTIFY ON INSERT UPDATE
  USING NOTIFICATION TABLE race_ntable
    NOTIFICATION VIEW race_nview
  RETENTION 1 DAYS;

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

Create a comprehensive JSON-relational duality view notification directive

CREATE DIRECTIVE race_drtv FOR race_dv
  NOTIFY ON INSERT UPDATE DELETE
  USING NOTIFICATION TABLE race_ntable
    TABLESPACE tbsp1
    NOTIFICATION VIEW race_nview
  CONTEXT NAMESPACE USERENV
    ATTRIBUTES CURRENT_USER(128)
  ENABLE
  RETENTION 1 DAYS;

This directive tracks all DML operations on race_dv and makes notification history eligible for purge after one day.