How Database Events Work
After a database event is defined for a table, it can be raised by all applications connected to the database, assuming appropriate privileges have been granted, as described in the Security Guide.
The event can be raised from interactive or embedded SQL applications, as a result of triggering a security alarm, or in a database procedure (where it can, in turn, be invoked by rules). It can also be received by all applications connected to the database and registered to receive the event.
In general, database events work as follows:
• An application or the DBMS raises an event, that is, issues a notification that a defined event has occurred.
• The DBMS notifies monitor applications that are registered to receive the event.
• The receiving application responds to the event by performing the action the monitor application designer specified when writing the program.
Note: You can also trace database events. For details, see the chapter “Using Monitoring and Tracing Tools” in the System Administrator Guide.
Raise an Event
To raise a database event, use the RAISE DBEVENT statement from interactive or embedded SQL applications or from within a database procedure.
A session can raise any event that is owned by the effective user, and any event for which the effective user, group, role, or public has been granted the raise privilege. For more information on granting privileges, see the Security Guide.
The RAISE DBEVENT statement requires you to specify an event_name parameter, which is the same as the value you enter in the Create Database Event dialog when you create the dbevent object using VDBA.
When the RAISE DBEVENT statement is issued, the DBMS sends an event message to all applications that are registered to receive the specified database event. If no applications are registered to receive the event, raising the event has no effect.
The optional event_text parameter is a string that can be used to pass context information or program handles to receiving applications. For example, use event_text to pass the name of the application that raised the event. You can retrieve this value using INQUIRE_SQL.
The WITH [NO]SHARE parameter enables you to specify which of the applications registered to receive the event are actually notified. If you specify WITH SHARE or omit this parameter, the DBMS notifies all registered applications when the event is raised. If you specify WITH NOSHARE, the DBMS notifies only the application that raised the event (assuming the program was also registered to receive the event).
If a transaction issues the RAISE DBEVENT statement and the transaction is subsequently rolled back, event queues are not affected by the rollback. The raised event remains queued to all sessions that registered for the event. The event queue is described in
Receive an Event.
For the complete statement syntax and additional information about using the RAISE DBEVENT statement, see the SQL Reference Guide.
Register to Receive an Event
To register to receive a database event, use the REGISTER DBEVENT statement from interactive or embedded SQL applications or from within a database procedure. For each event, the registration is in effect until the session removes the event registration or disconnects from the database.
A session can register for any event that is owned by the effective user, and any event for which the effective user, group, role, or public has been granted the register privilege. Sessions must register for each event to be received. For more information on granting privileges, see the Security Guide.
The DBMS issues an error if:
• A session attempts to register for a non-existent event.
• A session attempts to register for an event for which the session does not have register privilege.
• A session attempts to register twice for the same event.
If the REGISTER DBEVENT statement is issued from within a transaction that is subsequently rolled back, the registration is not rolled back.
For the complete statement syntax and additional information about using the REGISTER DBEVENT statement, see the SQL Reference Guide.
Receive an Event
To receive event information, an application must perform two steps:
1. Remove the next event from the session’s event queue (using GET DBEVENT, or implicitly, using WHENEVER DBEVENT or SET_SQL DBEVENTHANDLER).
2. Inquire for event information (using INQUIRE_SQL).
Get the Next Event from the Event Queue
The GET DBEVENT statement gets the next event, if any, from the queue of events that have been raised and for which the application has registered.
For the complete statement syntax and additional information about using the GET DBEVENT, see the SQL Reference Guide.
Obtain Event Information
To obtain event information, your application must issue the INQUIRE_SQL statement. With this statement, you specify one or more parameters to determine the type of information to retrieve. For example, to retrieve the text specified in the event_text parameter when the event was raised, use INQUIRE_SQL (DBEVENTTEXT).
For the complete statement syntax and additional information about using the INQUIRE_SQL statement, see the SQL Reference Guide.
Example: Using Database Events with Rules
The following example illustrates the use of database events in conjunction with rules in a manufacturing application. In this case, an event is used to detect when a drill gets too hot; the drill is then taken offline:
1. Create a database event named drill_hot to be raised when the drill overheats.
2. Create a database procedure that raises the drill_hot event; the procedure is executed when the rule defined in step 3 is triggered.
For example, the following procedure, take_drill_down, logs the time at which the drill was disabled and raises the drill_hot event:
Parameters
drill_id
Statements
insert into drill_log
select date('now'), 'OFFLINE', drill.*
from drill where id = :drill_id;
raise dbevent drill_hot;
3. Create a rule named drill_hot that is triggered whenever the drill temperature is logged. (This presumes another application that monitors and logs drill temperatures. This is created in the next step.)
For example, create a rule to execute the take_drill_down procedure (created in step 2) after any update operation in which the temperature column was changed. Using the following WHERE clause causes the rule to be fired if the temperature exceeded 500 degrees:
new.temperature > 500
The drill_id parameter must be passed as shown below:
drill_id = drill.id
4. Finally, create an application that monitors the status of the drills.
In the following example, the monitor application registers to receive the drill_hot event and checks for events. If the monitor application receives the drill_hot event, it sends mail to a supervisor and sends the signals required to disable the drill:
exec sql register dbevent drill_hot;
...
exec sql get dbevent
exec sql inquire_sql (:evname = eventname, ....);
if (evname = 'drill_hot') then
send mail
take drill offline
endif;
The various pieces function together as follows:
1. The drill monitor application periodically logs the drill temperature to the drill log table.
2. When the drill monitor application logs a drill temperature in excess of 500 degrees, the drill_hot rule fires.
3. The drill_hot rule executes the take_drill_down database procedure, which raises the drill_hot event.
4. Finally, the event monitor process detects the drill_hot event, sends mail to notify the responsible user, and sends a signal that disables the overheated drill.
Remove an Event Registration
To remove a database event registration, use the REMOVE DBEVENT statement from interactive or embedded SQL applications or from within a database procedure.
Using REMOVE DBEVENT simply “unregisters” an application for a particular database event. The event is still defined for the database and can be received by other applications that are still registered.
After an event registration is removed, the DBMS does not notify the application when the specified event is raised. Pending event messages are not removed from the event queue.
For the complete statement syntax and additional information about using the REMOVE DBEVENT statement, see the SQL Reference Guide.
Drop Database Events
You can drop a dbevent object from the database, in which case it cannot be raised and applications cannot register to receive it. Pending event messages are not removed from the event queue.
If an event is dropped while applications are registered to receive it, the event registrations are not dropped until each application disconnects from the database or removes its registration for the dropped event. If the event is recreated (with the same name), it can again be received by registered applications.