7. Understanding Database Procedures, Sessions, and Events : Database Events : Example: Database Events in Conjunction with Rules
 
Share this page                  
Example: Database Events in Conjunction with Rules
The following example uses database events in conjunction with rules to maintain inventory stock levels, as follows:
When the inventory table is updated, a rule is fired.
The rule executes a database procedure that checks stock levels.
If the on-hand quantity of a part falls below the required minimum, the procedure raises a stock_low database event.
Another application polls for stock_low database events. When the monitor application receives a stock_low database event, it generates a purchase order.
The detailed steps for this application are as follows:
1. Create a database event to be raised when the on-hand quantity of a part is low:
CREATE DBEVENT stock_low;
2. Create a rule that is fired when the qty_on_hand column of the inventory table is updated; the rule executes a database procedure when the quantity falls below 100 (assuming your business requires a minimum of 100 of each part you stock):
CREATE RULE check_qty AFTER UPDATE(qty_on_hand) OF
inventory WHERE qty_on_hand < 100
EXECUTE PROCEDURE issue_reorder(partno = old.partno);
3. Create the database procedure that raises the stock_low database event:
CREATE PROCEDURE reorder(partno VARCHAR(25)) AS
     BEGIN
          RAISE DBEVENT stock_low
               (DBEVENTTEXT = partno)
     END
4. At runtime, the stock monitoring application must register to receive the stock_low database event:
REGISTER DBEVENT stock_low
When the applications are running, the pieces work together as follows:
Whenever the qty_on_hand column of the inventory table is adjusted, the check_qty rule is fired; when the quantity falls below 100, the check_qty rule executes the reorder database procedure. The reorder procedure raises the stock_low database event.
The stock monitoring application issues the GET DBEVENT statement to check the database event queue and extract any database events (for which it is registered) that have been raised.
For each database event detected, the stock monitoring application issues an IINQUIRE_SQL statement to retrieve information about the database event. If it is the stock_low database event, the stock monitoring application generates a purchase order for the part.
The following diagram illustrates the process: