Oracle7 Server Application Developer's Guide

Example of Using Alerts

Suppose you want to graph average salaries by department, for all employees. Your application needs to know whenever EMP is changed. Your application would look similar to the code below.

dbms_alert.register('emp_table_alert');
    readagain: 
   /* ... read the emp table and graph it */ 
      dbms_alert.waitone('emp_table_alert', :message, :status); 
      if status = 0 then goto readagain; else 
      /* ... error condition */

The EMP table would have a trigger similar to the following example:

CREATE TRIGGER emptrig AFTER INSERT OR UPDATE OR DELETE ON emp
    BEGIN 
      dbms_alert.signal('emp_table_alert', 'message_text'); 
   END;

When the application is no longer interested in the alert, it makes the following request:

dbms_alert.remove('emp_table_alert');

This reduces the amount of work required by the alert signaller. If a session exits (or dies) while registered alerts exist, they are eventually cleaned up by future users of this package.

The above example guarantees that the application always sees the latest data, although it may not see every intermediate value.