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.