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);
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
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:
Last modified date: 01/30/2023